I have two tables - one has a list of users' assigned colors and another has a list of shapes associated with each color. When a user logs in, I want to get an array of their colors and then generate an array of their shapes based on their colors. So I would be doing a PHP/MySQL search using an array as WHERE criteria and generating an array from the results.

+------|-------+
| user | color |
+------|-------+
|  11  |  67   |
|  11  |  72   |
|  12  |  89   |
|  13  |  89   |
+------|-------+

+-------|-------+
| color | shape |
+-------|-------+
|  67   |  34   |
|  67   |  35   |
|  67   |  49   |
|  72   |  56   |
|  72   |  57   |
|  89   |  39   |
+-------|-------+

2/1/2010 3:43:43 PM

I would use a left join of some sort to bring the tables together.

this is probably way off base but i think it would look something like this

SELECT user
,(SELECT color FROM  colortable	LEFT JOIN color.usertable ON color.shapetable = colorID
								WHERE  user = color ') AS color_array
,(SELECT shape FROM colortable	LEFT JOIN color.usertable ON color.shapetable = colorID
								WHERE  user = color ') AS array_shapes
			
FROM usertable ORDER BY color ASC

2/1/2010 3:56:02 PM

you can do this with just mysql.

select color.user, color.color, shape.shape FROM color, shape WHERE color.user = 11 AND shape.color = color.color

something like that, I just assumed your table names with color (for user/color) and shape for (color/shape)

^that works too

[Edited on February 1, 2010 at 3:57 PM. Reason : .]

2/1/2010 3:56:28 PM

Oh, sorry for creating a new thread. I had forgotten about the other one. Thanks for the help!

2/1/2010 7:12:16 PM