Constructing SQL search strings from multiple html form elements?

[DCM]

I am trying to work out how to correctly format an SQL query string from user data taken

from multiple HTML text boxes/dropdowns.

 

As an example assuming i have a searach page on which the user can enter search criteria

into the following fields:

 

name - text box

site_location - drop down box (default set to 'search all')

job_title - drop down box (default set to 'search all')

manager - drop down box (default set to 'search all')

email - textbox

 

Its easy when all fields have user data set or one has user data set but when the user has entered data

into only a couple of the fields I have problems constructing the SQL query, namely when it comes to adding

the 'AND' keyword into the SQL query. No matter how i try to set out the logic in the PHP code i seem to end

up with at least one combination of the search critea leading to back to back AND statements.

 

I construct the SQL query by appending a string variable $query with the relevant search criteria, for example:

 

$query = "select * from employees where ";

if ($_POST['name'] != null)
{
	$query=$query."name='".$_POST['name']."' ";
}

if ($_POST['site_location'] != 'all')
{
	$query=$query."AND site_location='".$_POST['site_loaction']."' ";
}

        //etc

 

This does not work out though when fields are empty, I have tried adding additional if statements to check ahead

to see if fields further down in the query are required and if so append an 'and' but I cannot seem to get the

logic correct.

 

Is this something anyone else has come accross, if so is there a better solution than the route i am going down?

 

Thanks for reading.

 

[Psycho]

First, determine what fields to search, THEN add the WHERE and AND clauses if necessary. Also, I would add validation of the select field inputs to ensure the user has submitted a value that is in the list (malicious users can submit data not in your select list).

 

//Construct WHERE clause parts
$whereParts = array();
if(isset($_POST['name']) && strlen(trim($_POST['name']))>0)
{
    $whereParts[] = "`name` = '".mysql_real_escape_string(trim($_POST['name']))."'";
}
if(isset($_POST['site_location']) && $_POST['site_location'] != 'all')
{
    $whereParts[] = "`site_location` = '".mysql_real_escape_string(trim($_POST['site_location']))."'";
}
if(isset($_POST['job_title']) && $_POST['job_title'] != 'all')
{
    $whereParts[] = "`job_title` = '".mysql_real_escape_string(trim($_POST['job_title']))."'";
}
if(isset($_POST['manager']) && $_POST['manager'] != 'all')
{
    $whereParts[] = "`manager` = '".mysql_real_escape_string(trim($_POST['manager']))."'";
}
if(isset($_POST['email']) && strlen(trim($_POST['email']))>0)
{
    $whereParts[] = "`email` = '".mysql_real_escape_string(trim($_POST['email']))."'";
}

//Construct the final WHERE clause
$WHERE = (count($whereParts)>0) ? 'WHERE '.implode(' AND ', $whereParts) : '';

//Construct the query
$query = "SELECT * FROM employees {$WHERE}";

[DCM]

Thanks for the reply, i think i follow the logical (although I am using postgresql not mysql, my fault i should have said).

 

The part i dont really get is where the final WHERE clause is created, could you please elaborate on the follwoing line:

 

$WHERE = (count($whereParts)>0) ? 'WHERE '.implode(' AND ', $whereParts) : '';

 

I am assuming that the implode command outputs the contents of the $whereParts array but i dont get the syntax of how that line cycles through and creates the where part?

[Psycho]

That is the ternary operator. Basically it is the same as the following:

if (count($whereParts)>0)
{
    $WHERE = 'WHERE ' . implode(' AND ', $whereParts);
}
else
{
    $WHERE ='';
}

 

So, if there were any where parts it creates a string in the format:

'name` = 'enteredName' AND `job_title` = 'enteredTitle'

 

Otherwise the sting is empty

[DCM]

Thanks again, that helped a lot.