Building the Query | Dreamweaver Mx: Advanced Php Web Development

Chapter 8 - Creating a Search System

byGareth Downes-Powellet al.
Wrox Press 2003

We've built the simple user interface for our query and examined how we can get the values that the user enters into our PHP code. So let's move on to take a look at building the query that will get the data that the user requires out of our database.

The Base Query

We'll build our query with two parts. There's the unchanging part, or base query, and then a dynamic part which we will generate using PHP code. Let's start off by looking at the base query.

The first step is to create the correct SELECT statement, without filtering any values. To do that, we will create a normal recordset that will select the ID of the room, its number, the bed number, and the price:

    SELECT ID,bed,price,number FROM room

Executing this query on the MySQL server will return all the rooms in the room table of our database.

Creating the Recordset in Dreamweaver

Open the Application panel and select the Bindings tab. Click on the '+' button, and select the recordset menu item.

click to expand

This will pop up the window to design the recordset:

click to expand

Set the name of the recordset to rsSearch. Select the connection you set up for the hotel application, and select the room table. On the Columns option, click on the Selected: radio button, and then select the following fields: ID, price, bed, and number. Leave all the other options at their default values. Filter and Sort should be set to None. You can check to make sure that everything's OK by clicking on the Test button, which should give a list of the rows contained in the room table. Click on OK to finish the recordset.

Dreamweaver will now insert the following code in your page, above the <html> tag:

    <?php         mysql_select_db($database_connDWMXHotel, $connDWMXHotel);         $query_rsSearch = "SELECT ID, price, bed, number FROM room";         $rsSearch = mysql_query($query_rsSearch, $connDWMXHotel) or    die(mysql_error());         $row_rsSearch = mysql_fetch_assoc($rsSearch);         $totalRows_rsSearch = mysql_num_rows($rsSearch);    ?>

As you can see the third line contains our "base" query. We'll filter the results of this query, depending upon the values that the user has entered by manipulating the query itself.

Creating the Dynamic Query

Now that we have a working recordset, we will modify the recordset to integrate a WHERE statement.

Which Options Have Been Selected?

The user has two choices to make: the number of beds they're searching for, and the price range. The values of bed_option and price_option can be obtained in PHP with the following code:

    $HTTP_POST_VARS['bed_option']

and:

    $HTTP_POST_VARS['price_option']

Let's look at some example queries for some user inputs. If the user selected less than 3 beds, the query should look like this:

    SELECT ID,price,bed, number FROM room WHERE bed < 3

If the user selected more than 5 beds, the query should instead look like this:

    SELECT ID,price,bed,number FROM room WHERE bed > 5

And finally, if the user selected equal to 2 beds, the query would look like this:

    SELECT ID,price,bed,number FROM room WHERE bed = 5

So the only thing that needs to change between queries is the symbol used for comparison and the number to compare to. Therefore, we need to design a mechanism to add these two factors into the query dynamically, depending upon what the user has entered.

We're going to have to do a little hand coding here, because the possible values in $HTTP_POST_VARS["bed_option"] are less, more, or equal, but we need to insert the symbol <, >, or = into our query. To do that we will define a new variable, called $bComp. This variable will hold the symbol that we need depending upon the user input: >, <, or =.

We will use the following code in our page:

    <?php       if($HTTP_POST_VARS["bed_option"] == "less")       {          $bComp = "<";       }       else if($HTTP_POST_VARS["bed_option"] == "more")       {          $bComp = ">";       }       else       {          $bComp = "=";       }    ?>

It is important that you place this piece of code just before the recordset code in your document.

Now we have a variable named $bcomp that contains the value <, >, or = depending upon whether the user has selected the more than or less than option. We are now ready to modify the base query.

Building the New Query

To build the new query, open the Bindings panel. You should see the recordset rsSearch in the list of data sources:

click to expand

Double-click on the recordset, and click on the Advanced button, which will bring up the following window:

click to expand

Recordset Parameters

Click on the + button above the Variables box. This will open the Add Parameter dialog box which will help us to create a new recordset variable for your query. Enter the values as shown in the following window:

click to expand

The parameter's Name will be used only in the query building code that Dreamweaver generates when you finish creating the recordset.

The Default Value is a string value that the PHP script will use if the Runtime Value is not defined, so here we're going to make the default value the less than operator, <. Note that Dreamweaver will enclose the default value with double quotes when it generates the code. So, you can use anything as your Default Value, but don't use PHP code. This is because it won't be executed correctly, and the results are sometimes unpredictable.

The Runtime Value is the part of code that the recordset variable should take if everything goes all right, which is the value of the variable $bComp that we defined in our code earlier.

We can use the same process to add a parameter for the number of beds. Use the following values:

Name: bedNum
Default: 0
Runtime Value: $HTTP_POST_VARS["bed"]

The Variables table should now look like this:

click to expand

Inserting the Parameter Into the Recordset

To insert the newly created parameters, bedComp and bedNum, into the query, we just have to modify the SQL in the Recordset window:

    SELECT ID, price, bed, number FROM room WHERE bed bedComp bedNum

Your complete Recordset dialog box should look like this:

click to expand

Click OK to confirm the changes to your recordset.

Your PHP code should now look like the following:

    <?php require_once('Connections/connDWMXHotel.php'); ?>    <?php       if($HTTP_POST_VARS["bed_option"] == "less")       {          $bComp  =   "<";       }       else if($HTTP_POST_VARS["bed_option"] == "more"       {          $bComp = ">";       }       else       {          $bComp = "=";       }    ?>    <?php    $ bedComp_rsSearch = "<";    if (isset($bComp)) {      $ bedComp_rsSearch = (get_magic_quotes_gpc()) ? $bComp : addslashes($bComp);    }    $ bedNum_rsSearch = "0";    if (isset($HTTP_POST_VARS["bed"])) {      $ bedNum_rsSearch = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS["bed"] :    addslashes($HTTP_POST_VARS["bed"]);    }    mysql_select_db($database_connDWMXHotel, $connDWMXHotel);    $query_rsSearch = sprintf("SELECT ID, price, bed, number FROM room WHERE bed %s    %s", $ bedComp_rsSearch,$ bedNum_rsSearch);    $rsSearch = mysql_query($query_rsSearch, $connDWMXHotel) or die(mysql_error());    $row_rsSearch = mysql_fetch_assoc($rsSearch);    $totalRows_rsSearch = mysql_num_rows($rsSearch);    ?>

Note that, except for the little bit of code we wrote at the top, Dreamweaver has written all the rest of the code for us. If you want to change something in the recordset, it's much easier and less error-prone to use Dreamweaver rather than trying to manually edit this code.

Now, we need to generate the code to modify the query for the price range that the user entered. The methodology is exactly the same as for the bed options. First we need to do a little bit of hand coding, which we'll place just after the hand coding that we did before:

    <?php       if($HTTP_POST_VARS["price_option"] == "above")       {          $pComp = ">";       }       else       {          $pComp = "<=";       }    ?>

This bit of code has a very similar function to the code that we have had before. $HTTP_POST_VARS["price_option"] will contain either above or below, depending upon user input. We've defined another variable, $pComp that will have the value > if $HTTP_POST_VARS["price_option"] contains above, otherwise it will have the value <=. Now we need to go back into the recordset and edit it so that it has two new parameters, pComp and pNum, exactly as we did for the beds. Change the SQL for the recordset as follows:

    SELECT ID ,price,bed, number FROM room WHERE bed bComp bedNum AND price pComp pNum

This will mean that the search system will only return rooms that match both the user's price and bed criteria. If we wanted to make a search system that returned rooms that matched either of the user's criteria, we could use the following:

    SELECT ID ,price,bed, number FROM room WHERE bed bComp bedNum OR price pComp pNum

The final code looks like this:

    <?php require_once('Connections/connDWMXHotel.php'); ?>    <?php       if($HTTP_POST_VARS["bed_option"] == "less")       {          $bComp ="<";       }       else if($HTTP_POST_VARS["bed_option"] == "more"       {          $bComp = " >";       }       else       {          $bComp = " =";       }    ?>    <?php       if($HTTP_POST_VARS["price_option"] == "above")       {          $pComp = ">";       }       else       {          $pComp = "<=";       }    ?>    <?php    $bedComp_rsSearch = "<";    if (isset($bComp)) {      $bedComp_rsSearch = (get_magic_quotes_gpc()) ? $bComp : addslashes($bComp);    }    $bedNum_rsSearch = "0";    if (isset($HTTP_POST_VARS["bed"])) {      $bedNum_rsSearch = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS["bed"] :    addslashes($HTTP_POST_VARS["bed"]);    }    $pComp_rsSearch = "<=";    if (isset($pComp)) {      $pComp_rsSearch = (get_magic_quotes_gpc())? $pComp : addslashes($pComp);    }    $pNum_rsSearch = "0";    if (isset($HTTP_POST_VARS["price"])) {      $pNum_rsSearch = (get_magic_quotes_gpc())? $HTTP_POST_VARS["price"]:    addslashes($HTTP_POST_VARS["price"]);    }    mysql_select_db($database_connDWMXHotel, $connDWMXHotel);    $query_rsSearch = sprintf("SELECT ID, price, bed, number FROM room WHERE bed %s %s    AND price %s %s",    $bedComp_rsSearch,$bedNum_rsSearch,$pComp_rsSearch,$pNum_rsSearch);    $rsSearch = mysql_query($query_rsSearch, $connDWMXHotel) or die(mysql_error());    $row_rsSearch = mysql_fetch_assoc($rsSearch);    $totalRows_rsSearch = mysql_num_rows($rsSearch);    ?>