Converting static pages to dynamic pages using PHP and MySQL, Part 3

The challenge:
Take Adam Walker’s Carrajina dictionary (http://carrajina.conlang.org/dicthome.html) and stick it in a database so that Adam can add an entry whenever he wants without re-writing an html page and possibly messing up his html. One stricture is that Adam does not want to learn too much new stuff – no SQL, no PHP, just HTML. And if he can enter SAMPA and have it turn into IPA, that’s a plus!

Step 1 (data prep) is here, and Step 3 (generating listings) is here.

Step 2 involves importing data into a database. That’s what this blog post covers. I skipped it earlier because it is more complicated than step 2.

Part 4, when I get to it, will discuss the forms for adding new entries to the database and editing existing entries. I might do a Part 5 to discuss CSS and other miscellaneous details.

So, at the end of step 1, we had a data model and two XML files – one with all the English-Carrajina entries and one with all the Carrajina-English entries.

First, we turn our data model into the sql statements required to create the database.

CREATE SCHEMA IF NOT EXISTS adamsdb;

Then we tell the server to use that database for now.

USE adamsdb;

Creating the tables:

CREATE TABLE `carrajina_entry` (
 `carrajina_entryID` int(11) NOT NULL AUTO_INCREMENT,
 `carrajina_term` varchar(255) NOT NULL,
 `carrajina_pronunciation` varchar(255) DEFAULT NULL,
 `etymology` text,
 PRIMARY KEY (`carrajina_entryID`),
 UNIQUE KEY `uq_carrajina_term` (`carrajina_term`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE `carrajina_definition` (
 `carrajina_definitionID` int(11) NOT NULL AUTO_INCREMENT,
 `carrajina_entryID` int(11) NOT NULL,
 `definition_number` int(2) NOT NULL DEFAULT '1',
 `part_of_speech` varchar(45) DEFAULT NULL,
 `definition` text,
 PRIMARY KEY (`carrajina_definitionID`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE `carrajina_idiom` (
 `carrajina_idiomID` int(11) NOT NULL AUTO_INCREMENT,
 `carrajina_idiom` varchar(255) NOT NULL,
 `carrajina_idiom_definition` text,
 `carrajina_entryID` int(11) NOT NULL,
 PRIMARY KEY (`carrajina_idiomID`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `carrajina_note` (
 `carrajina_noteID` int(11) NOT NULL AUTO_INCREMENT,
 `note` text NOT NULL,
 `carrajina_entryID` int(11) NOT NULL,
 PRIMARY KEY (`carrajina_noteID`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `english_entry` (
 `english_entryID` int(11) NOT NULL AUTO_INCREMENT,
 `english_term` varchar(255) NOT NULL,
 `english_pronunciation` varchar(255) DEFAULT NULL,
 `head_entryID` int(11) DEFAULT NULL,
 PRIMARY KEY (`english_entryID`),
 UNIQUE KEY `uq_english_term` (`english_term`)
 ) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `english_definition` (
 `english_definitionID` int(11) NOT NULL AUTO_INCREMENT,
 `english_entryID` int(11) NOT NULL,
 `definition_number` int(2) NOT NULL DEFAULT '1',
 `part_of_speech` varchar(45) DEFAULT NULL,
 `definition` text,
 PRIMARY KEY (`english_definitionID`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Then we add the constraints. We could do this earlier, but it is easier to do it afterwards. That is to say, since we are asking MySQL to enforce these foreign key constraints (using innodb tables), it can’t do that until the tables are created. Thus, we create the tables first and then add the foreign key constraints.

ALTER TABLE `carrajina_definition`
  ADD CONSTRAINT `fk_cdefentry` FOREIGN KEY (`carrajina_entryID`)
  REFERENCES `carrajina_entry` (`carrajina_entryID`)
  ON DELETE CASCADE;

ALTER TABLE `english_definition`
  ADD CONSTRAINT `fk_edefentry` FOREIGN KEY (`english_entryID`)
  REFERENCES `english_entry` (`english_entryID`)
  ON DELETE CASCADE;

ALTER TABLE `english_entry`
  ADD CONSTRAINT `self_key` FOREIGN KEY (`head_entryID`)
  REFERENCES `english_entry` (`english_entryID`)
  ON DELETE SET NULL;

ALTER TABLE `carrajina_idiom`
  ADD CONSTRAINT `fk_idiomentry` FOREIGN KEY (`carrajina_entryID`)
  REFERENCES `carrajina_entry` (`carrajina_entryID`)
  ON DELETE CASCADE;

ALTER TABLE `carrajina_note`
  ADD CONSTRAINT `fk_noteentry` FOREIGN KEY (`carrajina_entryID`)
  REFERENCES `carrajina_entry` (`carrajina_entryID`)
  ON DELETE CASCADE;

Now we have an empty dictionary waiting for data. Time for those XML files. I wrote a PHP script to load the XML into the database. This involves a connection to the database, using mysqli which I am not going to detail here.

To process the xml, first we load the file:

if (file_exists('cartheng.xml')) {
    $xml = simplexml_load_file('cartheng.xml');
} else {
    exit('Failed to open cartheng.xml.');
}

simplexml_load_file is part of the SimpleXML extension and it provides an easy way to deal with XML. The simplexml extension comes with PHP 5.1.2 and later and is enabled by default. simplexml_load_file(filename) loads a file into a PHP object. This makes each node in the database an object as well. The XML file consists of a number of “entry” nodes and each can be referred to by $xml->entry[index] where index is the number of the entry, starting with zero and going on to whatever. “entry” subnodes can likewise be called with $xml->entry[index]->subnode_name.

First thing, we get a count of the number of entries:

$total = count($xml);

which turns out to be 2352. For each of those 2352 entries, we want to get the subnodes and enter them into the database:

for ($i=0; $i<$total; $i++) {
//carrajina, ipa, definitions+, etymology?, note?, idiom*
//start by naming our variables...
    $carrajina = null;
    $ipa = null;
    $etymology = null;
    $note = null;
    $subentry = null;

    $carrajina = $xml->entry[$i]->carrajina[0];
    $ipa = $xml->entry[$i]->ipa[0];
    $etymology = $xml->entry[$i]->etymology[0];

    //first insert statement
    $query1 = "INSERT INTO `carrajina_entry` (`carrajina_term`,
    `carrajina_pronunciation`, `etymology`) VALUES (?, ?, ?)";

    if ($stmt1 = $mysqli->prepare($query1)) {
        $stmt1->bind_param("sss", $carrajina, $ipa, $etymology);
        $stmt1->execute();
        $id = $mysqli->insert_id;
        $stmt1->close();
    }

Note that $mysqli->insert_id. That’s how we get the auto-incremented value that is the carrajina_entryID value for the subsequent inserts.

    $defs = $xml->entry[$i]->definitions->definition;
    //defs has subnodes

    $q = count($defs);
    for ($p = 0; $p<$q; $p++) {
        $num = $defs[$p]->num[0];
        $pos = $defs[$p]->pos[0];
        $deftext = $defs[$p]->deftext[0];

        //second insert statement
        $query2 =  "INSERT INTO `carrajina_definition`
        (`carrajina_entryID`, `definition_number`,
        `part_of_speech`, `definition`) VALUES (?, ?, ?, ?)";

        if ($stmt2 = $mysqli->prepare($query2)) {
            $stmt2->bind_param("iiss", $id, $num, $pos, $deftext);
            $stmt2->execute();
            $stmt2->close();
        }
    }//end for

    $note = $xml->entry[$i]->note[0];

    //don't run this if there is no note node
    if ($note) {
        //third insert statement
        $query3 = "INSERT INTO `carrajina_note`(`note`,
        `carrajina_entryID`) VALUES (?, ?)";

        if ($stmt3 = $mysqli->prepare($query3)) {
            $stmt3->bind_param("si", $note, $id);
            $stmt3->execute();
            $stmt3->close();
        }
    }//end if ($note)

    $idioms = $xml->entry[$i]->subentry;
    //idioms is also an array

    if ($idioms) {
        $k = count($idioms);
        for ($j = 0; $j<$k; $j++) {
            $idiom = $idioms[$j]->idiom[0];
            //because the file has idioms in upper-case...
            $idiom = strtolower($idiom);
            $idiomdef = $idioms[$j]->{"idiom-definition"}[0];
            //fourth insert statement
            $query4 =  "INSERT INTO `carrajina_idiom`
            (`carrajina_idiom`, `carrajina_idiom_definition`,
            `carrajina_entryID`) VALUES (?, ?, ?)";

            if ($stmt4 = $mysqli->prepare($query4)) {
                $stmt4->bind_param("ssi", $idiom, $idiomdef, $id);
                $stmt4->execute();
                $stmt4->close();
            }
        }//end for
    }//end if ($idioms)
}//end original for loop

The English-Carrajina side used more or less the same code with node names and variable names changed and of course different insert statements.

OK, maybe that wasn’t so complicated.