Jump to content


Photo

Using an array in MySQL where id IN ($array)?


  • This topic is locked This topic is locked
5 replies to this topic

#1 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 17 July 2006 - 12:28 AM

I have an array - $zips = array('92120', '92127')

$zips actually contain a hundred more is generated dynamically.

Now, can I use this kind of SQL?

SELECT username, zip FROM usertable
WHERE zip IN ('$zips')

I tried but it doesn't work. Basically, $zips is an array that contain a lot of zip codes from a class I'm using. However, I can't use an array in a "IN" statement for MySQL?

#2 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 17 July 2006 - 12:32 AM

no you can, you have to separate all zipcodes into a string:
SELECT username, zip FROM usertable WHERE zip IN ('92120', '92127');

it's easy to write a snippet of code to convert your array into a string like that.
Life's too short for arguing.

#3 akabugeyes

akabugeyes
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 17 July 2006 - 12:34 AM

How about trying FIND_IN_SET?

SELECT username, zip FROM usertable
WHERE FIND_IN_SET (zip,'$zips');

#4 Joe Haley

Joe Haley
  • Members
  • PipPipPip
  • Advanced Member
  • 103 posts
  • LocationCanada, eh?

Posted 17 July 2006 - 12:35 AM

http://ca3.php.net/implode

implode(); is your friend.
Give a man a fish; you have fed him for today.  Teach a man to fish; and you have fed him for a lifetime
Don't teach men to program. Teach them to fish.

Please, try the RTFM solution before asking for help:
http://php.net/manual/en/index.php

#5 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 17 July 2006 - 12:49 AM

I'm trying implode:


$zips = array('92120', '92127');
$zips = implode(',',$zips);
SELECT username, zip FROM usertable WHERE zip IN ($zips);

yields no result, yet just using WHERE zip IN ('92120', '92127') does. I have also implode it using '', ',' but still doesnt' work. FIND_IN_SET give the same result.

#6 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 17 July 2006 - 12:58 AM

Ah, here's the solution.
$zips = array(92102, 92127, 92103);
$quoted_zips= array();
foreach ($zips AS $v)
{
    $quoted_zips[]= "'{$v}'";
}
$zip_list= implode(',', $quoted_zips);

SELECT username, zip FROM usertable
WHERE zip IN ($zips)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users