#native_company# #native_desc#

SQL Theory & How-To Page 3

By Joe Stump
on January 3, 2001

“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
“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);

$row mysql_fetch_array($result))


'<OPTION VALUE="'.$row['categoryID'].'" ';

// save the category from the previous search if there

    // is one.

if($row['categoryID'] == $f['category'])








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))


'<OPTION VALUE="'.$key.'" ';

$key == $f['hits_compare'])








<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"> 


<input type="text" value="<? echo $f['record_limit']; ?>" name="f[record_limit]" size="3">. 


<input type="submit" name="submit" value="Search!">


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.