How do I query mysql data with array
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 ....
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
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";
精彩评论