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.

4 thoughts on “Converting static pages to dynamic pages using PHP and MySQL, Part 2”

  1. This has been such an incredible experience. I had no idea you were such a techie! I’m not accustomed to such easy and real communication with people who are so competent with tech. Usually I feel like it is taking every skill I have just to communicate the simplest meaing when communicating with techies. Communication with you has always been easy to the point that I never guessed you had these skills! I guess it is time for me to re-evaluate a stereotype. Not all tech people are difficult to communicate with.

  2. Actually, one of the questions I was going to ask (before you brought it up) was how to account for capitals. I like that capitals are stripped and made lower case, but sometimes (say, if I want to add a name), then I want for there to be a capital letter. I don’t suppose there’s any way to handle that…?

    Also, I think I now get $stmt = $mysqli thing. It’s kind of there just to “cleanse” the data so it can go in/come out without messing things up…?

  3. By “capitals stripped and made lower case”, I assume you are talking about search results, and how both upper and lower case are matched? That is actually dependent on the collation one uses. (I will write a collation post. I will.) Capitalization is maintained in the data no matter what. With case insensitive collations (these end in _ci), capitals are treated the same as lower case letters while searching and sorting. To force MySQL to match only capital letters while using a case insensitive collation, use the BINARY keyword after LIKE. So, SELECT… WHERE col_name LIKE ‘C%’ would find everything starting with the letter c, whether upper case or lower. SELECT… WHERE col_name LIKE BINARY ‘C%’ would only return matches starting with a capital C.

  4. As to $stmt = $mysqli. $mysqli variable holds the connection to the database. The $stmt variable holds the statement that one is sending to the database, even if the statement doesn’t really exist. $stmt = $mysqli->stmt_init(); basically says to create a variable ($stmt) that will hold a statement that will be prepared later.

Comments are closed.