Php Syntax Help

Question

I'm working on a search function, it was working fine four days ago, now it's returning 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 'AND state = 'AZ '' at line 1

what is the proper syntax for this line?

if($search_state !== "") {

$query .="AND state =  '" . $search_state .  " ' " ;

the entire portion is:

$query = "SELECT id, name, contact, contact2, address1, address2, city, state, postalcode, country, location, workphone, fax, email, webaddress, region, rail, food, forest, metal, bulk, chem, general, paper FROM companies_new WHERE dummy = '' ORDER BY state ASC ";

if($search_co !== "") {

$query .= "AND name LIKE '%" . $search_co ."%' ";

}


if($search_first !== "") {

$query .= "AND contact LIKE '%" .$search_first."%' ";

}

if($search_last !== "") {

$query .= "AND contact LIKE '%" .$search_last."%' ";

}

if($search_city !== "") {

$query .="AND city = ' " . $search_city . " ' ";

}

if($search_state !== "") {

$query .="AND state =  '" . $search_state .  " ' " ;

}

Answer 1

You can't put AND conjunctions for your WHERE clause after an ORDER BY. Your ORDER BY clause has to come after the entirety of the WHERE clause.

Answer 2

The WHERE part comes before the AND's in your code, which is wrong. This is what you want

$query = "SELECT id, name, contact, contact2, address1, address2, city, state, postalcode, country, location, workphone, fax, email, webaddress, region, rail, food, forest, metal, bulk, chem, general, paper FROM companies_new WHERE dummy = '' ";

if($search_co !== "") {

$query .= "AND name LIKE '%" . $search_co ."%' ";

}
...
$query .= " ORDER BY state ASC ";

Also, for completeness sake, you could make the code a bit easier to read:

$query .= "AND name LIKE '%$search_co%' ";

because your string is double quoted

Answer 3

You need to put the AND statements after the where, and before the sort_by. Move the sort_by to the end of the php (append to the query string), and it should work.

Answer 4

Try this:

$query = "SELECT id, name, contact, contact2, address1, address2, city, state, postalcode, country, location, workphone, fax, email, webaddress, region, rail, food, forest, metal, bulk, chem, general, paper FROM companies_new WHERE dummy = '' ";  

if($search_co !== "") {
    $query .= "AND name LIKE '%".$search_co."%' ";
}

if($search_first !== "") {
    $query .= "AND contact LIKE '%".$search_first."%' ";
}

if($search_last !== "") {
    $query .= "AND contact LIKE '%".$search_last."%' ";
}

if($search_city !== "") {
    $query .= "AND city = '".$search_city."' ";
}

if($search_state !== "") {
    $query .= "AND state = '". $search_state."' " ;
}

$query.= "ORDER BY state ASC"

As was said by others, your order by statement has to come last. Also, you had spaces surrounding the city name in the city = portion of the query. While this is valid SQL syntax, it's only going to return results where the city name is surrounded by spaces. I'm guessing that's not what you wanted.

Also, you may want to add some more fields to your order by. Right now, it will order by state, but will records come back randomly by state. Maybe something like

$query.= "ORDER BY state, city, name, id"

Finally, just FYI, if you're not carefully sanitizing your search inputs, this approach is susceptible to SQL Injection.

Answer 5

I think this might work:

$query = "SELECT id, name, contact, contact2, address1, address2, city, state, postalcode, country, location, workphone, fax, email, webaddress, region, rail, food, forest, metal, bulk, chem, general, paper FROM companies_new ";

$conditions = array();

if(!empty($search_co)) {
    $conditions[] = "name LIKE '%" . $search_co ."%' ";
}

if(!empty($search_first)) {
    $conditions[] = "contact LIKE '%" .$search_first."%' ";
}

if(!empty($search_last)) {
    $conditions[] = "contact LIKE '%" .$search_last."%' ";
}

if(!empty($search_city)) {
    $conditions[] = "city = '" . $search_city . "' ";
}

if(!empty($search_state)) {
    $conditions[] = "state =  '" . $search_state .  "' " ;
}

if (count($conditions) > 0) {
    $query .= " WHERE " . implode(' AND ', $conditions);
}

$query .= " ORDER BY state ASC";

Edit: The original version used empty, it should have been !empty. It's fixed now.

Edit 2: This also assumes you've already sanitized the variables using mysql_real_escape_string or similar.

Note that ORDER BY has to be after the WHERE clauses.

implode takes an array and turns it into a string with the specified string between the elements. So, given:

$myArray = array('A', 'B', 'C');

you can do

$myString = implode(' and ', $myArray);

which would put "A and B and C" into $myString.

Answer 6

Also, you seem to have spaces between the single and double quotes which will make the query like this: AND state = ' AZ ' which is probably not what you want. You can also avoid the additional quotes and concatenations in PHP by using this syntax:

$query .= " AND state = '$search_state' ";

Of course I have to mention that you should sanitize your inputs to protect against SQL injection.