SQL Theory & How-To - Page 3

by: Joe Stump

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

« Previous Page

Comment and Contribute

0 Comments (click to add your comment)

Your comment has been submitted and is pending approval.

Author:

Joe Stump

Comment:

Name/nickname:
Email:

Comment:

(Maximum characters: 1200). You have characters left.