Custom database search depending on form inputs

I have been working on a record database and everything is complete now except the search.

I have this code and it works as long as only one form input is passed to the display page e.g. performer or place.
NOTE: There is more to the slect query as I am joining another couple of tables but have left that part out to keep the posted code a bit shorter


$sql = 'Select 

performer.full_name,
 album.album_ID,
 album.source,
 company.name,
 album.file_number,
 source.origin,
 Aside.description AS titleA,
 Bside.description AS titleB,
 loc_sideA.place AS locA,
 loc_sideB.place AS locB
 
 FROM album
  
 WHERE ';
 
if ( !empty($_REQUEST['performer']) ) { $sql .= "performer = '$performer'"; }
if ( !empty($_REQUEST['title']) ) { $sql .= "titleA_ID OR titleB_ID  = '$title'"; }
if ( !empty($_REQUEST['place']) ) { $sql .= "locationA_ID OR locationB_ID  = '$place'"; }
if ( !empty($_REQUEST['company']) ) { $sql .= "name = '$company'"; }
if ( !empty($_REQUEST['source']) ) { $sql .= "source.origin = '$source'"; }

echo $sql;

I can also get it to work if the performer is always chosen by adding an AND to the where code after the performer check:


if ( !empty($_REQUEST['performer']) ) { $sql .= "performer = '$performer'"; }
if ( !empty($_REQUEST['title']) ) { $sql .= "AND titleA_ID OR titleB_ID  = '$title'"; }
if ( !empty($_REQUEST['place']) ) { $sql .= "AND locationA_ID OR locationB_ID  = '$place'"; }
if ( !empty($_REQUEST['company']) ) { $sql .= "AND name = '$company'"; }
if ( !empty($_REQUEST['source']) ) { $sql .= "AND source.origin = '$source'"; }

Obviously if I do not have a performer the code fails as the first thing after the WHERE will be an AND

There must be a cunning way around this but I can not think of it!

I would put AND after every conditional then use trim() to remove the last AND, or have this at the end

$sql .= "1 = 1";

depending on if you want results returned when nothing is REQUESTed.

Hi @Rubble,

Try this:


  # DEBUG: CHANGE 0 to 1 to activate
  if(1):
   $_REQUEST = array();
     if(0) $_REQUEST['performer'] = 'TESTING performer';
     if(0) $_REQUEST['title']     = 'TESTING title';
     if(0) $_REQUEST['place']     = 'TESTING place';
     if(0) $_REQUEST['company']   = 'TESTING company';
     if(0) $_REQUEST['source']    = 'TESTING source';
     echo '<pre>$_REQUEST ===> ';
      print_r($_REQUEST);
     echo '</pre>';
  endif;

# TEST for NO #_REQUESTS
if( isset($_REQUEST) && count($_REQUEST)):
  $sql = "
  Select 
   performer.full_name,
   album.album_ID,
   album.source,
   company.name,
   album.file_number,
   source.origin,
   Aside.description AS titleA,
   Bside.description AS titleB,
   loc_sideA.place AS locA,
   loc_sideB.place AS locB
   FROM album
   WHERE 1=1";

  $performer  = isset($_REQUEST['performer']) ? $_REQUEST['performer'] : NULL;
  $title      = isset($_REQUEST['title'])     ? $_REQUEST['title']     : NULL;
  $place      = isset($_REQUEST['place'])     ? $_REQUEST['place']     : NULL;
  $company    = isset($_REQUEST['company'])   ? $_REQUEST['company']   : NULL;
  $source     = isset($_REQUEST['source'])    ? $_REQUEST['source']    : NULL;

  $sql .= ! isset($performer) ? NULL : " AND performer = '$performer'";
  $sql .= ! isset($title)     ? NULL : " AND (titleA_ID OR titleB_ID) = '$title'"; 
  $sql .= ! isset($place)     ? NULL : " AND (locationA_ID OR locationB_ID) = '$place'";  
  $sql .= ! isset($company)   ? NULL : " AND name = '$company'";  
  $sql .= ! isset($source)    ? NULL : " AND source.origin = '$source'";     

  # DISPLAY SQL
    $result = explode(',', $sql);  
    foreach($result as $line):
      echo '<br />' .$line, ',';
    endforeach;

else:
  echo 'Yes we have NO $_REQUESTS?????';
endif; # ( $isset($_REEQUEST) && count($_REEQUEST)):

  exit;

Output

$_REQUEST ===&gt; Array
(
    [place] =&gt; TESTING place
    [company] =&gt; TESTING company
)


Select performer.full_name,
album.album_ID,
album.source,
company.name,
album.file_number,
source.origin,
Aside.description AS titleA,
Bside.description AS titleB,
loc_sideA.place AS locA,
loc_sideB.place AS locB FROM album WHERE 1=1 AND (locationA_ID OR locationB_ID) = 'TESTING place' AND name = 'TESTING company',

After all these years I did not know you could use trim( ) to remove more than just white spaces!

Two good simple methods @Mittineague ; I didn’t think it could be that easy. I will test out @John_Betong ; method later.