Looking Up Information in the Table

This exercise will build upon the ability to simply dump everything in a table. We will be more selective, based on what the user wants to see from the table. That means we have to have a means of getting information from the user operating the browser to the server, applying that information in the selection of rows to return, and returning those rows (or nothing at all).

The trick here is knowing what information is key to the user, because you don't want to have to allow complete ad-hoc access to the tables -- it makes the querying more difficult for the end user, and is often not necessary.

If there are only a few rows to search, one can simply scan the rows of a dumped table to find the desired information. However, if there are hundreds or more rows, it becomes time-consuming to do so -- though sorting the table by the key information would help. Although our tblEmployees table is small, we will use it to illustrate looking up information.

Here's the problem: Find all employees whose last name starts with a letter chosen by the user.

How do you do this via a web page? You need at least two web pages (they could be merged into one, but it complicates things) -- one to ask for the letter, and another to display the results.

Ask for the Letter and Look It Up

The script will be saved as choose_letter.php.

How is the letter choice going to be allowed? Several options are available; three are listed below:

Here are the details of each:

  1. listing all of the letters as links to a script

    This is fairly easy to generate in PHP: 

      <p>
  Choose a letter:
  </p>
  <ul>
  <?php
    $letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    for ($i=1; $i <= 26; $i++)
    {
       $letter = substr($letters, $i-1, 1);

       print '  <li><a href="lookup_letter.php?letter='.$letter.'">'.$letter."</a></li>\n";
    }

  ?>
  </ul>

    Here is the lookup_letter.php script that receives the query string (the part after the ? in the URL), assigns it to PHP variable $letter, and uses it in the $stmt variable to search on the first letter of each last name. 

    <html>
<head>
<title>Lookup Information</title>
</head>

<body>
<table border="1">
<caption>Employees</caption>
<tr>
  <th>Last Name
  <th>First Name
</tr>

<?php
  function dquote($str){
         return "'".str_replace("'","''",$str)."'";
  }

  $letter = $_GET["letter"];

  $letter = dquote($letter); // Ensure the string is safe from SQL injection

  $db = odbc_connect("iaidb", "css_test", "password") or die ("could not connect<br />");
  $stmt = "Select * from tblEmployees where substring(LastName,1,1) = $letter ORDER BY LastName, FirstName";

  $result = odbc_exec($db, $stmt);

  if ($result == FALSE) die ("could not execute statement $stmt<br />");

  while (odbc_fetch_row($result)) // while there are rows
  {
     print "<tr>\n";
     print "  <td>" . odbc_result($result, "LastName") . "\n";
     print "  <td>" . odbc_result($result, "FirstName") . "\n";
     print "</tr>\n";
  }

  odbc_free_result($result);

  odbc_close($db);
?>
</table>
</body>
</html>
  2. allowing the user select from a list using a drop-down box

    Create the drop-down box using the <select> tag to specify the values to select. 

      <p>
  Choose a letter:
  </p>
  <form method="post" action="lookup_letter.php">
  <select name="letter">
  <option value="0">- select -</option>
  <?php
    $letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    for ($i=1; $i <= 26; $i++)
    {
       $letter = substr($letters, $i-1, 1);

       print '  <option value="'.$letter.'">'.$letter."</option>\n";
    }

  ?>
  </select>
  <br />
  <input type="submit" name="submit" value="Submit">
  </form>

    With just a slight modification to lookup_letter.php, this variation can be handled by the same script. The original way referenced the query string; this additional way references the posted information. 

      $letter = $_GET["letter"];
  if (empty($letter)) $letter = $_POST["letter"];
  3. allowing the user to type the letter in a text box

    This is the simplest of all to express in HTML, but since the user is typing something versus selecting from a fixed list, there are more possibilities for things to go wrong -- such as the wrong character typed, more than one character being typed, or spaces in the input.

    To handle some of these issues, the maxlength attribute eliminates the possibility of multiple characters, while the size attribute makes the box smaller to guide the user. Nevertheless, checking the input in lookup_letter.php for valid values is a good idea... but how do you tell the user of their error and allow them to correct it? Is another web page required? 

      <p>
  Enter a letter:
  </p>
  <form method="post" action="lookup_letter.php">
  <input type="text" name="letter" maxlength="1" size="1">
  <br />
  <input type="submit" name="submit" value="Submit">
  </form>