sql syntax error in my code of menus????

gatorwire

here is the error statement:

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 'AND bed = '' AND bath = '' AND garage = ''' at line 1

here is part of the code that its talking about:

$sql = "SELECT * FROM homes WHERE models LIKE '%$search%' LIMIT $from, $max_results";

if ($_POST['bed'] != "any") { 
$sql .= " AND bed = '".$bed."'"; 
} 

if ($_POST['bath'] != "any") { 
$sql .= " AND bath = '".$bath."'"; 
} 

if ($_POST['garage'] != "any") { 
$sql .= " AND garage = '".$garage."'"; 
}

This is part of my search.php page and these three parts are used as a drop down menus in a search form.

such as this....

My problem is that I just added a pagination code to my script and now it doesn't like the coding for the three options. Before the pagination script it worked perfectly.

How can i make this code better with no errors?

here is the code in action...

http://www.gatorwire.com/page/

Weedpacket

Your ANDs are part of the query's WHERE clause, and so have to go before the LIMIT clause.

So put together the query up to and including the LIKE, then attach your ANDs (if any), and then attach the LIMIT.
And while you're about it, use $_POST['bed'] instead of $bed, because $bed is evidently empty.

In fact, while you're about it, use [man]mysql_escape_string[/man] on the variables you're putting into the query, to block the opportunity for SQL-injection attacks.

gatorwire

Originally posted by Weedpacket
Your ANDs are part of the query's WHERE clause, and so have to go before the LIMIT clause.

So put together the query up to and including the LIKE, then attach your ANDs (if any), and then attach the LIMIT.
And while you're about it, use $_POST['bed'] instead of $bed, because $bed is evidently empty.

In fact, while you're about it, use [man]mysql_escape_string[/man] on the variables you're putting into the query, to block the opportunity for SQL-injection attacks.

Can you show me how it should look.

I changed it to this and I get no syntax errors but it doesn't search just for 2 bedrooms or just 2 baths. It brings up the entire database.

if ($_POST['bed'] != "any") {
$sql .= " AND bed = '".$bed."'";
}

if ($_POST['bath'] != "any") {
$sql .= " AND bath = '".$bath."'";
}

if ($_POST['garage'] != "any") {
$sql .= " AND garage = '".$garage."'";
}

$sql = "SELECT * FROM homes WHERE models LIKE '%$search%' LIMIT $from, $max_results";

I really don't understand where to put or how to use the escape string.