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.