picture of Joe Stump
I try and wade through as many postings on PHP General as possible throughout the day. Everything from "What does $$var mean?" to "How to I redirect someone to another page?" pop up. Two common problems pop up on the list frequently, one is "How do I build complex queries on the fly?", and the other is the problem of how to fully utilize all those cool features in your DB.
So here is what I plan to tackle in this article:
1.) Creating complex, powerful, queries to take advantage of your table schemas
2.) Building those queries on the fly according to user input
My main beef with people on the list is that they don't learn things in the proper order. They want to know how to redirect people without necessarily understanding what a "header" is much less what "302" means. The same goes for SQL - there is an underlying theory behind SQL, called "Relational Algebra". If you took any math classes beyond Math 101 then you have most likely played with its general theories.
Three Circle Theory
Above is a Ven Diagram that 99.99% of us have all seen, and many grew to love. It represents the basic foundations of Relational Algebra. If you want to know all the people who own a Toaster or own a TV you would choose area (green circle + blue circle), or, in SQL terms, "SELECT * FROM people WHERE own='Toaster' OR own='TV'".
Enough math already - I wanna build complex queries on the fly - NOW! Ok, well first we need some decent table schemas. I'm going to use my link tracker as an example. Here are the table schemas.
    categoryName char(50) NOT NULL,
    PRIMARY KEY (categoryID),
    UNIQUE ID (categoryID)

    categoryID tinyint(2) UNSIGNED NOT NULL,
    linkTitle char(150) NOT NULL,
    linkURL char(255) NOT NULL,
    linkDesc text,
    hits int(9) UNSIGNED DEFAULT '0',
    PRIMARY KEY(linkID),
    UNIQUE ID (linkID),
    KEY (categoryID),
    KEY (hits)
The first thing to note is that we have two tables here with a many to one relationship. "Many to one what?" What this means is that there can be "many" links in "one" category, thus creating a "many to one" relationship. Other types are "one to one" and "many to many". To keep things simple we won't be going into those. Another thing to note is that they share a common bond "categoryID" - this "relates" each link to its respective category. Notice that they have the exact same attributes - "tinyint(2) UNSIGNED NOT NULL" - which will optimize the JOIN we do on that field later in this article. "Optimize ... who needs to optimize - it's only gonna have 15 links in it!" - well it's a good practice to get in, I know MySQL needs to have EXACT declarations for an optimized JOIN - other DB's may not. A good article on normalizing your tables can be found here.
I'll leave how you enter the data into the tables up to you. I generally make a simple form to enter in cateogories into link_categories and then use that table in my form for link_information - choosing a category each time I enter a link and the categoryID being entered into the link_information table. So after we have some data entered into our tables we have something like this:
mysql> select * from link_categories;       
| categoryID | categoryName    |
|          1 | miester.org     |
|          2 | Linux           |
|          3 | miester's picks |
|          4 | PHP             |
|          5 | My Desktop      |
|          6 | Mini Howto's    |
6 rows in set (0.00 sec)

mysql> select * from link_information;
| linkID | categoryID | linkTitle       | linkURL                  | linkHits | linkInfo             |
|      1 |          2 | Linux.com       | http://www.linux.com     |       69 | Info about link here |
|      2 |          2 | Slackware Linux | http://www.slackware.com |       70 | Info about link here |
|      3 |          1 | Home            | http://www.miester.org   |      377 | Info about link here |
"But I only want to show the title, link, and category name on my homepage!" - OK that's where the JOIN comes into play here.
mysql> SELECT I.linkTitle as title, I.linkURL as href, C.categoryName as category 
    -> FROM link_information as I, link_categories AS C 
    -> WHERE I.linkCategory=C.categoryID;
| title           | href                     | category    |
| Linux.com       | http://www.linux.com     | Linux       |
| Slackware Linux | http://www.slackware.com | Linux       |
| Home            | http://www.miester.org   | miester.org |
3 rows in set (0.00 sec)
"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);
$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]">

= array(
'<=' => 'less than or equal to',
'>=' => 'greater than or equal to',
'=' => 'exactly',
'<' => 'less than',
'>' => 'greater than');

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


// Our initial SELECT query - we will build on to it.
$sql "SELECT * FROM link_information AS I, link_categories AS C";

$where[] = ' C.categoryID=I.categoryID ';

// If it's not "Any" then add on a where claus
if($f['category' != 'all')
$where[] = ' I.categoryID='.$f['category'];

// If "hits" is a valid number then add on a where clause
if(isset($f['hits_compare']) && strlen($f['hits_limit']))
$where[] = ' I.hits '.$f['hits_compare'].' '.$f['hits_limit'];

$sql .= ' WHERE '.implode(' ',$where);

// decide our limit values
if(strlen($f['record_limit']) && strlen($f['record_start']))
$limit = ($f['record_limit'] - $f['record_start']);
$f['record_start'] >= && $limit >= 0)
$sql .= ' LIMIT '.$f['record_start'].','.$limit;


That should build intelligent queries for you to then use at your discretion. You can view the above in action at www.miester.org/appendix/dynamic_queries.php and the code at www.miester.org/appendix/dynamic_queries.phps.
You can also add "ORDER BY" clauses with ease and the ability to choose which columns to perform those "ORDER BY" cluases on. In the grand scheme of things users are wanting not only more customization but better search results then they go to your webpages. Giving them the power of a SQL console in their browser will bring them one step close to their goal.
Finally here are some links of interest: