Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
1  
Possible duplicate of stackoverflow.com/questions/907806/… – minitech♦ Jun 9 '12 at 3:58
Wait, the array is in the table now? Could you clarify, please? – minitech♦ Jun 9 '12 at 4:00
Yeah apparently so. But don't do that. Give us more info on your table structure and we can help better. – ddlshack Jun 9 '12 at 4: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 9 '12 at 4: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 9 '12 at 4:06
show 1 more comment

2 Answers

up vote 1 down vote accepted 
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.

share|improve this answer
Oh, I misinterpreted your question. Don't store a comma-delimited list of values in one mysql field. – ddlshack Jun 9 '12 at 4:00
Actually, the problem is I need to find a value within the array I am searching for – Juan Gonzales Jun 9 '12 at 4:01
What? morechars – ddlshack Jun 9 '12 at 4:02
In your opinion, what is a better alternative of storage? – Juan Gonzales Jun 9 '12 at 4:09
I've updated my answer with a rambling explanation. – ddlshack Jun 9 '12 at 4:21
up vote 0 down vote

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

share|improve this answer
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 9 '12 at 4:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.