SQL Theory & How-To Page 3

“Whoa! You took some into from one table and some info from another to make a psuedo table!” – pretty cool, huh? And
the best thing about it is that if I want to change the name of category “Linux” to “FooBar” I only have to worry
about changing an arbitrary field in one record of the link_categories table. Notice that you don’t actually use
the word “JOIN” in the syntax, rather that feat is accomlished with the “I.linkCategory=C.categoryID” – which is
the equivilent of joining on those columns. Just think, I used to “SELECT * FROM link_information” and then
“SELECT * FROM link_categories” for EACH record from link_information! What a waste of CPU!!! I would like you to
know that I have stopped that nasty habit :O)

One thing to note about JOIN’s is that if there isn’t something to JOIN on it will fail. Meaning that if there are
links in link_information that have categoryID of 100 then they won’t be included in our JOIN because there isn’t
a categoryID of 100 in link_categories. It’s like trying to shake hands with only one hand, doesn’t work out too
well.

“OK, now I want to let my users perform advanced searches on certain fields in my DB” – well that’s slightly
trickier, but it can be done. To start out with we need to decide what data a user might be interested in filtering
out. My guesses at first glance are hits, category, and number of links to show. A simple keyword index could be
built using the descriptions, but that’s out of the scope of this article. Here is the form that I’ll be using for
the example:

<form method="post" ACTION="<? echo $PHP_SELF; ?>">
I want all links that are in the 
<select name="f[category]">
<option value="all">Any</option>
<?
  // get all of our categories - updated whenever a
  // category is added
  $sql = "SELECT * FROM link_categories";
  $result = mysql_query($sql);
  while($row = mysql_fetch_array($result))
  {
    echo '<OPTION VALUE="'.$row['categoryID'].'" ';
    // save the category from the previous search if there
    // is one.
    if($row['categoryID'] == $f['category'])
    {
      echo ' SELECTED ';
    }
    echo '>'.$row['categoryName'].'</OPTION>'."n";
  }
?>
</select> 
category that have 
<select name="f[hits_compare]">
<?
  $operands = array(
            '<=' => 'less than or equal to',
            '>=' => 'greater than or equal to',
            '=' => 'exactly',
            '<' => 'less than',
            '>' => 'greater than');
  while(list($key,$val) = each($operands))
  {
    echo '<OPTION VALUE="'.$key.'" ';
    if($key == $f['hits_compare'])
    {
      echo ' SELECTED ';
    }
    echo '>'.$val.'</OPTION>'."n";
  }
?>
</select>
<input type="text" value="<? echo $f['hits_limit']; ?>" name="f[hits_limit]" size="3"> 
hits. Also I only want to view records 
<input type="text" value="<? echo $f['record_start']; ?>" name="f[record_start]" size="3"> 
through 
<input type="text" value="<? echo $f['record_limit']; ?>" name="f[record_limit]" size="3">. 
<p>
<input type="submit" name="submit" value="Search!">
</form>

This isn’t a traditional “Advanced Search” form, but I bet my mom would understand it. The engineer in all of us
can see that we can logically build queries from this form – so let’s get working on that. Here is the code that
would effectively build an SQL query based on what the user wants.