Advanced Search

Advanced search: the wrong way

In the advanced search, you can specify exactly which database fields will be searched, and you can specify different search terms for different fields. This is advantageous, for example, if you are looking for Phase IV Rumbas: You can enter IV in the Phase box, and Rumba in the Rhythm box. If you tried doing a similar search using the easy method, you might find extraneous results that have the string Rumba or IV in other fields, that don't really apply to your specific search objective.

This kind of search requires more extensive processing of the search terms, mostly because some or most of the form fields might be blank, in which case they don't apply at all. So, for example, if we were to search like this, it might not give us the results we expect, because if any of those form fields is left blank, we'll be feeding blank strings into the MySQL query.

  my $sql = "SELECT * FROM gen44_rom WHERE " . 
         "DanceName LIKE '%$dance%' OR " .
         "Rhythm LIKE '%$rhythm%' OR " .
         "Phase LIKE '%$phase%' OR " .
         "Choreographer LIKE '%$choreo%' OR " .
         "Label LIKE '%$label%' OR " .
         "Record LIKE '%$record%' OR " .
         "Year LIKE '%$year%' OR " .
         "Month LIKE '%$month%' OR " .
         "Extra LIKE '%$extra%'; ";

Advanced search: the right way

Instead, we explicity check each form field and only add it to the search query if the user filled something in. This takes considerably more care:

  my $added = 0;
  my $sql = "SELECT * FROM gen44_rom WHERE ";

  if ($dance ne '') {
    $sql = $sql . "DanceName LIKE '%$dance%' ";
    $added = 1;
  }

  if ($rhythm ne '') {
    if ($added) { $sql = $sql . " AND "; }
    $sql = $sql . "Rhythm LIKE '%$rhythm%' ";
    $added = 1;
  }

  if ($phase ne '') {
    if ($added) { $sql = $sql . " AND "; }
    $sql = $sql . "Phase LIKE '%$phase%' ";
    $added = 1;
  }
  // and so on ... and so on ... and so on ...

Easy Search »