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
– Phil Perry, Commented Mar 28, 2014 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
– GCallie, Commented Mar 28, 2014 at 14:52
@Phil Perry: Instead of returning 0 records I would like all my records. — GCallie
– GCallie, Commented Mar 28, 2014 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
– Tularis, Commented Mar 28, 2014 at 15:14

Beauvais · Accepted Answer · 2014-03-28 16:32:22Z

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, 2014 at 16:32

answered Mar 28, 2014 at 14:36

Beauvais

2,3495 gold badges34 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL IN Operator ERROR when array is empty

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related