Constructing an SQL query around optional search parameters

Question

I'm attempting to construct an MySQL query around some optional search parameters in PHP. There are 3 search fields linking to the three columns in my database. The user should be able to search each column individually, or up to all 3 if they want to narrow down their results.

My PHP code is built so that the if one of the search fields is empty, it doesn't matter and it will continue the search in the other two fields. However, I'm struggling to get my head around how to construct my SQL query afterwards as my where clauses have already been specified in the $whereclauses array. I am returning the same columns in any search the user makes so that should make the SQL query quite simple and mean it only needs to be defined once.

My PHP code:

<?php
require '../db/connect.php';
$whereclauses = array();
$subsets = false;

// for every field
if(!empty($_POST['name']))
  {
  $subsets = true;
  $whereclauses[] = " ARTIST = ". mysql_real_escape_string(trim($_POST['name']));
  }

if($subsets)
  {
  $whereclauses = implode(", ". $whereclauses);
  }
else
  {
  $whereclauses ="";
  }

SQL query in PHP

 if(!empty($whereclauses)) {
 $sql = "SELECT 
            `ARTIST`, `TRACKTITLE`, `DATE`, `LOCATION`
        FROM 
            `table 3`";
 };    
 $result = mysql_query($sql);
 $data = array();
 while ($array = mysql_fetch_assoc($result)) {
 $data[] = $array;
 }
 header('Content-type: application/json');
 echo json_encode($data);

How can I add the $whereclauses array into my $sql query?

You can add more where clauses using the "AND" command. So you have to pick at least 1 of the 3 and at most 3 correct? — Newbi3, Feb 13 '14 at 10:23

senK · Accepted Answer · 2014-02-13T11:42:51.297

1

<?php
require '../db/connect.php';
$whereclauses = array("where 1=1");
$subsets = false;

// for every field
if(!empty($_POST['name']))
  {
  $subsets = true;
  $whereclauses[] = " ARTIST = '". mysql_real_escape_string(trim($_POST['name']))."'";
  }

Just implode the array in sql

$sql = "SELECT 
            `ARTIST`, `TRACKTITLE`, `DATE`, `LOCATION`
        FROM 
            `table 3` ".implode(" AND ",$whereclauses);

edited Feb 13 '14 at 11:42

answered Feb 13 '14 at 10:26

senK

2,782
1
27
38

Thanks for this! Definitely pointing me in the right direction. Just having a problem now after the `$result` is defined. The `mysql_fetch_assoc` line returns an error of `mysql_fetch_assoc() expects parameter 1 to be resource`. Am I constructing this part correctly? – Byate Feb 13 '14 at 11:24
possible reason would be the query failure or there will be no database connection(just print the query by `echo` and check) – senK Feb 13 '14 at 11:27
yeah i'm looking into this - however I think I can rule out database connection issues as when debugging the MySQL error it displays `Unknown column 'COLDPLAY' in 'where clause'`. So when I search for COLDPLAY it's searching the column name rather than the column value – Byate Feb 13 '14 at 11:37
yes, for searching of string you must need enclose the value in quotes, updated the answer check populating the array – senK Feb 13 '14 at 11:43

score 0 · Answer 2 · answered Feb 13 '14 at 10:23

 if(!empty($whereclauses)) {
     $sql = "SELECT 
            `ARTIST`, `TRACKTITLE`, `DATE`, `LOCATION`
        FROM 
            `table 3`";
     $sql.= " WHERE 1=1 ";
     foreach($whereclauses as $where){
        $sql.= " AND ".$where;
     }
 }

 $result = mysql_query($sql);
 $data = array();
 while ($array = mysql_fetch_assoc($result)) {
 $data[] = $array;
 }
 header('Content-type: application/json');
 echo json_encode($data);

Constructing an SQL query around optional search parameters

2 Answers2

Linked