mysql query array check

Question

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

Possible duplicate of stackoverflow.com/questions/907806/… — Ry-
– Ry- ♦, Commented Jun 9, 2012 at 3:58
Wait, the array is in the table now? Could you clarify, please? — Ry-
– Ry- ♦, Commented Jun 9, 2012 at 4:00
Yeah apparently so. But don't do that. Give us more info on your table structure and we can help better. — Cameron Martin
– Cameron Martin, Commented Jun 9, 2012 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
– Juan Gonzales, Commented Jun 9, 2012 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
– Juan Gonzales, Commented Jun 9, 2012 at 4:06

Cameron Martin · Accepted Answer · 2012-06-09 04:21:07Z

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.

edited Jun 9, 2012 at 4:21

answered Jun 9, 2012 at 3:58

Cameron Martin

6,0102 gold badges45 silver badges55 bronze badges

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

3 Comments

Cameron Martin Over a year ago

Oh, I misinterpreted your question. Don't store a comma-delimited list of values in one mysql field.

Juan Gonzales Over a year ago

Actually, the problem is I need to find a value within the array I am searching for

Juan Gonzales Over a year ago

In your opinion, what is a better alternative of storage?

Ruslan Osipov · Accepted Answer · 2012-06-09 04:00:03Z

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

answered Jun 9, 2012 at 4:00

Ruslan Osipov

5,8734 gold badges31 silver badges44 bronze badges

1 Comment

Juan Gonzales Over a year ago

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.

Collectives™ on Stack Overflow

mysql query array check

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related