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

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, Step 2 (create database and load data) is here, and Step 3 (generating listings) is here. Part 4 on adding new entries is here. This is part 5.

Updating an entry uses the same form as adding an entry, but with the values pre-filled. Here is a screenshot of the form with an entry to edit. screenshot of edit-entry form

And here is the code to generate this form. First some code in the html head:

switch ($lang) {
   case 'eng':
      if ((isset($id)) && (is_numeric($id))) {
         $_SESSION['id'] = $id;
         $data = new EnglishEntry($mysqli, $id);
         $task = 'edit';
         $_SESSION['task'] = $task;
         $dbc = new EnglishController();
      }
   break;
   case 'car':
      if ((isset($id)) && (is_numeric($id))) {
         $_SESSION['id'] = $id;
         $data = new CarrajinaEntry($mysqli, $id);
         $task = 'edit';
         $_SESSION['task'] = $task;
         $dbc = new CarrajinaController();
      }
   break;
}
?>

Now for code in the body:

<!-- the big form -->
<form method="post" action="edit.php">
<?php
$term = null;
$pron = null;
$defs = null;

if (isset($data)) {
   $term = $data->getTerm();
   $pron = $data->getPron();
   $delforms[] = $dbc->formDelete($id, 'delentry',
      "Delete Full Entry "$term"");
   $defs = $data->getDefs();
}
?>
<!-- term and pronounciation -->
<p>
Word &nbsp; <input type="text" id="term" name="term"
value="<?php echo $term; ?>" />
&nbsp; Sampa or IPA &nbsp; <input
type="text" id="pron" name="pron" value="<?php echo $pron; ?>"
class="ipa" />
<span class="replace">click for ipa</span>
</p>

Easy enough. The $delforms array is used to hold delete links for each item that can be independently deleted.

<!-- definitions -->
<?php
$i = 1;
if (isset($defs)) {
   foreach ($defs as $defno => $defentry) {
      $delforms[] = $dbc->formDelete($defentry[0],
      'deldef', "Delete Def #$defno");
?>
<p>
Def # <input type="text"  class="num"
name="<?php echo "def[$defno][defno]"; ?>"
value="<?php echo $defno; ?>" />
&nbsp; POS <input type="text"
class="dp" name="<?php echo "def[$defno][pos]"; ?>"
value="<?php echo $defentry[1]; ?>" />
&nbsp; Definition Text
<textarea rows='3' cols='48' name="<?php echo
"def[$defno][deftext]"; ?>"><?php echo $defentry[2]; ?></textarea>
<input type="hidden" name="<?php echo "def[$defno][defid]"; ?>"
value="<?php echo $defentry[0]; ?>" />
</p>
<?php
      $i = $defno + 1;
   }
}
?>
<!-- add a definition -->
<div class="animate">
<p class="toggleable">New Definition +</p>
<div class="newitem">
<p>Def # <input type="text"  class="num"
name="<?php echo "def[$i][defno]"; ?>"  /> &nbsp; POS
<input type="text" class="dp" name="<?php echo "def[$i][pos]"; ?>" />
&nbsp; Definition Text <textarea rows='3' cols='48'
name="<?php echo "def[$i][deftext]"; ?>"></textarea>
</p>
</div>
</div>

This code prints definition form fields for each definition plus form fields for adding a new definition. The following adds the other form fields for English or for Carrajina entries.

<!-- extras -->
<!-- English first -->
<?php
if ($lang == 'eng') {
   //subentries
   $j = 1;
   if (isset($data)) {
      $subids = $data->getSubIDs();
      $count = count($subids);
      if ($count != 0) {
         foreach ($subids as $subid) {
            $sub = new EnglishEntry($mysqli, $subid);
            $sterm = $sub->getTerm();
            $spron = $sub->getPron();
            $delforms[] = $dbc->formDelete($subid, 'delsub',
               "Delete Subentry "$sterm"");
?>
<p>
Subentry <input type="text"
name="<?php echo "subentry[$j][sterm]"; ?>"
value="<?php echo $sterm; ?>" /> &nbsp; Sampa or IPA
<input type="text" name="<?php echo "subentry[$j][spron]"; ?>"
value="<?php echo $spron; ?>" class="ipa" />
<span class="replace">click for ipa</span>
<input type="hidden" name="<?php echo "subentry[$j][subid]"; ?>"
value="<?php echo $subid; ?>" />
</p>
<?php
            //subentry defs
            $defs = $sub->getDefs();
            $k = 1;
            if (!empty($defs)) {
               foreach ($defs as $defno => $defentry) {
                  $delforms[] = $dbc->formDelete($defentry[0],
                    'deldef', "Delete Subentry "$sterm" Def #$defno");
?>
<p>
Def # <input type="text"  class="num" value="<?php echo $defno; ?>"
name="<?php echo "subentry[$j][sdef][$defno][defno]"; ?>" />
&nbsp; POS <input type="text"  class="dp"
value="<?php echo $defentry[1]; ?>"
name="<?php echo "subentry[$j][sdef][$defno][pos]"; ?>"  />
&nbsp; Definition Text <textarea rows='3' cols='48'
name="<?php echo "subentry[$j][sdef][$defno][deftext]"; ?>"><?php
echo $defentry[2]; ?></textarea>
<input type="hidden"  value="<?php echo $defentry[0]; ?>"
name="<?php echo "subentry[$j][sdef][$defno][defid]"; ?>" />
</p>
<?php
               $k = $defno + 1;
            }//end foreach ($defs as $defno => $defentry)
         }//end if (!empty($defs))
?>
<div class="animate">
<p class="toggleable">New Subentry Definition +</p>
<div class="newitem">
<p>
Def # <input type="text"  class="num"
name="<?php echo "subentry[$j][sdef][$k][defno]"; ?>" />
&nbsp; POS <input type="text"  class="dp"
name="<?php echo "subentry[$j][sdef][$k][pos]"; ?>"  />
&nbsp; Definition Text <textarea rows='3' cols='48'
name="<?php echo "subentry[$j][sdef][$k][deftext]"; ?>"></textarea>
<input type="hidden"
name="<?php echo "subentry[$j][sdef][$k][defid]"; ?>" />
</p>
</div>
</div>
<?php
         $j++;
      }//end foreach ($subids as $subid)
   }//end if ($count != 0)
}//end if (isset($data))
?>
<!-- new subentry -->
<div class="animate">
<p class="toggleable">New Subentry +</p>
<div class="newitem">
<p>
Subentry <input type="text"
name="<?php echo "subentry[$j][sterm]"; ?>"  />
&nbsp; Sampa or IPA
<input type="text" name="<?php echo "subentry[$j][spron]"; ?>"
class="ipa" />
<span class="replace">click for ipa</span>
</p>
<p>
Def # <input type="text"  class="num"
name="<?php echo "subentry[$j][sdef][1][defno]"; ?>" />
&nbsp; POS  <input type="text"  class="dp"
name="<?php echo "subentry[$j][sdef][1][pos]"; ?>" />
&nbsp; Definition Text  <textarea rows='3' cols='48'
name="<?php echo "subentry[$j][sdef][1][deftext]"; ?>"></textarea>
</p>
</div>
</div>

<!-- Carrajina extras -->
<?php
} else if ($lang == 'car') {
   $etym = null;
   if (isset($data)) {
      $etym = $data->getEtymology();
   }//end if (isset($data))
?>
<!-- etymology -->
<p>Etymology   
<textarea rows='3' cols='48' name="etymology"><?php
echo $etym; ?></textarea>
</p>

<!-- Idioms -->
<p class="help">Idioms should be entered in lower case.</p>
<?php
   $idioms = null;
   $p = 1;
   if (isset($data)) {
      $idioms = $data->getIdioms();
      if (!empty($idioms)) {
         foreach ($idioms as $idiomentry) {
            $delforms[] = $dbc->formDelete($idiomentry[2],
              'delidiom',"Delete Idiom "$idiomentry[0]"");
?>
<p>
Idiom <input type="text" name="<?php echo "idioms[$p][idiom]"; ?>"
value="<?php echo $idiomentry[0]; ?>" />
&nbsp; Definition <textarea rows='3' cols='48'
name="<?php echo "idioms[$p][idiomdef]"; ?>"><?php
echo $idiomentry[1]; ?></textarea>
<input type="hidden"  name="<?php echo "idioms[$p][idiomid]"; ?>"
value="<?php echo $idiomentry[2]; ?>" />
</p>
<?php
            $p++;
         }//end foreach ($idioms as $idiomentry)
      }//end if (!empty($idioms))
   }//end if (isset($data))
?>
<!-- New idioms -->
<div class="animate">
<p class="toggleable">New Idiom +</p>
<div class="newitem">
<p>Idiom
<input type="hidden"  name="<?php echo "idioms[$p][idiomid]"; ?>"  />
<input type="text"  name="<?php echo "idioms[$p][idiom]"; ?>" />
&nbsp; Definition
<textarea rows='3' cols='48' name="<?php
echo "idioms[$p][idiomdef]"; ?>"></textarea>
</p>
</div>
</div>

<!-- Note -->
<?php
   $note = null;
   if (isset($data)) {
      $note = $data->getNote();
      if ($note[0] > 0) {
         $delforms[] = $dbc->formDelete($note[0], 'delnote',
            "Delete Note for "$term"");
      }
   }
?>
<p>
Note   
<input type="hidden" name="note[noteid]"
value="<?php echo "$note[0]"; ?>"  />
<textarea rows='3' cols='48' name="note[note]"><?php
echo "$note[1]"; ?></textarea>
</p>
<?php
}//end if lang
?>

<!-- Submit! -->
<p><input type="hidden"  name="task" value="<?php echo $task; ?>" />
<input type="hidden"  name="lang" value="<?php echo $lang; ?>" />
<!-- this will be blank for new entries -->
<input type="hidden" name="entryID"
value="<?php if (isset($id)) {echo $id;} ?>" />
<input type="submit" value="Submit!" /></p>
</form>

<!-- Now for those nasty deletes -->
<div>
<?php
if (isset($delforms)) {
   echo "<h2>Deletes</h2>";
   foreach ($delforms as $form) {
      echo $form;
   }
}
?>
</div>

That’s the form. One thing that might bite: Make sure there is no space or other characters between empty <textarea></textarea> code. If there is, the space will show up as a value when processing the data.

The code for processing updates is very similar to the code for processing adds.

public function updateEntry($mysqli, $entry) {
   $id = $this->sanitize($entry['entryID']);
   $term = $this->process($entry['term']);
   $pron = $this->process($entry['pron']);
   $etym = $this->process($entry['etymology']);
   $csort = $this->getSort($term);

   $query = "UPDATE carrajina_entry SET carrajina_sort=?,
      carrajina_term=?, carrajina_pronounciation=?,
      etymology=? WHERE carrajina_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssssi", $csort, $term, $pron, $etym, $id);
      $stmt->execute();
      $stmt->close();
   }

   //update defs and add new defs for entry, if
   foreach ($entry['def'] as $def) {
      if (!empty($def['defid'])) {
         $this->updateCardefs($mysqli, $def);
      } else {
         $this->addDefs($mysqli, $def, $id);
      }
   }

   //update idioms, if and add new, if
   if (isset($entry['idioms'])) {
      foreach ($entry['idioms'] as $idiom) {
         if (!empty($idiom['idiomid'])) {
            $this->updateIdioms($mysqli, $idiom);
         } else {
            $this->addIdioms($mysqli, $idiom, $id);
         }
      }
   }

   //update note, if
   if (!empty($entry['note']['noteid'])) {
      $noteid = $this->process($entry['note']['noteid']);
      $note = $this->process($entry['note']['note']);

      $query = "UPDATE carrajina_note SET note=? WHERE noteID =?";

      $stmt = $mysqli->stmt_init();
      if ($stmt = $mysqli->prepare($query)) {
         $stmt->bind_param("si", $note, $noteid);
         $stmt->execute();
         $stmt->close();
      }
   } else if (!empty($entry['note']['note'])) {
      $this->addNote($mysqli, $entry['note'], $id);
   } else {
      //nada
   }

   return $id;
}

And that is pretty much it. I enjoyed doing this and I think Adam enjoyed having it done. 🙂

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

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, Step 2 (create database and load data) is here, and Step 3 (generating listings) is here.

Here is the html for adding a new English entry.

<h2>Add New Entry</h2>

<form action="edit.php" method="post">
<p>
Word  
<input id="term" type="text" value="" name="term" />
  Sampa or IPA
<input id="pron" class="ipa" type="text" value="" name="pron" />
<span class="replace">click for ipa</span>
</p>

<div class="animate">
<p class="toggleable">New Definition +</p>
<div class="newitem" style="display: none;">
<p>
Def #
<input class="num" type="text" name="def[1][defno]" />
  POS
<input class="dp" type="text" name="def[1][pos]" />
  Definition Text
<textarea name="def[1][deftext]" cols="48" rows="3"></textarea>
</p>
</div>
</div>

<div class="animate">
<p class="toggleable">New Subentry +</p>
<div class="newitem" style="display: none;">
<p>
Subentry
<input type="text" name="subentry[1][sterm]" />
  Sampa or IPA
<input class="ipa" type="text" name="subentry[1][spron]">
<span class="replace">click for ipa</span>
</p>
<p>
Def #
<input class="num" type="text" name="subentry[1][sdef][1][defno]">
  POS
<input class="dp" type="text" name="subentry[1][sdef][1][pos]">
  Definition Text
<textarea name="subentry[1][sdef][1][deftext]"
     cols="48" rows="3"></textarea>
</p>
</div>
</div>

<p>
<input type="hidden" value="add" name="task">
<input type="hidden" value="eng" name="lang">
<input type="hidden" value="" name="entryID">
<input type="submit" value="Submit!">
</p>

</form>

This uses some Javascript (or rather jQuery), stored at the bottom of the page:

<script type="text/javascript">
//hides and shows new item sections
$(document).ready(function () {
	$('.newitem').hide();

	$('.animate').children('.toggleable').click(function () {
		$(this).parent().children('.newitem').toggle();
	});
});

//click for ipa
$('.replace').click(function () {
	var inputtext = $(this).prev('.ipa');
	var str = inputtext.val();
	str = str.replace(/a/g, 'ä')
				 .replace(/E/g,'ɛ')
				 .replace(/I/g, 'ɪ')
				 .replace(/J/g, 'ɲ')
				 .replace(/N/g, 'ŋ')
				 .replace(/O/g, 'ɔ')
				 .replace(/U/g, 'ʊ')
				 .replace(/S/g, 'ʃ')
				 .replace(/T/g, 'θ')
				 .replace(/U/g, 'ʊ')
				 .replace(/V/g, 'ʌ')
				 .replace(/W/g, 'ʍ')
				 .replace(/Z/g, 'ʒ')
				 .replace(/3/g, 'ɜ')
				 .replace(/4/g, 'ɾ')
				 .replace(/@/g, 'ə')
				 .replace(/&/g, 'æ')
				 .replace(/,/g, 'ˌ')
				 .replace(/\?/g, 'ʔ');
	var language = "<?php echo $lang; ?>";
	if (language == "eng") {
		str = str
				 .replace(/m=/g, 'm̩')
				 .replace(/n=/g, 'n̩')
				 .replace(/l=/g, 'l̩')
				 .replace(/r/g, 'ɹ');
	}
	if (language == "car") {
		str = str.replace(/d/g, 'd̪')
				 .replace(/d̪ʒ/g, 'd͡ʒ')
				 .replace(/t/g, 't̪')
				 .replace(/t̪ʃ/g, 't͡ʃ')
			     .replace(/n/g, 'n̪');
	}
	$(this).prev('.ipa').val(str);
});
</script>

The click for ipa code is simply a series of global replace functions.

Processing this form works like so:

public function addEntry($mysqli, $entry) {
   $hentryID = null;
   $term = $this->process($entry['term']);
   $pron = $this->process($entry['pron']);

   $query = "INSERT INTO english_entry (english_term,
      english_pronunciation, head_entryID) VALUES (?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssi", $term, $pron, $hentryID);
      $stmt->execute();
      $stmt->close();
   }

   $entryid = $mysqli->insert_id;
   $this->addDefs($mysqli, $entry['def'][1], $entryid);

   if (isset($entry['subentry'])) {
      $this->addSubentry($mysqli, $entry['subentry'][1], $entryid);
      $subid = $mysqli->insert_id;
      $this->addDefs($mysqli, $entry['subentry'][1]['sdef'][1],
         $subid);
   }

   return $entryid;
}

This function calls the process() and addDefs() functions and sometimes the addSubentry() function. process() is part of the parent class, PageController.

class PageController {

private $fake;
private $real;

function __construct() {
   $this->fake = array('[i]', '[/i]', '[b]', '[/b]',
      '[sup]', '[/sup]');
   $this->real = array('<i>', '</i>', '<b>', '</b>',
      '<sup>', '</sup>');
}

public function makeReal($str) {
   $str = str_replace($this->fake, $this->real, $str);
   return $str;
}

public function makeFake($str) {
   $str = str_replace($this->real, $this->fake, $str);
   return $str;
}

public function sanitize($str) {
   $str = trim($str);
   $str = strip_tags($str);
   $str = htmlspecialchars($str, ENT_QUOTES, "UTF-8");
   return $str;
}

public function process($str) {
   $str = $this->makeFake($str);
   $str = $this->sanitize($str);
   $str = $this->makeReal($str);
   return $str;
}

}

So process() changes any allowed html tags into [tag] and [/tag], then trims the string, and strips all quotes and special characters, and then changes [tag] and [/tag] into <tag> and </tag>.

Here is addDefs():

private function addDefs($mysqli, $def, $id) {
   $defno = $this->sanitize($def['defno']);
   $pos = $this->process($def['pos']);
   $deftext = $this->process($def['deftext']);

   $query = "INSERT INTO english_definition (english_entryID,
      definition_number, part_of_speech, definition)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("iiss", $id, $defno, $pos, $deftext);
      $stmt->execute();
      $stmt->close();
   }
   $defid = $mysqli->insert_id;

   return $defid;
}

Note that we don’t actually use the returned value, but it is useful to have for debugging purposes. Likewise with addSubentry().

private function addSubentry($mysqli, $sub, $id) {
   $term = $this->process($sub['sterm']);
   $pron = $this->process($sub['spron']);

   $query = "INSERT INTO english_entry (english_term,
      english_pronunciation, head_entryID)
      VALUES (?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssi", $term, $pron, $id);
      $stmt->execute();
      $stmt->close();
   }
   $subid = $mysqli->insert_id;

   return $subid;
}

Adding an entry to the Carrajina side is similar. The html form is different, of course, and the code is somewhat different, too. Here is addEntry():

public function addEntry($mysqli, $entry) {
   $term = $this->process($entry['term']);
   $pron = $this->process($entry['pron']);
   $etym = $this->process($entry['etymology']);
   $csort = $this->getSort($term);

   $query = "INSERT INTO carrajina_entry (carrajina_sort,
      carrajina_term, carrajina_pronunciation, etymology)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssss", $csort, $term, $pron, $etym);
      $stmt->execute();
      $stmt->close();
   }
   $entryid = $mysqli->insert_id;

   //add defs
   $this->addDefs($mysqli, $entry['def'][1], $entryid);

   //add idioms, if
   if (isset($entry['idioms'])) {
      $this->addIdioms($mysqli, $entry['idioms'][1], $entryid);
   }

   //add note, if
   if (isset($entry['note'])) {
      $this->addNote($mysqli, $entry['note'], $entryid);
   }

   return $entryid;
}

carrajina_sort is a column added to the carrajina_entry table to enable a custom sort value. getSort() works like so:

private function getSort($value) {
   $chararray = array('<sup>', '</sup>', 'ch', 'dj', 'rr', 'ñ',
      'Ch', 'Dj', 'Rr', 'Ñ', 'ó',
      'a', 'b', 'c', 'd', 'e', 'f',
      'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p',
      'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
      'A', 'B', 'C', 'D', 'E', 'F',
      'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
      'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
      '-', "'", ' ', '...');
   $numberarray = array('000', '', '13', '15', '31', '26',
      '13', '15', '31', '26', '27',
      '10', '11', '12', '14', '16', '17',
      '18', '19', '20', '21', '22', '23', '24', '25', '27', '28',
      '29', '30', '32', '33', '34', '35', '36', '37', '38', '39',
      '10', '11', '12', '14', '16', '17',
      '18', '19', '20', '21', '22', '23', '24', '25', '27', '28',
      '29', '30', '32', '33', '34', '35', '36', '37', '38', '39',
      '', '', '00', '00');
   $value = str_replace($chararray, $numberarray, $value);
   return $value;
}

This converts a Carrajina word into a string of numbers that correspond to the Carrajina sort order.

Carrajina’s addDefs():

private function addDefs($mysqli, $def, $id) {
   $defno = $this->sanitize($def['defno']);
   $pos = $this->process($def['pos']);
   $deftext = $this->process($def['deftext']);

   $query = "INSERT INTO carrajina_definition (carrajina_entryID,
      definition_number, part_of_speech, definition)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("iiss", $id, $defno, $pos, $deftext);
      $stmt->execute();
      $stmt->close();
   }
}

addIdioms():

private function addIdioms($mysqli, $idiom, $id) {
   $iterm = $this->process($idiom['idiom']);
   $idef = $this->process($idiom['idiomdef']);
   $csort = $this->getSort($iterm);

   $query = "INSERT INTO carrajina_idiom (carrajina_entryID,
      carrajina_sort, carrajina_idiom, carrajina_idiom_definition)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("iiss", $id, $csort, $iterm, $idef);
      $stmt->execute();
      $stmt->close();
   }
}

addNote():

private function addNote($mysqli, $entry, $id) {
   $note = $this->process($entry['note']);
   $query = "INSERT INTO carrajina_note (note, carrajina_entryID)
      VALUES (?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("si", $note, $id);
      $stmt->execute();
      $stmt->close();
   }
}

And that’s enough for today. I’ll do updating an entry in Part 5.

Character sets, collations, and sort orders, oh my!

Let’s start with some definitions. Sort order is easy enough – that’s the order things are supposed to go in. In English, that happens to be:

a b c d e f g h i j k l m n o p q r s t u v w x y z

In Carrajina (to choose an example at random) alphabetical order is:

a b c ch d dj e f g h i j l m n ñ o p r rr s t u v x z

So that takes care of sort order. Character sets are also easy. These are the set of characters for use. So ascii is and latin1 is and utf-8 is another set.

Collations are the rules used in a database to determine which letters are equivalent and what order to sort them in. So, in a sense, a collation is a combination of a character set and a sort order, plus rules about which characters are equivalent. In case-insensitive collations, capital A and lower-case a are equivalent. That’s useful, because otherwise all the words with that start with a capital letter would be sorted separately.

In MySQL, there are numerous collations that have the name pattern charset_collation_ci/cs. Examples are utf8_general_ci, latin1_general_ci, latin1_swedish_ci (which is the MySQL default), and so forth. The _ci stands for case-insensitive. _cs is case-sensitive, and then there is _bin as in ascii_bin and utf8_bin. _bin stands for “binary” and means that the collation is sorted in unicode code point order.

In MySQL, within _ci all accented letters are considered the equivalent of unaccented letters, so ‘à’, ‘á’, ‘å’, ‘ä’, and ‘ā’ are all considered to be the equivalent of ‘a’ when comparing and sorting. This means that as far as MySQL is concerned ä=a is a true statement. _bin collations do not do this. One can force MySQL to recognize accented characters as different (so that ‘n’ and ‘ñ’ are not considered to be the same letter) by using the keyword BINARY before the field name.

So which collation you use will determine how your data is compared and sorted.

There are ways to create new collations and to change sort order and equivalencies in existing collations. These require access to the server files, though, which makes them useless to those of us who host our sites on 3rd-party servers.

So here is my hack for creating a new sortorder for any character set. I am not saying this is the only way to do this (it’s not!) or even that it’s the best way to do this.

Let’s create a Carrajina sort order (while still using utf-general_ci collation). I do this by adding a field to the database called sort or something similar. In the case of Carrajina, I added a field called carrajina_sort to the carrajina_entry table and make it a VARCHAR datatype. I then wrote a function to parse each word and add a two digit code for each letter. Despite the fact that I am using numbers to sort, I am storing it as a string.I am also adding k, q, w, and y in case Carrajina ever adopts foreign words with those letters

a, A 10
b, B 11
c, C 12
ch, Ch 13
d, D 14
dj, Dj 15
e, E 16
f, F 17
g, G 18
h, H 19
i, I 20
j, J 21
k, K 22
l, L 23
m, M 24
n, N 25
ñ, Ñ 26
o, O, ó 27
p, P 28
q, Q 29
r, R 30
rr 31
s, S 32
t, T 33
u, U 34
v, V 35
w, W 36
x, X 37
y, Y 38
z, Z 39
[space], … 00
<sup> 000
-, ‘, </sup>

I’ve also set all spaces and ellipses to 00, the opening <sup> tag to 000 (and then the number after the tag will stay as is), and the closing tag, hyphens, and apostrophes are all set to empty. I then use the str_replace() function to substitute one for the other. Like so:

function getSort($value) {
    $chararray = array('<sup>', '</sup>', 'ch', 'dj', 'rr', 'ñ',
       'Ch', 'Dj', 'Rr', 'Ñ', 'ó',
       'a', 'b', 'c', 'd', 'e', 'f',
       'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p',
       'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
       'A', 'B', 'C', 'D', 'E', 'F',
       'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
       'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
       '-', "'", ' ', '...');
    $numberarray = array('000', '', '13', '15', '31', '26',
       '13', '15', '31', '26', '27',
       '10', '11', '12', '14', '16', '17',
       '18', '19', '20', '21', '22', '23', '24', '25', '27', '28',
       '29', '30', '32', '33', '34', '35', '36', '37', '38', '39',
       '10', '11', '12', '14', '16', '17',
       '18', '19', '20', '21', '22', '23', '24', '25', '27', '28',
       '29', '30', '32', '33', '34', '35', '36', '37', '38', '39',
       '', '', '00', '00');
    $value = str_replace($chararray, $numberarray, $value);
    return $value;
}

I then call each carrajina word, parse it, and update the row with the sort value.

$query = "SELECT carrajina_entryID, carrajina_term
    FROM carrajina_entry";

$stmt = $mysqli->stmt_init();
if ($stmt = $mysqli->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($id, $term);
    while ($stmt->fetch()) {
        //put each word into the array with the id as index value
        $wordarray[$id] = $term;
    }
    $stmt->close();
}

foreach ($wordarray as $id => $word) {
    $wordsort = getSort($word);
    $sortarray[$id] = $wordsort;
}

$query = "UPDATE carrajina_entry, SET carrajina_sort = ?
    WHERE carrajina_entryID = ?";

foreach ($sortarray as $id => $wordsort) {
    $stmt = $mysqli->stmt_init();
    if ($stmt = $mysqli->prepare($query)) {
        $stmt->bind_param("si", $wordsort, $id);
        $stmt->execute();
        $stmt->close();
    }
}

All done. Each entry now has a corresponding sort string in the carrajina_sort field.

As I said earlier, this is not the only way, or even the “best” way to do this. Those of you who have solved this problem in other ways, please leave a comment with your favorite solution.

 

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.

 

 

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

The challenge:
Take Adam Walker’s Carrajina dictionary 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

Step 2 involves importing data into a database. I  will skip blogging Step 2 for now. Putting data into a database is more complicated than taking it back out again. But I will blog it eventually. Promise.

Step 3: Getting the data back out of the database to generate the dictionary listings.

So, the first thing we need to do is connect to the database. I mentioned in a previous entry that PHP’s mysqli extension is the preferred extension to use with a MySQL database. Here’s how that works:

$host = "my_host"; //"localhost" or "http://mysql.host.com"...
$user = "my_username"; //an authorized user of MySQL
$password = "my_password"; //my_username's password
$database = "my_database"; //the database we want to use.

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_error) {
    die('Connect Error ('. $mysqli->connect_errno .') '
            . $mysqli->connect_error);
}

After connecting to the database, the next step is writing a select query to find the appropriate data. Querying the database with mysqli uses the following steps, initialize the statement, prepare the (parameterized) statement, bind the variables to the parameterized statement, and execute the query. (More on this in a future post.) One of the nice things about using PHP (or any programming language, really) to make dynamic pages is that you do not have to write a complicated SQL query to get everything from the database at once. Instead, you can write a sequence of queries and use PHP to do things with the results.

Adam currently has his dictionary set up so that each letter is on its own page. To duplicate this, I’ve used the following queries:

public function getList($mysqli, $letter) {
   $like = $letter . '%';  //% is the SQL wildcard character

   $query = "SELECT english_entryID FROM english_entry
   WHERE head_entryID IS NULL  /* so we don't get subentries */
   AND english_term LIKE ? ORDER BY english_term ASC";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("s", $like);
      $stmt->execute();
      $stmt->bind_result($entryID);
      while ($stmt->fetch()) {
         $listIDs[] = $entryID;
      }
      $stmt->close();        
   }
   return $listIDs;
}

What this does, when called, is execute an SQL statement that looks in the table english_entry and finds all the english_entryIDs in the table that match the following conditions: head_entryID does not exist (and therefore this is not a subentry) and english_term starts with the passed $letter. The ‘?’ in the query is replaced by the variable $like, which is $letter followed by the ‘%’ sign to match anything. So, if $letter = ‘a’, then the SQL statement becomes:

SELECT english_entryID FROM english_entry WHERE head_entryID IS NULL
AND english_term LIKE 'a%' ORDER BY english_term ASC;

This SQL statement returns a list of english_entryIDs

english_entryID
1
2
3

which is then put into a PHP array array( [0] => 1, [1] =>2, [2] =>3, ...); for ease of use.

Now that we have the array of english_entryIDs that start with ‘a’ and are not subentries, we can use more code to get each actual entry. For this project, I have put all the code that gets an English language entry into a class called EnglishListing. The following code is then used on the dictionary pages to generate a listing.

//make sure $letter is lower case
$letter = strtolower($letter);

$eng = new EnglishListing();

$list = $eng->getList($mysqli, $letter);
foreach ($list as $entryID) {
    echo $eng->getEntry($mysqli, $entryID);
}

So after calling getList(), for each english_entryID returned by the getList function, we call getEntry:

public function getEntry($mysqli, $entryID) {
    //an entry has a term, a pronunciation,
    //one or more defs (num, pos, deftext),
    //and 0 or more subentries (just like an entry).
    $termrow = $this->getTerm($mysqli, $entryID);
    $defrow = $this->getDefs($mysqli, $entryID);
    $subrow = $this->getSubentry($mysqli, $entryID);

    return "<dt>$termrow</dt><dd>$defrow $subrow</dd>\n";
}//end function

OK. That code breaks up the process of getting an entry into 3 steps: getTerm(), getDefs(), and getSubentry().

getTerm() executes an SQL statement that returns english_term and english_pronunciation. It then wraps each of these in span tags and returns $termrow:

private function getTerm($mysqli, $entryID) {
   $termrow = "";
   $query = "SELECT english_term, english_pronunciation
              FROM english_entry WHERE english_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($term, $pron);
      while ($stmt->fetch()) {
         $termrow .= "<span class='term'>$term</span>
                      <span class='pron'>$pron</span>";
      }
      $stmt->close();   
   }
   return $termrow;
}//end function

getDefs() executes an SQL statement that returns definition_number, part_of_speech, definition for each definition matched by the given english_entryID. Where only 1 definition exists, $defrow contains only the part_of_speech and definition values. Otherwise, it contains all the values. ‘.=’ in the PHP code concatenates the value to the existing variable. An entry with two definitions, then would have $defrow equal to “$definition_number. $part_of_speech $def. $definition_number. $part_of_speech $def. “.

private function getDefs($mysqli, $entryID) {
   $defrow = "";

   $query = "SELECT definition_number, part_of_speech, definition
      FROM english_definition
      WHERE english_entryID = ?
      ORDER BY definition_number ASC";

   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($defno, $pos, $def);
      $stmt->store_result(); //so I can use $stmt->num_rows
      while ($stmt->fetch()) {
         $defno = "<span class='defno'>$defno</span>";
         $pos = "<span class='pos'>$pos</span> ";
         $def = "<span class='deftext'>$def</span> ";
         if (($stmt->num_rows) == 1) {
            $defrow .= "$pos $def ";
         } else {
            $defrow .= "$defno. $pos $def ";
         }
      }//end while
      $stmt->close();        
   }//end if 

   return $defrow;
}//end function

getSubentry() starts by calling another function checkSubentry() which checks to see if the entry has any subentries. If there is a subentry, then checkSubentry() returns the english_entryIDs for the subentries. Otherwise it returns an empty array. getSubentry() then checks the length (number of values) of that array (must be greater than or equal to 1), and for each value in the array it calls getTerm() and getDefs().

private function getSubentry($mysqli, $entryID) {
   $subrow = "";

   $headentryIDs = $this->checkSubentry($mysqli, $entryID);

   if (count($headentryIDs) >= 1) {
      foreach ($headentryIDs as $entryID) {
         $subrow .= "<br />&#x2022; "
            . $this->getTerm($mysqli, $entryID);
         $subrow .= $this->getDefs($mysqli, $entryID);
      }
   } 

   return $subrow;
}//end function

private function checkSubentry($mysqli, $entryID) {
   $headentryIDs=array();

   $query = "SELECT english_entryID FROM english_entry
             WHERE head_entryID =?"; 

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($eentryID);
      while ($stmt->fetch()) {
         $headentryIDs[] = $eentryID;
       }
      $stmt->close();
   }
   return $headentryIDs;
}

That is the code for the EnglishListing class. The Carrajina side looks similar. There were some challenges in that ‘ch’ and ‘dj’ could not be listed under ‘c’ and ‘d’. I’ll talk about that later.

<?php

class CarrajinaListing {

public function getList($mysqli, $letter) {
   //extra letters ch, dj, ñ, rr
   $like = $letter . '%';  //% is the SQL wildcard character
   $like2 = ucfirst($letter) . '%'; 

   //BINARY means that MySQL doesn't ignore accented characters,
   //n-tilde, and the like. it also doesn't ignore capitalization
   //without the BINARY keyword, accents and capitalization are
   //ignored.
   $query = "SELECT carrajina_entryID FROM carrajina_entry ";

   switch ($letter) {
      case 'c':
         $query .= "WHERE (carrajina_term LIKE BINARY ?
                    OR carrajina_term LIKE BINARY ?) AND
                    (carrajina_term NOT LIKE 'ch%') ";
      break;
      case 'd':
         $query .= "WHERE (carrajina_term LIKE BINARY ?
                    OR carrajina_term LIKE BINARY ?) AND
                    (carrajina_term NOT LIKE 'dj%' ) ";
      break;
      default:
         $query .= "WHERE carrajina_term LIKE BINARY ?
                    OR carrajina_term LIKE BINARY ? ";
      break;
   }

   $query .= " ORDER BY carrajina_term ASC";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("s", $like);
      $stmt->execute();
      $stmt->bind_result($entryID);
      while ($stmt->fetch()) {
         $listIDs[] = $entryID;
      }
      $stmt->close();        
   }
   return $listIDs;
}//end function

private function getEntry($mysqli, $entryID) {
   //an entry has a term, a pronunciation,
   //one or more defs (num, pos, deftext),
   //one or more idioms (idiom, idiomdef),
   //an etymology, and sometimes a note
   $termrow = $this->getTerm($mysqli, $entryID);
   $defrow = $this->getDefs($mysqli, $entryID);
   $idioms = $this->getIdioms($mysqli, $entryID);
   $etymology = $this->getEtymology($mysqli, $entryID);
   $note = $this->getNote($mysqli, $entryID);

   return "<dt>$termrow</dt>
           <dd>$defrow $etymology $idioms $note</dd>\n";
}//end function

private function getTerm($mysqli, $entryID) {
   $query = "SELECT carrajina_term, carrajina_pronunciation
             FROM carrajina_entry WHERE carrajina_entryID = ?";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($term, $pron);
      while ($stmt->fetch()) {
           $termrow .= "<span class='term'>$term</span>
                        <span class='pron'>$pron</span>";
      }
      $stmt->close();        
   }//end if 

   return $termrow;
}//end function

private function getDefs($mysqli, $entryID) {
   $defrow = "";

   $query = "SELECT definition_number, part_of_speech, definition
             FROM carrajina_definition WHERE carrajina_entryID = ?
             ORDER BY definition_number ASC";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($defno, $pos, $def);
      $stmt->store_result(); //so I can use $stmt->num_rows
      while ($stmt->fetch()) {
         $defno = "<span class='defno'>$defno</span>";
         $pos = "<span class='pos'>$pos</span> ";
         $def = "<span class='deftext'>$def</span> ";
         if (($stmt->num_rows) == 1) {
            $defrow .= "$pos $def ";
         } else {
            $defrow .= "$defno. $pos $def ";
         }
      }//end while
      $stmt->close();        
   }//end if 

   return $defrow;
}//end function

private function getIdioms($mysqli, $entryID) {
   $idioms = "";

   $query = "SELECT carrajina_idiom, carrajina_idiom_definition
             FROM carrajina_idiom WHERE carrajina_entryID = ?
             ORDER BY carrajina_idiom ASC";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($idiom, $idiomdef);
      while ($stmt->fetch()) {
         $idioms .= "<span class='idiom term'>&#x2022; $idiom</span>
                     <span class='idiomdef'>$idiomdef</span> ";
      }//end while
      $stmt->close();        
   }//end if 

   return $idioms;
}//end function

private function getEtymology($mysqli, $entryID) {
   $etymology = "";

   $query = "SELECT etymology FROM carrajina_entry
             WHERE carrajina_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($etym);
      while ($stmt->fetch()) {
         $etymology .= "<span class='etymology'>$etym</span>";
      }//end while
      $stmt->close();        
   }//end if 

   return $etymology;
}//end function

private function getNote($mysqli, $entryID) {
   $note = "";

   $query = "SELECT note FROM carrajina_note
             WHERE carrajina_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($cnote);
      while ($stmt->fetch()) {
         $note .= "<span class='note'>$cnote</span> ";
      }//end while
      $stmt->close();        
   }//end if
   return $note;
}//end function

}//end class
?>

I know there are things in here that I didn’t explain. Consider this your excuse to take me to task and ask questions in the comments.

Edited 9-20-2011 for aesthetic reasons.

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

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: get the html source code for the current dictionary pages. Examine them carefully and figure out the structure. Outcome:

For the English to Carthaginian side:

Each entry consists of an English word, followed by a pronunciation. Then come definitions: a number if there’s more than one, a part of speech, and the definition text – a set of glosses in Carrajina sometimes with explanation. In addition, some entries have subentries, which have an English word or phrase, the definition number if there’s more than one, and the definition text.

I turned this into XML to make it easier to analyze. Turning it into a spreadsheet might also have worked. The dtd for this part of the dictionary is:

<!ELEMENT english-carthaginian (english_entry+)>
<!ELEMENT english_entry (english_term, pronunciation, definitions+, subentry*)>
<!ELEMENT english_term (#PCDATA)>
<!ELEMENT pronunciation (#PCDATA)>
<!ELEMENT definitions (definition_entry+)>
<!ELEMENT definition_entry (definition_number, part_of_speech, definition) >
<!ELEMENT definition_number (#PCDATA) >
<!ELEMENT part_of_speech (#PCDATA) >
<!ELEMENT definition (#PCDATA) >
<!ELEMENT subentry (english_term, pronunciation?, definition+)>

Creating the database model, I decided to put the definitions in their own table. Also, since the subentry is more or less identical to a head entry, I decided to put them in the same table. The diagram:

data model for english-carthaginian tables

For the Carthaginian to English side:

<!ELEMENT carthaginian-english (entry+)>
<!ELEMENT carrajina_entry (carrajina_term, pronunciation, definitions+, etymology?, note?, idioms*)>
<!ELEMENT carrajina_term (#PCDATA)>
<!ELEMENT pronunciation (#PCDATA)>
<!ELEMENT definitions (definition_entry+)>
<!ELEMENT definition_entry (definition_number, part_of_speech, definition) >
<!ELEMENT definition_number (#PCDATA) >
<!ELEMENT part_of_speech (#PCDATA) >
<!ELEMENT definition (#PCDATA) >
<!ELEMENT etymology (#PCDATA)>
<!ELEMENT note (#PCDATA) >
<!ELEMENT idioms (idiom, idiom_definition)>
<!ELEMENT idiom  (#PCDATA)>
<!ELEMENT idiom_definition (#PCDATA) >

Again, I put the definitions in their own table. Despite the fact that English and Carthaginian definitions both have the same structure, I will keep them in separate tables. Since an entry can have multiple idioms associated with it, those will go in their own table, too. Since etymologies are common, I will keep those in the same table with the terms. Notes, however, are not very common, so I will put them in their own table. This is a space- and memory-saving move. It is perfectly valid to put notes in the same table as terms as well.

data model for carthaginian-english tables

The full model:

full data model

Note: ignore the VARCHAR(45). I haven’t decided on the length of these fields yet.

Questions? Comments? Mistakes?