SQL IN Operator ERROR when array is empty

Question

SQL IN OPERATOR

I use the IN operator because I work with arrays in my SQL statement.

For example:

$city[1] = "'Paris'";
$city[2] = "'London'";
$cityString= implode(", ", $city);

SELECT * FROM Customers
WHERE City IN ($cityString);

But in my code I use the value's of my GET or POST array. But when the array is empty there are no values, so my SQL statement won't work. How can I solve this without if statements and have a list of thousands of SQL statements.

The SQL query makes no sense if there is no list of cities (at least one) to compare against. To simply avoid a fatal error, you might have a dummy, impossible city name (e.g., '12345'), that should return 0 records. — Phil Perry, Mar 28 '14 at 14:32
@Tularis: Because it's not the only array I want to use, and if I make an if statement for every array it would be complex — GCallie, Mar 28 '14 at 14:52
@Phil Perry: Instead of returning 0 records I would like all my records. — GCallie, Mar 28 '14 at 14:52
you could also turn it into a function, or place it in a loop which loops over all your records/arrays/whatever, dynamically adding them to your resulting query ? Skipping empty search-parameters entirely (and speeding up your query that way!) — Tularis, Mar 28 '14 at 15:14
Please add to your OP a clear statement of what you want to happen for various $city arrays, including empty and invalid. What do you mean by "thousands of SQL statements"? Do you mean that $city could have thousands of different combinations of names, but you have one SQL statement? — Phil Perry, Mar 28 '14 at 17:21

Beauvais · Accepted Answer · 2014-03-28T16:32:22.903

3

You can check if the array is empty:

if(!empty($city)) {
    $query = "SELECT * FROM Customers WHERE City IN ($cityString)";
    $result = mysql_query($query);

} else {
    // .. do something else ?
}

edited Mar 28 '14 at 16:32

answered Mar 28 '14 at 14:36

Beauvais

2,149
4
28
63

SQL IN Operator ERROR when array is empty

1 Answers1