PHP/MySQL search - query builder not working

Report · Dec 29, 2011

Hi Folks,

Can anyone help me figure out what I have done wrong here.

I am trying to make a search system that builds a query according to the options selected by the user.

As well as a text box ('search') the user can choose from 3 drop down menus:

category

location

dealtype

The default for each of the drop downs is 'Any' which I have given a null value "". So if the string length of any of category, location or dealtype is greater than 1 they should be added to the query.

I'm in over my head here so any pointers would come in very handy.

Right now I have this error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Any way, here's the code:

--------------------------

// search query builder starts

if (isset($_GET['search'])){

//set search variable

$search=$_GET['search'];

// reset search query clause variables

$where_clauses= "";

$location="";

$category="";

$dealtype="";

// set search query variables with GET data

if (isset($_GET['location'])){$location=$_GET['location'];}

if (isset($_GET['category'])){$category=$_GET['category'];}

if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}

//test for "any" (any is set to "" in every case)

if (strlen($location >1)){

$where_clauses .= "AND location ='.$location'. ','";

}

if (strlen($category >1)){

$where_clauses .= "AND category ='.$category'. ','";

}

if (strlen($dealtype >1)){

$where_clauses .= "AND dealtype ='.$dealtype'";

}

//construct query

mysql_select_db($database_deals, $deals);

$query_rs_deals = "SELECT * FROM deals WHERE description LIKE '$search'"."'$where_clauses'";

$rs_deals = mysql_query($query_rs_deals, $deals) or die(mysql_error());

$row_rs_deals = mysql_fetch_assoc($rs_deals);

$totalRows_rs_deals = mysql_num_rows($rs_deals);

}

// search query builder ends

--------------------------------

Report · Dec 30, 2011

Dave, I don't work with PHP, but it seems you are not concatenating your strings corrected. In most languages, anything between double quotes are string literals - you can't put variables in there.

So this :$where_clauses .= "AND location ='.$location'. ','";

should probably be: $where_clauses .= " AND location =" .$location;

Same goes for the other optional where clauses.

And the final SQL

$query_rs_deals = "SELECT * FROM deals WHERE description LIKE " . $search . $where_clauses;

As I said, I don't really know PHP so I could be all wrong here. The best thing to do when you are having this type of problem is to echo the strings to the screen so you can see how they are being concatenated.