PHPBuilder - SQL Theory & How-To Page 3



RSS Twitter
Articles Databases

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
1
|
2
|
3
|
4
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Joe Stump

Comment:



Comment:

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