Using Array "Keys" In a MySQL WHERE Clause

Question

I have a data set that is generated by a Zip Code range search:

$zips:

key -> value
11967 -> 0.5
11951 -> 1.3

The key is the Zip Code (Which I need to query the Database for), and the value is the miles from the user entered zip code. I need to take the key (Zip Code) and search the database, preferably using a MySQL query similar to my current one:

$getlistings = mysql_query("SELECT * FROM stores WHERE zip IN ($zips)");

The other alternative is to change the array somehow in my code. I tried looking in code for where the array is generated originally but I couldn't find it. Any help would be greatly appreciated!! Thanks :)

Answer 1

You could convert the array keys to a SQL-compatible string. For example:

'11967', '11951'

and then use the string in the query.

Since the SQL query doesn't know what a php array is and there's no good way (that I know of) to extract just the keys and surround them in quotes, so this may be your best bet.

EDIT: As Ionut G. Stan wrote (and gave an example for), using the implode and *array_map* functions will get you there. However, I believe the solution provided will only work if your column definition is numeric. Character columns would require that elements be surrounded by apostrophes in the IN clause.

Answer 2

array_keys should be what you're looking for.

$zip = array_keys($zips);      # gives you simple array(11967, 11951);
implode(', ', $zip);           # results in: '11967, 11951'

Answer 3

For best performance, you should create a temporary table, fill it with your ZIP codes and query like this:

SELECT  *
FROM    stores
JOIN    temptable
ON      zip = tempvalue

Of course this will be more efficient only if you ZIP column is indexed.

Answer 4

This should do it:

// array_map sanitizes the data
$zip_codes = implode(', ', array_map('intval', array_keys($zips)));
$getlistings = mysql_query("SELECT * FROM stores WHERE zip IN ($zip_codes)");

Answer 5

Cannot comment the other answers, so one additional remark from my side. Depending on the country you are in and what you do with the data... In Germany there are ZIP-Codes starting with "0" so you should make sure that you either do not store them as numerical value if you want to compare them to other data (e.g. ZIP <-> geocoord-mappings) or make sure that you convert them to int everywhere and use filtering on the output.

Old ZIP codes had four numbers, new ones have five. So displaying a new ZIP with four numbers because the leading 0 is missing will lead to confusion.

Regarding use of a temporary table i would say it depends on the size of the table and how many zip codes are used in the query.

Answer 6

I just want to throw in that the previous code snippets gave me some syntax errors and the database just spitted out one entry instead of all relevant data. The following snippet worked for me:

implode("','", $zipNumbers);