How do I query mysql data with array

Question

I have 2 tables colorcode & users

colorcode

ID colorid  colorname
------------------------
1  1        yellow
2  2        black
3  3        red
4  4        white

users

ID userid  colorid
------------------------
1  1        1,2
2  2        3,4
3  3        1,3,4
4  4        1

How do I retrieve & query individual colorid

$aa = $db->query("SELECT * FROM colorcode"); 
$colors = array();
   while ($colordata = mysql_fetch_assoc($aa)) {
   $colors[] = $colordata["colorid"];
}

Let's say I want query which users have yellow color & what it's the statement should I use for users

SELECT .. FROM users
WHERE colorid ....

You should normalize your users table. Create a new relationship table called user_colors, because the way you have it, it's not even in the 1NF. — Shef
– Shef, Commented Sep 26, 2011 at 19:55
You should probably change your structure. Storing comma separated values causes far more headaches than it solves. @Shef Thanks to A Level computing, I know know what 1NF is. Hooray! — Bojangles
– Bojangles, Commented Sep 26, 2011 at 19:55
possible duplicate stackoverflow.com/questions/7448245/mysql-select-list-in-array — Einacio
– Einacio, Commented Sep 26, 2011 at 19:56
By the way, Using a column named "ID" is not necessary for the tables if "userid" and "colorid" can be used as primary keys for unique identifiers. — Chris C
– Chris C, Commented Sep 26, 2011 at 20:00

Marc B · Accepted Answer · 2011-09-26 19:56:54Z

1

It's a bad design... since you're trying to access the individual color_ids in the user table, but have stored them as a comma-separated list, you canot have the database do a normal join for you - you've killed off the main point of using a relational database by making it impossible to for the database to do the relating for you.

However, since you're on mysql, you're in luck - mysql has a function for cases like this:

SELECT users.ID, userid, GROUP_CONCAT(colorcode.colorname)
FROM users
LEFT JOIN colorcode ON FIND_IN_SET(colorcode.ID, users.colorid)
GROUP BY users.id

answered Sep 26, 2011 at 19:56

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Comments

Naftali · Accepted Answer · 2011-09-26 19:55:16Z

0

SELECT * FROM users
WHERE colorid LIKE "%1%"

But what I would really do is make a link table from users to colors:

usersToColors:

ID userid  colorid
------------------------
1  1        1
2  1        2
3  2        3
4  2        4
...

Then you could do:

SELECT * FROM users u, usersToColors utc
WHERE u.userid = utc.userid 
      AND utc.colorid = 1;

Or even:

SELECT * FROM users u, usersToColors utc, colors c
WHERE u.userid = utc.userid 
      AND utc.colorid = c.colorid
      AND c.colorname = "yellow";

answered Sep 26, 2011 at 19:55

Naftali

147k41 gold badges247 silver badges304 bronze badges

Collectives™ on Stack Overflow

How do I query mysql data with array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related