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. 🙂

10 PHP string functions

Here are 10 useful PHP string functions.

$string = "Hello World";

1, 2, 3: trim() along with ltrim() and rtrim() strip characters from a string. trim($string) without any further arguments will strip all spaces, carriage returns, new lines, null characters, and vertical tabs from $string.

echo trim($string, 'ld');

returns “Hello Wor” because trim() only trims the edges of a string. ltrim() trims from the left only, rtrim() from the right only, and trim() from both edges.

4: str_replace() replaces all instances of a given character with another. To strip out all ‘l’s from our string, use

echo str_replace('l', '', $string);

This returns “Heo Word”.

5, 6: strtolower() transforms a string into all lower-case letters.

echo strtolower($string);

returns “hello world”. strtoupper() makes everything upper case – “HELLO WORLD”.

7: strlen() returns the length of a string.

echo strlen($string);

returns “11”. This can be a useful way to check if you have the right string.

8: substr() returns a portion of the string. It helps to know the length of the string, because

echo substr($string, 3);

will start at the 4th character (or after the 3rd, if that’s easier for you) in the string and continue to the end, returning “lo World”.

echo substr($string, 10);

will return “d”. One can use negative numbers:

echo substr($string, -1);

will also return “d”. Here substr() starts at the end of the string and returns one character.

echo substr($string, -3);

return “rld”. You can also specify a length for substr() to return.

echo substr($string, 0, 3);

returns “Hel”. It starts at position 0, and returns 3 characters. Lengths can be negative. Though with a negative length, the beginning point is the end of the word.

echo substr($string, 0, -3);

returns “Hello Wo”.

echo substr($string, 1, -3);

removes the first character (i.e. it starts after the first character, and removes the last three characters, returning “ello Wo”.

9, 10: strpos() finds the first position of a character in a string.

echo strpos($string, 'l');

returns “2”. “H” is position 0, “e” is position 1, and “l” is position 2 (and 3, and 9). strpos() is case-sensitive. stripos() is the equivalent case-insensitive function.

Halloween CSS tricks

Here is a treat for you:

Happy
Halloween

 

Here’s how it is done.

First, create a div of equal width and height.

<div
style="width: 200px; height: 200px;" >
</div>

Then add rounded corners of half the width:

<div
style="width: 200px; height: 200px;
-moz-border-radius: 100px; 
-webkit-border-radius: 100px; 
border-radius: 100px; " >
</div>

Now you have a round div. The property -moz-border-radius is for Firefox, -webkit-border-radius is for Chrome and Safari, and border-radius is the actual CSS3 property. IE does not support this property, so if you have been wondering what the treat was, look at this post in Firefox, Chrome, or Safari.

Next, supply a solid black border of 1px.

<div
style="width: 200px; height: 200px;
-moz-border-radius: 100px; 
-webkit-border-radius: 100px; 
border-radius: 100px; 
border: 1px solid black;" >
</div>

You now have a round div with a border. How about the color? That’s a radial gradient.

<div style="width: 200px; height: 200px; 
-moz-border-radius: 100px; 
-webkit-border-radius: 100px; 
border-radius: 100px; 
border: 1px solid black;
background-image: -ms-radial-gradient(center, circle contain, 
#FECCB1 0%, #EA5507 100%); 
background-image: -moz-radial-gradient(center, circle contain, 
#FECCB1 0%, #EA5507 100%); 
background-image: -o-radial-gradient(center, circle contain, 
#FECCB1 0%, #EA5507 100%); 
background-image: -webkit-gradient(radial, center center, 
0, center center, 143, color-stop(0, #FECCB1), 
color-stop(1, #EA5507)); 
background-image: -webkit-radial-gradient(center, circle contain, 
#FECCB1 0%, #EA5507 100%); 
background-image: radial-gradient(center, 
circle contain, #FECCB1 0%, #EA5507 100%);">

All those different radial-gradient variations support different browsers. Won’t it be nice when everyone supports CSS3?

As for the text, that is done with the google font ‘Mountains of Christmas’. I added the line:

@import 
url(http://fonts.googleapis.com/css?family=Mountains+of+Christmas);

to my child theme’s style sheet, so I can now use the font. The text is in it’s own div, like so:

<div style="font-family: 'Mountains of Christmas', cursive; 
font-size: 3em; text-align: center; padding-top: 20px;">
Happy <br />
Halloween</div>

And this div is nestled inside the previous div.

Here is an image for those of you who might be stuck using a non-compliant browser.

image of css example for non-compliant browsers

And one further note. If you want to use this trick within a wordpress blog post, DO NOT use the Visual editor. It will lose half the css. Stick with the HTML editor—it is much better.

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.

 

 

Creating a user with SELECT privileges only on a specific database, Part 3

Part 3 of 3. This part covers using Dreamhost‘s web panel for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 1 covered the actual SQL commands. Part 2 covered using phpMyAdmin.

Using Dreamhost‘s interface.

I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

After logging in to Dreamhost‘s panel, click on Databases under Let’s Get Started!

Let's Get Started screenshot

Under the list of databases, click Add a New User on the right.

List of databases screenshot

Select “Create a new user now…” from the select menu, then fill in the username and password. Click Add new user now! Note that the instructions say: “This will grant a user full access to your “database_name” database. To limit this user’s database access privileges to “terjemar_newdict”, simply click their username on the screen that follows. ”

adding a new user screenshot

Now we get our success message: “User “selectonly_user” has been granted access to your “terjemar_newdict” database! If you’d like to fine-tune their permissions or set where they can connect from, do so from here.” You can also click on the username listed by the database.

selecting the user for editing screenshot

Make sure only Select is checked. The other options are Insert, Update, Delete, Create, Drop, Index, and Alter. The first three allow the use of those statements, so INSERT statements, UPDATE statements, and DELETE statements. Create allows for CREATE TABLE statements, Drop for DROP TABLE statements, Index for CREATE INDEX and DROP INDEX statements, and Alter for ALTER TABLE statements.

modifying privileges screenshot

Click on “Modify selectonly_user now!”.

final success message

And we have our success statement.

Creating a user with SELECT privileges only on a specific database, Part 2

Part 2 of 3. This part covers using phpMyAdmin for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 1 covered the actual SQL commands. Part 3 will cover using Dreamhost‘s web panel. I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

Using phpMyAdmin:

From the homepage, go to the Privileges tab.

phpMyAdmin homepage

There you will find an “add a new user” link.

add a new user link screenshot

Click that for the Add a New User form.

add a new user form screenshot

Fill this out. Make sure None is the option under Database for user, and do not check anything under Global privileges. Click GO.

success screenshot

This display the green success checkmark and gives the SQL for what has been done:

CREATE USER 'username'@'%' IDENTIFIED BY '***';
GRANT USAGE ON *.* TO  'username'@'%' IDENTIFIED BY '***' ...;

GRANT USAGE does not grant anything at all.

Underneath is the Edit User form (also reachable through the list of users).

Edit user form screenshot

Note that the first part is labelled Global privileges. We don’t want those, we want privileges for a specific database (‘test’). That is under the Global Privileges part. We select the database from the select box and get to the database’s privileges page:

database specific edit form screenshot

Here we check the box marked SELECT and click GO.

final success screenshot

And Success!. The SQL here reads:

GRANT SELECT ON test.* TO 'username'@'%';

Creating a user with SELECT privileges only on a specific database, Part 1

Part 1 of 3. This part covers the actual SQL commands for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 2 will cover using phpMyAdmin. Part 3 will cover using Dreamhost‘s web panel. I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

Using SQL:

First, you have to have GRANT privileges yourself to do this. If so, use the CREATE USER statement, like so:

CREATE USER 'username'@'hostname';

The above creates a user without a password. (Not a good idea.)

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

This creates the user and the password. The main issue with this syntax is that the password is right there in plain text. There is a MySQL function which creates an encoded password. That is the PASSWORD() function. Like so:

SELECT PASSWORD('badpwd');

returns ‘*AAB3E285149C0135D51A520E1940DD3263DC008C’. You can then use that encoded string in the CREATE USER statement:

CREATE USER 'username'@'hostname' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';

Make sure to include that PASSWORD after IDENTIFIED BY.  You can also use the ‘%’ wildcard for the hostname so that the given user can log on from any host:

CREATE USER 'username'@'%' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';

Specifying privileges is done using a GRANT statement:

GRANT SELECT ON database_name.table_name TO 'username'@'%';

This allows the user access only to the specified database and table and only for use of SELECT statements.

GRANT SELECT ON database_name.* TO 'username'@'%';

allows usage on all tables in the named database.

So, two statements will do it:

CREATE USER 'username'@'%' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';
GRANT SELECT ON database_name.* TO 'username'@'%';

Do you speak SQL? (Part 2: Creating tables)

Most SQL tutorials start with SELECT statements and then go into all sorts of detail about the various functions and elaborations available. These are really cool, and I will go into them eventually, but I have found that aside from basic syntax and a handful of functions most conlangers do not need to know all that in order to display a dictionary entry or list of entries. So I am going to skip all that for now and talk about creating tables.

The SQL for creating tables starts with an obvious CREATE TABLE followed by the name of the table and then a list of the column names with descriptive material in parentheses. Like so:

CREATE TABLE IF NOT EXISTS `definitions` (
  `definitionID` INT(11) NOT NULL AUTO_INCREMENT,
  `wordID` INT(11) NOT NULL,
  `definition_number` INT(2) NOT NULL DEFAULT '1',
  `part_of_speech` VARCHAR(25) COLLATE utf8_general_ci 
          DEFAULT NULL,
  `definition_text` TEXT COLLATE utf8_general_ci,
  PRIMARY KEY (`definitionID`),
  CONSTRAINT `fk_defword` FOREIGN KEY (`wordID`)
  REFERENCES `dictionary_word` (`wordID`)
);

The statement above creates the table named “definitions”. The phrase IF NOT EXISTS means that if a table by that name already exists, then nothing happens. If a table by that name does not exist, then the table is created with 5 columns, a primary key, and a foreign key constraint.

Let’s take this column by column. The first column created is called “definitionID”, it is described as an integer of 11 places maximum. (That’s the default for MySQL’s INT datatype.) It cannot be NULL, and the value is automatically incremented every time an entry is added to this table.

The next column is “wordID”, which is also an integer of 11 places maximum and also cannot be null. The third column is “definition_number”, which is an integer of 2 places maximum. That means its value cannot go above “99”. It also has a default value of “1”.

The fourth column “part_of_speech” has a description of VARCHAR(25). CHAR and VARCHAR are common datatypes for strings. CHAR has a fixed width, and VARCHAR has a variable width. In this column, VARCHAR’s maximum width or number of characters is 25. This column also has a defined collation of utf8_general_ci and a default of NULL. Actually, DEFAULT NULL is optional, since NULL is already the default of any column not designated as NOT NULL. Collations I will discuss some other time.

The fifth column “definition_text” has a datatype of TEXT. In many ways TEXT is simply VARCHAR without a maximum size. It used to be (MySQL version 5.0.3 and earlier) that VARCHAR was limited to a maximum size of 255 characters while TEXT could store up to 65536 bytes*. TEXT types have no stated size or default values.

After that comes the PRIMARY KEY definition. Each table ought to have a primary key, and it is best that the primary key is not derived from the data in the other columns. And auto-incremented integer is the usual form of a primary key, but the only requirements are that all values be unique and never null. Defining a primary key tells the database how to index the data in the table so it can be found in a query. Here we have declared “definitionID” to be the primary key.

The foreign key constraint (here arbitrarily named “fk_defword”) tells the database that one (or more, sometimes) of the columns in the table references a key (usually the primary key) in another table. This is how one connects data in one table to data in another table. Here the column “wordID” references the key “wordID” in the “dictionary_word” table.

Here’s another create table statement for you. If you have any questions, ask them in the comments.

CREATE TABLE `dictionary_word` (
 `wordID` int(11) NOT NULL AUTO_INCREMENT,
 `word` varchar(45) NOT NULL,
 `pronunciation` varchar(45) DEFAULT NULL,
 `etymology` text,
 PRIMARY KEY (`wordID`)
);

*but only if a TEXT column is the only thing in the table. Otherwise the maximum size of a TEXT column is 65536 bytes minus the number of bytes needed for all the other columns in the table.