In the first part of this series, I showed how to create a Master/Detail page set using built in Dreamweaver tools, and then add a basic search form querying against one field in the database. What if you want more than that though? The Dreamweaver recordset dialog box allows one filter against one field. This is sometimes useful, but usually not. Typically you might also want to allow other HTML fields into the search. You will want to search allowing multiple search words. Finally, you might want to search multiple database fields. I will address all three situations in this article, which will be targeted to PHP users only. The basic script used to start the exercises, which was the completed results page from Part 1, is saved as results_start.php in the accompanying download.

Multiple HTML Search Fields

The first point to address is what to do if you have multiple search fields on a web page. Let's say you have a keyword search box as presented in the last article that searches for text in a product name, but also want to allow a user to search a price range. A second search field would give the end user an opportunity to enter a maximum price. A third field would allow a minimum price. You can't do this in the simple Recordset dialog box any more. Open the recordset and switch to the Advanced view, if it's not already there. The SQL code looks something like this:

SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM products
WHERE ProductName LIKE %colname%

This is Dreamweaver-specific SQL code. Dreamweaver automatically uses a generic "colname" naming convention, which in a real-world application might be confusing. I've left the default in place here, however, to concentrate on the steps to make the search more advanced.

The single quotes and dynamic parameter necessary for the code to work are provided by the Recordset server behavior and show in Code View:

$query_rsMaster = sprintf("SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM products
WHERE ProductName LIKE %s", GetSQLValueString("%" . $colname_rsMaster . "%", "text"));

Note: Dreamweaver does not maintain your SQL formatting. It was formatted here for readability, but shows as one line in the code.

We'll take the first statement and add our two new parameters to it -- MinPrice and MaxPrice. There are no form fields yet, but that's ok -- we want the page to work whether a user has entered something or not.

Modify the SQL to read like this in the dialog box:

SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM products
WHERE ProductName LIKE %colname%
AND UnitPrice >= MinPrice
AND UnitPrice <= MaxPrice


Figure 1: Dreamweaver recordset dialog box after adding two new parameters

The variables will need to be added as shown in the following screenshots:


Figure 2: The MinPrice variable


Figure 3: The MaxPrice variable

And then, the "colname" parameter will need to be changed to allow a % symbol as the default value. This ensures that when a user does not enter a search term that all results are returned -- this allows the MinPrice and MaxPrice values to determine search results in addition to the search box.


Figure 4: The colname variable

If you save and browse to the page now, you should see all results.

Next, add two text fields to the page: MinPrice and MaxPrice. You can put these right under the search box, with labels of "Minimum Price" and "Maximum Price". If you browse the page now, you will still get all results. However, if you click the "Submit" button without entering anything, you will get no results. The default values you put into the Recordset dialog box only affect the page when the form is not submitted. We want them to also be in effect when someone doesn't fill in the field -- for example if a person searches for a specific keyword but doesn't enter a minimum or maximum price, then no records will be returned. This is not the desired outcome. There are at least two ways to handle this. The first way would be to modify the recordset code on the page and modify the SQL code if no parameters are passed. This is a good method for a hand-coded page, but we'll use another method that is more friendly to maintaining Dreamweaver recordset code. We will modify the default parameter definitions so that a blank field will still use the default.

$colname_rsMaster = "%";
if (isset($_GET['Search'])) {
$colname_rsMaster = $_GET['Search'];
}
$MinPrice_rsMaster = "0";
if (isset($_GET["MinPrice"])) {
$MinPrice_rsMaster = $_GET["MinPrice"];
}
$MaxPrice_rsMaster = "100000";
if (isset($_GET["MaxPrice"])) {
$MaxPrice_rsMaster = $_GET["MaxPrice"];
}

This section of code shows what is going on: a default value is created, but if a form field is defined, the value is used in its place. We'll change this to also disallow blank submissions:

$colname_rsMaster = "%";
if (isset($_GET['Search']) && $_GET["Search"] != '') {
$colname_rsMaster = $_GET['Search'];
}
$MinPrice_rsMaster = "0";
if (isset($_GET["MinPrice"])&& $_GET["MinPrice"] != '') {
$MinPrice_rsMaster = $_GET["MinPrice"];
}
$MaxPrice_rsMaster = "100000";
if (isset($_GET["MaxPrice"])&& $_GET["MaxPrice"] != '') {
$MaxPrice_rsMaster = $_GET["MaxPrice"];
}

Save the page and browse it. Now, when you click the submit button with no values in the form fields, all results are returned, however if you put a value in one of the boxes, the filters will work. For example, entering the value "50" into the MinPrice box will show only results where the price is higher than $50.

The recordset will now show a red check mark in the server behaviors panel, but will still list the fields correctly in the Bindings panel. However, editing the server behavior will now strip the hand-coded modifications. The completed page, to compare with your own, is saved as results1.php in the download that accompanies the article.

Dynamic SQL

To make the search even better, we'll allow the user to enter multiple keywords; this is where it gets tricky. The task of creating a dynamic SQL query is fairly straightforward, but it requires string manipulation and knowledge of SQL as well. Also, Dreamweaver has strict rules governing its recordsets, and if you change the way that the SQL is created by adding server-side conditional logic into the SQL, the recordset will cease to work in the Bindings panel. As long as you already have your recordset columns on the page where you want them, though, this isn’t a problem.

What you’ll have to do is remove the first part of the Where clause from the SQL statement while you are in Code view on the page, not from the recordset dialog box, and replace it with a new variable called $sqlString. The remaining SQL statement will then look like this:

$query_rsMaster = sprintf("SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM products
$sqlString
AND UnitPrice >= %s
AND UnitPrice <= %s"
,GetSQLValueString($MinPrice_rsMaster, "int")
,GetSQLValueString($MaxPrice_rsMaster, "int"));

Again, I've broken it down on multiple lines to show the code better, but Dreamweaver has the code all on one line. At this point, we will cease to use the Dreamweaver recordset box anyway, so formatting the code to make it more "programmer friendly" is a good strategy. PHP does not care if there are line breaks, but it will make things easier to read. Essentially, we've replaced WHERE ProductName LIKE %s with $sqlString, and removed the parameter from the list of parameters. A good trick to use is to make a copy of the original SQL statement and include it in the page within comments:

/*
$query_rsMaster = sprintf("SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM products WHERE ProductName LIKE %s AND UnitPrice >= %s
AND UnitPrice <= %s", GetSQLValueString("%" . $colname_rsMaster . "%", "text"),
GetSQLValueString($MinPrice_rsMaster, "int"),GetSQLValueString($MaxPrice_rsMaster, "int"));
*/

This way, if you ever need the Bindings or Server Behaviors panel functionality, you can comment out your custom code and replace it with the original code.

The next thing to do is to add a block of code to the page to parse the text field into three possible situations:

The first option will use the standard Where clause that was previously created. The second option will require the quotes to be stripped off before using the standard Where clause that was previously created. The third option requires that the words be separated. For instance, if the web user enters the words sauce, hot, pepper, the words need to be added to the Where clause like this:

WHERE ProductName LIKE '%sauce%' AND ProductName LIKE '%hot%' AND
ProductName LIKE '%pepper%'

The difficulty lies in the fact that there is no way of knowing how many words will be entered. You can write a short script to take care of the three situations. This pseudocode illustrates the functionality that the script will accomplish:

Define sqlString to hold the 'where' clause of the SQL statement. myField holds the incoming form field data
IF myField has quotes
  remove the quotes
  Build SQLstr as the Where clause using the phrase that had quotes around it
ELSE IF myField has commas or spaces
  separate the parts of myField
  construct a Where clause to allow for all of the substrings in myField
ELSE
  use myField as it stands and construct the Where clause
END OF IF STATEMENTS
Execute the SQL adding the Where clause to it

Following is the PHP code that you should insert right before the recordset is created.

$myField = $colname_rsMaster;
$fieldToSearch = "ProductName";
if (strstr($myField, '"')) {
  $myField= str_replace('"', '', $myfield);
  $sqlString = " WHERE $fieldToSearch LIKE '%" ;
  $sqlString .= str_replace("'", "''",$myField);
  $sqlString .= "%'" ;
}else if (strstr($myField,',') || strstr($myField,' ')){
  $myField = str_replace(" ",",",$myField);
  $splitField = explode(",", $myField);
  $sqlString = " WHERE";
  for ($i = 0; $i < count($splitField); $i++){
    $sqlString .= " $fieldToSearch LIKE '%";
    str_replace("'", "''", $splitField[$i]);
    $sqlString .= $splitField[$i];
    $sqlString .= "%'";
    if ($i < count($splitField) -1) $sqlString .= " AND ";
  }
}else{
  $sqlString = " WHERE $fieldToSearch LIKE '%" ;
  $sqlString .= str_replace("'", "''", $myField);
  $sqlString.= "%'" ;
}

You’ll notice that after you add the variable to the SQL statement, the recordset no longer shows up inside the Bindings panel, and you’ll see red checkmarks next to several items in the Server Behaviors panel. This is normal and merely indicates that Dreamweaver no longer recognizes the items as the items that it generated. When you begin hand-coding, you can close the Bindings panel and Server Behaviors panel -- they are useless at this point.

If you attempt to edit a Server Behavior with a red check mark in it, you should make sure that the red check mark isn’t there because you changed the code manually. By re-editing the Server Behavior, the red check mark will disappear, but so will your hand-coded script.

To view the $sqlString created by our script, put this bit of code at the top of the viewable page (after the <body> tag):

<?php
echo("The SQL code used is: <br>$query_limit_rsMaster");
echo("<br><br>");
?>

This will show the search query used by the script. For example, if you type in the following:

anton chef

The following will be shown as the query (showing line breaks here for readability):

SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM products
WHERE ProductName LIKE '%anton%'
AND ProductName LIKE '%chef%'
AND UnitPrice >= 0
AND UnitPrice <= 100000
LIMIT 0, 10

Using the standard Northwind table, two products will be returned.

The page now contains our second search situation -- searching on more than one word. The file is saved as results2.php in the accompanying download.

Searching multiple fields

Now all we have left to add is the ability to search mulitple database fields. Our sample Products table doesn't have another text field that would be a good field to search, but all products have categories, so we'll join to the Categories table to search on CategoryName in addition to ProductName. First, create the join in the SQL on the page:

$query_rsMaster = sprintf("SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM Products p
INNER JOIN Categories c
ON p.CategoryID = c.CategoryID
$sqlString
AND UnitPrice >= %s
AND UnitPrice <= %s"
,GetSQLValueString($MinPrice_rsMaster, "int")
,GetSQLValueString($MaxPrice_rsMaster, "int"));

There are two ways to make a search query work across multiple fields. The first way is to use OR statements in the query:

SELECT * FROM MyTable WHERE ProductName LIKE '%search%' OR CategoryName LIKE '%search%'

This is a good method, but would require a more complex script. We will use the easy way here: concatenate the fields. A sample search query would look like this:

SELECT * FROM MyTable WHERE CONCAT(ProductName, CategoryName) LIKE '%search%'

The fields are concatenated on the WHERE clause only. To create this funtionality, simply change this line in the original search script...

$fieldToSearch = "ProductName";

...to this:

$fieldToSearch = "CONCAT(ProductName, CategoryName)";

Now, the combined field will be searched each time. A search for "seafood" turns up 12 records. (Seafood does not appear in any of the product names.) A search for "seafood,crab" turns up one result.

The resulting script is saved as results3.php in the accompanying download.

Conclusion

There are many ways to create a database search, but a simple database search can be made slightly more robust with a few carefully placed instances of hand-coding in an otherwise automated Dreamweaver-created page.