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.
