Create search query with optional fields

This solution involves manually editing the Recordset code generated by Dreamweaver. Once you do so, Dreamweaver no longer lists the Recordset in the Bindings panel, so it's important to lay out your search results page first with a simple Recordset.

For the search results, you need at least the primary key of each record and a short description. For the purposes of this recipe, let's call those database columns prop_id and short_desc.

1. Create a recordset using the Recordset dialog box in Simple mode. Select the table you want to search and the names of the columns you want to display in the results list.
2. Use the Bindings panel to lay out the search results, and apply a Repeat Region to display them. From this point onwards, you are going to edit the recordset code generated by Dreamweaver.
3. Open Code view, and locate the recordset code. It's immediately above the DOCTYPE declaration, and looks like this:

   mysql_select_db($database_search, $search);
   $query_search = "SELECT prop_id, short_desc FROM properties";
   $search = mysql_query($query_search, $search) or
   die(mysql_error());
   $row_search = mysql_fetch_assoc($search);
   $totalRows_search = mysql_num_rows($search);
    
     

4. Just above this code is the definition of the Dreamweaver GetSQLValueString() custom function. This prepares user input for use in a SQL query and sanitizes it to prevent SQL injection. It defines the following data types: text, int (integer), double (number with decimal fraction), and date. You need to create an associative array of the names of the search columns and their data types. To allow for wildcard searches, I have created an extra data type called like.
5. Call the array $expected, and insert it after first line of code shown in step 3. The fields in the search form should have the same names as the columns you want to search, and be followed by the data type like this:

   $expected = array('property_type' => 'text',
                     'price'         => 'int',
                     'city'          => 'like',
                     'district'      => 'text');
    
     

6.  Finally, add the following code after the second line shown in step 3. This will build the SQL query from the submitted values:

// Set a flag to indicate whether the query has a WHERE clause
$where = false;

// Loop through the associatiave array of expected search values
foreach ($expected as $var => $type) {
  if (isset($_GET[$var])) {
    $value = trim(urldecode($_GET[$var]));
    if (!empty($value)) {
      // Check if the value begins with > or <
      // If so, use it as the operator, and extract the value
      if ($value[0] == '>' || $value[0] == '<') {
        $operator = $value[0];
        $value = ltrim(substr($value, 1));
      } elseif (strtolower($type) != 'like') {
        $operator = '=';
      }
      // Check if the WHERE clause has been added yet
      if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      // Build the SQL query using the right operator and data type
      $type = strtolower($type);
      switch($type) {
        case 'like':
          $query_search .= "`$var` LIKE " . GetSQLValueString('%' .
$value . '%', "text");
          break;
        case 'int':
        case 'double':
        case 'date':
          $query_search .= "`$var` $operator " .
GetSQLValueString($value, "$type");
          break;
        default:
        $query_search .= "`$var` = " . GetSQLValueString($value,
"$type");
      }
    }
  }
}

If the value begins with > or <, the loop uses it as the operator. If the data type has been set to like in the associative array, it uses LIKE as the operator. Otherwise, it uses an equals sign. All the data is passed to the GetSQLValueString() function to sanitize it before inserting it into the SQL query.

The only things that need to be changed are the original recordset and the contents of the $expected array.