Using an Array to Build an SQL Select Query

SELECT * FROM ledb_events WHERE keywords LIKE '%jeff%' AND keywords LIKE '%crys%' 
AND date_start>='1987-00-00' AND date_start<='1988-12-31' 
AND ((access<=40 AND is_approved>0) OR (created_by='abanana' AND access<90))
ORDER BY date_start ASC LIMIT 0,20

That is a typical SQL statement showing actual values sent to the MySQL database. Everything between the WHERE and ORDER BY keywords is built using an array. It starts with the Search page (shown below). The user selects one or more of the Search checkboxes enabling areas of the form, enters or selects data, then Submits the form.

Using this Search form example, Keywords and Date-range are checked, therefore the variables $form_criteria_keywords and $form_criteria_rangeyears are TRUE. Both Keyword 1 and Keyword 2 have data, so neither variable $form_key1 or $form_key2 is NULL. Every statement gets executed within the if statements. (Only pertinent code is shown below and other form related statements have been omitted.)

  if($form_criteria_keywords) {
    if($form_key1 != NULL) {
      $hasdata = TRUE;
      $where[] = "keywords LIKE '%$form_key1%'";
      $criteria[] = " KEYWORDS:$key1";
    } else {
      header ('Location: event-search.php?error=2'); exit();  // KEYWORD1 WAS EMPTY
    }
    if($form_key2 != NULL) {
      $where[] = "keywords LIKE '%$form_key2%'";
      $criteria[] = ",$key2";
    }
  }
  if($form_criteria_rangeyears) {
    $hasdata = TRUE;
    $dateyear1 = $form_year1 . "-00-00";
    $dateyear2 = $form_year2 . "-12-31";
    $where[] = "date_start>='$dateyear1' AND date_start<='$dateyear2'";
    $criteria[] = " FROM $form_year1 TO $form_year2";
  }

After the code is evaluated, there are 3 values in the $where array as follows:

$where[0] = "keywords LIKE 'jeff'"
$where[1] = "keywords LIKE 'crys'"
$where[2] = "date_start>='1987-00-00' AND date_start<='1988-12-31'"

Also added to the $where array is the following:
$where[3] = "((access<=40 AND is_approved>0) OR (created_by='abanana' AND access<90))"

This matches a user's access level (abanana has an access level of 40) with a record's access level, but allows the user (abanana) to see their own entries whether or not the entry has been approved by the administrator.

The following code builds the WHERE string from the values in the $where array. For every array value after the first, an AND is inserted.

After the WHERE string has been created. The following $sql statement is sent to the database:

$sql = "SELECT * FROM ledb_events WHERE $where_string ORDER BY $orderby
    LIMIT $record_start,$num_records";

Finally, here are the search results using the Select statement first shown at the top of this page.