Wizard Internet Solutions

Image via CrunchBase

I spent a few days lately working on a personal project and couldn’t get my search menu to function quite like i wanted. After several attempts with forms and such i wanted a less messy and more attractive search. After looking on eBay for something i realized that a similar search would work great for my needs and look good as well. Not only did it list all valid categories and options but it gave a total of the items listed for that category. After a few searches and a few attempts I got it working. Lets start by creating some demo data to work with, we will be using the below for this.

Click here for a working demo

The files we will be working with can be downloaded at the bottom of the page. The sql for this data is included.

Now that we have some data to work with lets get started with the PHP that will create this search menu. Assuming you already have a MySQL connection made in you PHP document we will start by defining our 2 sql queries. The first query is used to get the count of all the items in each category (i.e. how many shapes are red, how many are blue, how many are green etc) and is done by using the Count and If statements to get our data.

The following will get a count of how many items are red and setting the results as reds:

$sql = "SELECT Count(If((demo.color = 'red'),1,NULL)) AS reds FROM demo";
$query = mysql_fetch_array(mysql_query($sql));
echo "Red(".$query['reds'].")<br/>";

The output of this should be “Red(2)”. We will later turn this into a link. Using this same Count statement we will form a much more advanced string for all of our options and later we will add a conditional WHERE clause to only get the results of options that we have already chosen. After adding the other option counts you should have something like this:

$count_sql = "SELECT
 Count(If((demo.color = 'red'),1,NULL)) AS reds,
 Count(If((demo.color = 'blue'),1,NULL)) AS blues,
 Count(If((demo.color = 'green'),1,NULL)) AS greens,
 Count(If((demo.shape = 'square'),1,NULL)) AS squares,
 Count(If((demo.shape = 'triangle'),1,NULL)) AS triangles,
 Count(If((demo.shape = 'circle'),1,NULL)) AS circles
FROM demo";
$view_sql = "SELECT * FROM demo";

As you can see the the first variable $count_sql uses our counts to get the amount of items in each category, when we add a WHERE statement it will only show the results that match our search conditions. The second $view_sql is used to select the items that match the search for viewing. On to the next section:

if(isset($_GET['clear'])){
   $clear = $_GET['clear'];
   unset($_GET[$clear]);
}

This will simply clear the variable for a specific option. This is used so that a user can remove a previously selected option from the results. The following code then checks for the existence of any chosen options and if they exist it then adds the appropriate WHERE condition to both of the sql variables. The $c variable is used to determine whether or not the AND statement is needed. The $link variable is used to set the full links for the options links below.

if(isset($_GET['color']) || isset($_GET['shape'])){
   $c = FALSE;
   $count_sql .= " WHERE";
   $view_sql .= " WHERE";
   $link = '?';
   if(isset($_GET['color'])){
      if($c == FALSE){
         $count_sql .= " color='".$_GET['color']."'";
         $view_sql .= " color='".$_GET['color']."'";
         $link .= "color=".$_GET['color'];
         $c = TRUE;
      } else {
         $count_sql .= " AND color='".$_GET['color']."'";
         $view_sql .= " AND color='".$_GET['color']."'";
         $link .= "&color=".$_GET['color'];
      }
   }
   if(isset($_GET['shape'])){
      if($c == FALSE){
         $count_sql .= " shape='".$_GET['shape']."'";
         $view_sql .= " shape='".$_GET['shape']."'";
         $link .= "shape=".$_GET['shape'];
         $c = TRUE;
      } else {
         $count_sql .= " AND shape='".$_GET['shape']."'";
         $view_sql .= " AND shape='".$_GET['shape']."'";
         $link .= "&shape=".$_GET['shape'];
      }
   }
};

Now that we have our sql statements complete the next section will build the search options.  I have used the php if statements shorthand version which follows the (condition ? if true : else) format. This is a great shortcut that can be used inline with echo and variable assignments. The link is then followed by the count for all items that match that option and meet the conditions of the WHERE statements. If the option has already been selected then a clear link becomes available in its place. This clear link will clear the set variable

$result = mysql_query($count_sql);
$cnt_qry = mysql_fetch_array($result);
if(!isset($_GET['color'])){
   echo "Color<br/>";
   echo "<a href='".((isset($link)) ? $link."&" : "?")."color=red'>Red</a>(".$cnt_qry['reds'].")<br/>";
   echo "<a href='".((isset($link)) ? $link."&" : "?")."color=blue'>Blue</a>(".$cnt_qry['blues'].")<br/>";
   echo "<a href='".((isset($link)) ? $link."&" : "?")."color=green'>Green</a>(".$cnt_qry['greens'].")<br/>";
} else {
   echo "<a href='".((isset($link)) ? $link."&" : "?")."clear=color'>Clear Color</a><br/>";
}
if(!isset($_GET['shape'])){
   echo "Shape<br/>";
   echo "<a href='".((isset($link)) ? $link."&" : "?")."shape=square'>Square</a>(".$cnt_qry['squares'].")<br/>";
   echo "<a href='".((isset($link)) ? $link."&" : "?")."shape=triangle'>Triangle</a>(".$cnt_qry['triangles'].")<br/>";
   echo "<a href='".((isset($link)) ? $link."&" : "?")."shape=circle'>Circle</a>(".$cnt_qry['circles'].")<br/>";
} else {
   echo "<a href='".((isset($link)) ? $link."&" : "?")."clear=shape'>Clear Shape</a><br/>";
}

Following the search menu output is the results of the search. This is a simple where loop on the results of the $view_sql query we built earlier. It loops through the results and outputs them to the browser.

$result = mysql_query($view_sql);
while($view_qry = mysql_fetch_array($result)){
 echo $view_qry['id'].": ".$view_qry['name']." is ".$view_qry['shape']." and ".$view_qry['color'].".<br/>";
};

This can be customized in many ways however you need with as many options as you need. You can style this menu to fit almost anywhere in your site. As long as the sql statements get run before the search bar code and the while loop those elements can be placed anywhere in the code to be run below. As always i hope this is enough to get you started and i hope i made it simple enough to understand. If you have any questions or comments please leave them below and i will answer them. If you feel you can improve on this please do by all means. If you send me your revisions i will add them here with all credit due.

Files for this demo:

• Click here for a working demo
• Download Ebay Style Search Menu Files (archive)