0

This may be an easy one for some of you, but I am having trouble. I need to do a mysql query that will retrieve data if "$my_id" exists within the querying array. Something like this, or an idea to achieve the same results....

mysql_query("SELECT myArray FROM myTable WHERE my_id IN (myArray) LIMIT 1")

Any ideas?

EDIT:

The array in the table is deliminated by comma's

Juan Gonzales
  • 1,967
  • 2
  • 22
  • 36
  • 1
    Possible duplicate of http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause – Ry- Jun 09 '12 at 03:58
  • Wait, the array is in the table now? Could you clarify, please? – Ry- Jun 09 '12 at 04:00
  • Yeah apparently so. But don't do that. Give us more info on your table structure and we can help better. – Cameron Martin Jun 09 '12 at 04:01
  • This is not a duplicate, I have done my research already. The problem is that I need to find the my id within the value I am searching not an array already query'd – Juan Gonzales Jun 09 '12 at 04:02
  • @ddlshack I have a field in my table row that holds a group of id's that are set up like a deliminated array. I need to search that array, and find out if an id exists in it. If the id does exist, then I want to get that row. – Juan Gonzales Jun 09 '12 at 04:06

2 Answers2

1
mysql_query("SELECT myArray FROM myTable WHERE my_id IN ('".implode("', '", array_map('mysql_real_escape_string', $array))."') LIMIT 1");

Edit

I think I see what you're trying to do. You could use mysql LIKE for this, but its really inefficient (it has to do a full table scan). You shouldn't store lists of values in one field in mysql.

You want to create a one-to many relationship between entities. Because you havn't really given us much information, we'll call the entities stored in your table foos. So you have a table named foos that stores your foos. Each foo has a relationship with a number of bars, which are currently being stored in an 'array'. This is called a one-to-many relationship. These bars need to be in a separate table calls bars. You will store one bar per row, and each bar is associated with one foo, so you store foo's id along with each bar. If you wan to find which foos are associated with a certain bar, you just do a simple join.

If bars can be shared between many foos, this is called a many-to-many mapping. Then you need one table for foos, one table for bars, and a third table to map foos to bars. Then you can search which foos are associated with which bars, and which bars are associated with which foos.

Cameron Martin
  • 5,952
  • 2
  • 40
  • 53
0

If it's a one dimensional array - just implode it:

implode("','", $array)

IN takes comma separated values. http://www.w3schools.com/sql/sql_in.asp

Ruslan Osipov
  • 5,655
  • 4
  • 29
  • 44
  • You misinterpreted my question. I have a field in my table row that holds a group of id's that are set up like a deliminated array. I need to search that array, and find out if an id exists in it. If the id does exist, then I want to get that row. – Juan Gonzales Jun 09 '12 at 04:07