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
– Newbi3, Commented Feb 13, 2014 at 10:23

senK · Accepted Answer · 2014-02-13 11:42:51Z

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, 2014 at 11:42

answered Feb 13, 2014 at 10:26

senK

2,8021 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Byate Over a year ago

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?

senK Over a year ago

possible reason would be the query failure or there will be no database connection(just print the query by echo and check)

Byate Over a year ago

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

senK Over a year ago

yes, for searching of string you must need enclose the value in quotes, updated the answer check populating the array

Raúl Monge · Accepted Answer · 2014-02-13 10:23:07Z

0

 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);

answered Feb 13, 2014 at 10:23

Raúl Monge

2232 silver badges8 bronze badges

Collectives™ on Stack Overflow

Constructing an SQL query around optional search parameters

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related