In response to a special plea, I translated my PERL version for creating a database out of a GEDCOM file to a PHP version. This version is also free for use.Only some personal data for connection to database and to find the file must be adapted:
#############################################################################
## insert the path to file and the filename
##
$path = "path/to/gedcom/file/"; # path to gedcom file
$filename = "gedcomfile.ged"; # GEDCOM file
##
#############################################################################
## Database connection
##
$hostport = "localhost"; # database host
$user = "root"; # database user name
$password = ""; # database password
$database = "stammbaum"; # database schema name
##
#############################################################################Afterwards the script can be called with a browser.
More words are not necessary, because everything is explained in the previous posts (Perl version) (Script for use in browser).
Have fun.
Download gedcomToDatabase.php as ZIP file
Table Structure:
--
-- Table structure for table `famchild`
--
DROP TABLE IF EXISTS `famchild`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `famchild` (
`famID` varchar(40) NOT NULL DEFAULT '',
`child` varchar(40) NOT NULL DEFAULT '',
PRIMARY KEY (`famID`,`child`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `family`
--
DROP TABLE IF EXISTS `family`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `family` (
`famID` varchar(40) NOT NULL DEFAULT '',
`husband` varchar(40) DEFAULT NULL,
`wife` varchar(40) DEFAULT NULL,
`marr_date` varchar(255) DEFAULT NULL,
`marr_plac` varchar(255) DEFAULT NULL,
PRIMARY KEY (`famID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `person_st`
--
DROP TABLE IF EXISTS `person_st`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person_st` (
`persID` varchar(40) NOT NULL DEFAULT '',
`name` varchar(255) DEFAULT NULL,
`vorname` varchar(255) DEFAULT NULL,
`marname` varchar(255) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birt_date` varchar(255) DEFAULT NULL,
`birt_plac` varchar(255) DEFAULT NULL,
`taufe_date` varchar(255) DEFAULT NULL,
`taufe_plac` varchar(255) DEFAULT NULL,
`deat_date` varchar(255) DEFAULT NULL,
`deat_plac` varchar(255) DEFAULT NULL,
`buri_date` varchar(255) DEFAULT NULL,
`buri_plac` varchar(255) DEFAULT NULL,
`occupation` varchar(255) DEFAULT NULL,
`occu_date` varchar(255) DEFAULT NULL,
`occu_plac` varchar(255) DEFAULT NULL,
`religion` varchar(80) DEFAULT NULL,
`confi_date` varchar(255) DEFAULT NULL,
`confi_plac` varchar(255) DEFAULT NULL,
`note` longtext,
PRIMARY KEY (`persID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;________________________________________________________________
UPDATE:
Like you all see, Hans had some problems with my script. First and Last name and Mariage name are still empty in Database after using my script. He send me a small GEDCOM file of his own to find the problem and fix the bug.
And I found the problem. I will explain it:
I’m using the genealogical Software of the “The Church of Jesus Christ of Latter-day Saints” (Mormons). It is called “Personal Ancestral File” and this FREE SOFTWARE is using and filling absolutely all fields of the GEDCOM format. Most of other software do not use some of this fields, if they are not really necessary. So there is in my file a person always starting like this:
0 @I1@ INDI 1 NAME Carsten Dedo /Fröhlich/ 2 SURN Fröhlich 2 GIVN Carsten Dedo 1 SEX M
The problem is, that the two lines with SURN and GIVN are not necessary, because this information is in the line with NAME, too. So they are not including this two line, but my first script was using this lines for the database inserts.
Now I created a second version of this script, which can be used in both versions, with or without SURN and GIVN lines.
For the marriage name I cannot help Hans, because his Software is not including this information (line starts with “2 _MARNM”) into the GEDCOM file. Because of this, I only can recommend to switch to an other software, if you really need the information of marriage name.
new version of PHP script for download.
Bug Fixed version from 05. Sep. 2011:
Gedcom To Database V1.0.2
Newest version: gedcomToDatabase V.1.0.3 (Bugfix in Extended Notes)
Carsten, you did a great job and so quick! There is only one problem I don’t get the names from the gedcom into the database. The fields: name, vorname and marname in the table: person_st are still empty (after a few tries). Perhaps the problem is caused by the software I used to create the gedcom file. I used Aldfaer, Dutch freeware (http://aldfaer.net). I will send you the gedcom file and the: language_NL.properties file for use in I your familytree.zip by mail. Perhaps it helps you find a quick solution to this problem.
New version is free for download. See update of post.
Pingback: Top Motion | Internet Marketing blog » Create a database from your GEDCOM file, PHP Version | Luventas …
Pingback: Include your family tree in your webpage, new Version | Luventas Web Design Blog
Great script !
I used it to convert my Gedcom to sql (just for testing for now) and it seems to have small bug (at least for my gedcom) – my residence place was imported as my birth place. May be because there are 2 PLAC fields on one person data and then first one was just ignored. Below is sample for one person from Gedcom, exported from Geni.com
———————————————-
0 @I1@ INDI
1 NAME Firstname /Lastname/
2 GIVN Firstname
2 SURN Lastname
2 _MAR Lastname
1 SEX M
1 BIRT
2 DATE 01 JAN 1901
2 PLAC MyCity, MyCounty
2 ADDR
3 CITY MyCity
3 STAE MyCounty
1 RESI
2 PLAC MyResidence
2 ADDR
3 CTRY MyResidence
1 _EMAIL my@email.com
1 PHON +49 12345678
1 FAMC @F1@
1 FAMS @F92@
1 RFN online:reference
1 SUBM @I22@
1 OBJE
2 FORM text/html
2 FILE http://www...
1 OBJE
2 FORM image/jpeg
2 TITL Title for photo
2 FILE http://www...
1 OBJE
2 FORM text/html
2 FILE http://www...
1 OBJE
2 FORM image/pjpeg
2 FILE http://www …
1 CHAN
2 DATE 01 JAN 2011
3 TIME 09:15:55
——————————————
you are totally right! There was a bug in the script, because I do not fill this information about residence in my scripts. So I never recognized that.
I will directly after this reply post my fix version, where the residence information as first fix is now ignored.
Thanks for your hint!
Greetings
Carsten Fröhlich
http://www.luventas-webdesign.de
Hi Carsten,
Nice tool you made here. I wonder if you compleet this tool by adding the sources in a tabel. That would be great.
René
In which table should I add it? Sorry, but I do not understand this hint. Please explain it a bit more…
hi Carsten,
What i mean is this.
I use the dutch program Alfaer to import the gedcom file.
for every birth, bapt, death and marriage are sources available. like this one 2 SOUR @S274@
You need to create extra colums in the person_st and one in the family tabel. example Birth_source, bapt_source.
After this is done there is to create another tabel because every source has an description
For @S274 is this
0 @S274@ SOUR
1 TEXT
2 CONC Bidprentje
In this example it means a Death source
Another thing what’s going wrong is the death date. if there’s no death date the program used this date
2 DATE 26 MAR 2010
0 @I5320@ INDI
1 RIN 5320
1 REFN 743
1 NAME Aldegundis Joannes/van der Aa/
1 SEX F
1 DEAT
1 FAMS @F1269610238@
1 _NEW
2 TYPE 1
2 DATE 26 MAR 2010
3 TIME 14:58:12
1 CHAN
2 DATE 17 MAR 2011
3 TIME 13:24:26
René
The script works for me until 22500 persons, with 25000 is doesn’t work any more. Is there a maximum in the script or is there another problem. I’am working on a webprogram for myself and got more than 26000 persons in my database. Is there a solution for the problem?
Greetings Jos Reintjens
Hi Jos,
26000 Persons is really a lot, but I do not included a maximum value in the script. Maybe you have to extend your Apache settings. Normally there is a maximum time included, how long a script can run. I think, you reach this time limit with the 23000 Persons. If you run the script on your local system or on a server where you are able to administrate, then try to extend this time limit and test it again.
Greetings
Carsten Fröhlich
Hello Carsten.
I made the time inputs larger. but it didn’t help.
I’ve got the next screen writings:
Start reading GEDCOM file
Start reading Person data
Start reading family data
Then it stops.
The apache time limits are set to:
Keep-alive timeout 500 (coming from 5)
Request timeout 1000 (coming from 300)
I did some changes to the script but i don’t think that’s the problem. The changes are:
## create single insert statement
$insert = “INSERT INTO person_st (`record`, `Achternaam`, `voornaam`, `marname`, `Geslacht`, `Geboortedatum`, `Geboorteplaats`, “;
$insert .= “`Doopdatum`, `Doopplaats`, `Overlijdendatum`, `Overlijdenplaats`, `Begrafenisdatum`, `Begrafenisplaats`, “;
$insert .= “`Beroep`, `occu_date`, `occu_plac`, `Gezindte`, `confi_date`, `confi_plac`, `Persooninfo`) “;
I hope you can point me to the problem.
Thanks Jos Reintjens
Hi Jos,
you changed the DB-column names in your insert statment. Do you also changed the names of the columns in your MySQL DB?
Original was:
DROP TABLE IF EXISTS `person_st`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person_st` (
`persID` varchar(40) NOT NULL DEFAULT ”,
`name` varchar(255) DEFAULT NULL,
`vorname` varchar(255) DEFAULT NULL,
`marname` varchar(255) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birt_date` varchar(255) DEFAULT NULL,
`birt_plac` varchar(255) DEFAULT NULL,
`taufe_date` varchar(255) DEFAULT NULL,
`taufe_plac` varchar(255) DEFAULT NULL,
`deat_date` varchar(255) DEFAULT NULL,
`deat_plac` varchar(255) DEFAULT NULL,
`buri_date` varchar(255) DEFAULT NULL,
`buri_plac` varchar(255) DEFAULT NULL,
`occupation` varchar(255) DEFAULT NULL,
`occu_date` varchar(255) DEFAULT NULL,
`occu_plac` varchar(255) DEFAULT NULL,
`religion` varchar(80) DEFAULT NULL,
`confi_date` varchar(255) DEFAULT NULL,
`confi_plac` varchar(255) DEFAULT NULL,
`note` longtext,
PRIMARY KEY (`persID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
You have to change this also to:
DROP TABLE IF EXISTS `person_st`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person_st` (
`record` varchar(40) NOT NULL DEFAULT ”,
`Achternaam` varchar(255) DEFAULT NULL,
`voornaam` varchar(255) DEFAULT NULL,
`marname` varchar(255) DEFAULT NULL,
`Geslacht` char(1) DEFAULT NULL,
`Geboortedatum` varchar(255) DEFAULT
etc.
And also the column name are needed in the whole application. Mybe it is better to leve the DB clumn names like they are. How they are named in view on website is not related to the DB column name.
Hope, this will help you.
Greetings
Carsten Fröhlich
Hello Carsten
I put the original script back. I loaded the file with 23000 persons. It worked. I took the file with 25000 persons, and got the same problem.
Start reading GEDCOM file
Start reading Person data
Start reading family data
Then I took the total file with 26000+, and I had the next screen writing:
Start reading GEDCOM file
Nothing more. I’m not a programmer only a guy who likes to do some coding. I’m out of solutions. Thank for your time and help. If you know something pease help me i’m out of solutions.
Greetings
Jos Reintjens
I tried it with the original data of Jos and found no problem. It seems to be a problem of the configuration of the system.
Hello, I found the problem. The memory_limit in the php.ini was set on 128M. I chenged it in 256M and it works great. Carsten Thank you for your help.
Greetings Jos Reintjens