What is wrong with this SQL IF Statement?

Question

so I am building a search script and meed to pass on two variables, but first I want to make sure that the SQL QUery is correct so I am hard-coding the variable for now. So my variable is

$comma_separated = "'Alberta','Ontario'";

This is getting passed through to the query, which looks like this:

$sql = "SELECT * FROM persons WHERE 1=1";



if ($firstname)
$sql .= " AND firstname='" . mysqli_real_escape_string($mysqli,$firstname) . "'";

if ($surname)
$sql .= " AND surname='" . mysqli_real_escape_string($mysqli,$surname) . "'";

if ($province)
$sql .= " AND province='" . mysqli_real_escape_string($mysqli,$comma_separated) . "' WHERE province IN ($comma_separated)";

$sql .= " ORDER BY surname";

and then when the query runs, I get this message:

cannot run the query because: 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 'WHERE province IN ('Alberta','Ontario') ORDER BY surname LIMIT 0, 5' at line 1

But to me the query looks right, what am I missing here?

Thanks in advance.

You have used `WHERE` twice in the query – M Khalid Junaid Mar 28 '14 at 16:53 — M Khalid Junaid, Mar 28 '14 at 16:53

Mike Brant · Accepted Answer · 2014-03-28T17:08:22.460

2

You can't have WHERE in there twice. You also seem to be trying to filter on province values in two different ways. Based on the assumption that $province will always be an array of values (even if only a single value is given), you can try this:

$sql = "SELECT * FROM persons WHERE 1=1";

if (!empty($firstname)) {
    $sql .= " AND firstname='" . mysqli_real_escape_string($mysqli,$firstname) . "'";
}

if (!empty($surname)) {
    $sql .= " AND surname='" . mysqli_real_escape_string($mysqli,$surname) . "'";
}

if (!empty($province)) {
    array_walk($province, function($value, $key_not_used) use ($mysqli) {
        return mysqli_real_escape_string($mysqli, $value);
    });
    $sql .= " AND province IN ('" . implode(',', $province) . "')";
}

$sql .= " ORDER BY surname";

edited Mar 28 '14 at 17:08

answered Mar 28 '14 at 16:54

Mike Brant

70,514
10
99
103

Something is up with your syntax on the implode. When I echo it out, I get `" . implode(, $province) . "`. I'll try to fix it while I wait for your comment, but you might beat me to the punch. – JLA Mar 28 '14 at 17:10
@JLA It looks fine to me. Have you done something different than what is shown with regards to string quoting? You might get that if you missed had a single quote somewhere earlier in that line. – Mike Brant Mar 28 '14 at 17:14
I've double checked this and no results are being retrieved. – JLA Mar 28 '14 at 17:29
OK, so I got this figured out in a far less elegant way than you present @Mike Brant. But here it is: So first I implode $province and then wrap it with single quotes as presented here (http://stackoverflow.com/questions/6102398/php-implode-101-with-quotes). SO my code looks like this: `$implosion = implode("','", $province); $implosion = "'".$implosion."'";` And then I pass it through the query as you suggested earlier. `if ($province) $sql .= " AND province IN ($implosion)";` And now everything works! Thanks for the help! – JLA Mar 28 '14 at 17:47
@JLA Don't forget to escape that input before using it in the query. – Mike Brant Mar 28 '14 at 18:29

score 1 · Answer 2 · answered Mar 28 '14 at 16:57

Your SQL contains two WHERE's.

SELECT * FROM persons WHERE 1=1
                      AND firstname='fn'
                      AND surname='sn'
                      AND province='p'
                      WHERE province IN ($comma_separated)
                      ORDER BY surname

Change the last bit to:

$sql .= " AND province='" . mysqli_real_escape_string($mysqli,$comma_separated) . "' AND province IN ($comma_separated)";

Which becomes:

AND province='p'
AND province IN ('Alberta','Ontario')

score -1 · Answer 3 · answered Mar 28 '14 at 16:53

-1

Change the last part to:

if ($province)
  $sql .= " AND province IN (" . mysqli_real_escape_string($mysqli,$comma_separated) . ")";

answered Mar 28 '14 at 16:53

Abhi Beckert

32,787
12
83
110

1

This is wrong actually. The single quotes MUST be present around the string values. That string is not where the problem lies. – Mike Brant Mar 28 '14 at 16:56
@MikeBrant yeah you're right. Dunno what I was thinking... Fixed it. – Abhi Beckert Mar 28 '14 at 16:58

What is wrong with this SQL IF Statement?

3 Answers3