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”
// 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
You have a few issues that I can see right off the bat.
First, the first two $where_clauses end with a comma but they are not needed.
Second, there are no spaces before each ‘AND’ in $where_clauses.
Third, there are single quotes around $where_clauses in $query_rs_deals. Remove them.
Fourth, strlen($location >1) is not valid. It should be strlen($location) >1
EDIT: Sorry, also remove the ‘.’ before $location, $category, $dealtype in each $where_clauses statement.
Your code should look something like this:
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'";
}
$query_rs_deals = "SELECT * FROM deals WHERE description LIKE '$search'".$where_clauses;
The error message is telling you that there are syntax errors in your sql query.
Echo out the actual query being run and the syntax errors should be easily seen. Then just fix your php code to make sure it creates a properly syntaxed sql query.
$query_rs_deals = "SELECT * FROM deals WHERE description LIKE '$search'"."'$where_clauses'";
[COLOR=#006400]echo $query_rs_deals;
die();[/COLOR]
OK, the error now is that the search field is blank and you still have it in the SQL query. Try modifying your code as follows:
// define $search to be empty by default
$search = ‘’;
// only set $search to a string if $_GET[‘search’] is set
if(isset($_GET[‘search’])){
$search= " description LIKE ‘". $_GET[‘search’] . "’";
}
if (strlen($location)>1){
[INDENT]// add ‘AND’ only if the search field was used
if(strlen($search) >0)){
$where_clauses .= ’ AND ‘;
}[/INDENT]
$where_clauses .= "location =’$location’";
}
if (strlen($category)>1){
[INDENT]// add ‘AND’ only if the search field or location was selected
if(strlen($search) >0 || strlen($location) >0){
$where_clauses .= ’ AND ‘;
}[/INDENT]
$where_clauses .= "category =’$category’";
}
if (strlen($dealtype)>1){
[INDENT]// add AND only if the search field, location or category were selected
if(strlen($search) >0 || strlen($location) >0 || strlen($category) >0){
$where_clauses .= ’ AND ‘;
}[/INDENT]
$where_clauses .= "dealtype =’$dealtype’";
}
$query_rs_deals = "SELECT * FROM deals WHERE ".$search.$where_clauses;
back at it after a brief night’s sleep . Feels like we are making progress.
I have two options now, the original code with your embellishments and Max heights re-write. Here’s where I am up to:
With the original code in place I now get this output when I echo out the SQL:
SELECT * FROM deals WHERE description LIKE ‘cut’ AND location =‘Brighton’ AND category =‘Hair and Beauty’ AND dealtype =‘BOGOFF’
That all looks good to me. But when I comment out the echo sql line and run the page as normal I get 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
When run the page with Max Height’s rewritten code I get the following errors:
Notice: Undefined variable - on these lines
47. if (strlen($location)>1){
54. if (strlen($category)>1){
61. if (strlen($dealtype)>1){
The page stops loading at that point.
Probably easy to fix I think but I will have to study it a bit cos I’m not sure what’s going on in there yet.
Happy to pursue either option if anyone can see a way to make it work.
Any ideas?
OH yeah, it was Programthis who posted the new code. (You could be right about the sleep).
Anyway, the original version is very close to working. In fact when I copy the SQl output form the page and paste it into phpmyadmin I get no error. Yet on the page I still get the 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
Does that explain anything?
The SQL code generated by the query builder looks okay. Here’s an example.
SELECT * FROM deals WHERE description LIKE ‘pizza’ AND location =‘Manchester’ AND category =‘Restaurants’ AND dealtype =‘free’
But I still get this error on the page:
I still get the 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
Just before you execute the query, [fphp]var_dump[/fphp] the $variable holding the final composed query you’re about to execute. Chances are, it’s not what you expect it is.
No change. Same error.
By execute “execute the query with this function”, I assume you meant paste that code into the page and load it up.
(clearly out of my depth here!?)
I think I did that right but no change. Did I do it right? here’s the code I have now can you just let me know if I did the execute_query thing correctly?
if (isset($_GET[‘location’])){$location=$_GET[‘location’];}
if (isset($_GET[‘category’])){$category=$_GET[‘category’];}
if (isset($_GET[‘dealtype’])){$dealtype=$_GET[‘dealtype’];}
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’";
}
$where_clauses = rtrim($where_clauses);
//construct query
mysql_select_db($database_deals, $deals);
$query_rs_deals = “SELECT * FROM deals WHERE headline LIKE ‘$search’ OR description LIKE ‘$search’”.$where_clauses.“”;
$rs_deals = execute_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);
}
Barring passing the $deals connection along, yes, you’re doing it correctly. You say “no change”, does this mean you’re still getting the error you previously mentioned?
