Help with query

[Darkmatter5]

I have 2 tables

 

Fruits

idnamecolor

1applies1,2

2oranges3

 

Colors

idname

1red

2green

3orange

 

Now I have this query "SELECT *.fruit, colors.name AS color FROM fruits, colors".

 

Now how can I deal with the results in the color record of apples as apples can be red or green so I can have the array be something like.

 

idnamecolor

1applesred, green

2orangesorange

 

If this is more a PHP question I apologize!

[ram4nd]

You should start by makeing 2 right queryes yours seems a bit funny.

Get both data and then combine them.

I would make a function.

[corbin]

You need to normalize the tables to do this most efficiently.  (Look into normalized SQL schema.)

 

 

Anyway, I would do it something like this:

 

 

CREATE TABLE fruits (

    fruit_id INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,

    fruit_name varchar(32)

);

 

CREATE TABLE colors (

    color_id INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,

    color_name varchar(32)

);

 

CREATE TABLE fruit_colors (

    fruit_id INT NOT NULL,

    color_id INT NOT NULL

);

 

 

INSERT INTO fruits (name) VALUES ('Apples'), ('Oranges');

 

INSERT INTO colors (name) VALUES ('Red'), ('Green'), ('Orange');

 

INSERT INTO fruit_colors (color_id, fruit_id) VALUES (1, 1);

INSERT INTO fruit_colors (color_id, fruit_id) VALUES (2, 1);

INSERT INTO fruit_colors (color_id, fruit_id) VALUES (3, 2);

 

 

 

 

 

Then, to select all fruits with their colors:

 

 

 

SELECT f.fruit_id, f.name, c.name FROM fruits f INNER JOIN fruit_colors fc ON fc.fruit_id = f.fruit_id INNER JOIN colors c ON c.color_id = fc.color_id;

 

 

Yeah, a beast of a query, but it would keep you from having to pull all of the data into PHP and do more queries.  It would also help prevent repetitive data.  In this case, this could be seen by some as over normalizing though, since you will probably have a relatively small amount of rows, and colors wouldn't take much space.  (There are advantages to the middle table though.  Less repetition, thus smaller size, and easier/more uniform changes, for the most part.)

 

 

Some problems with the schema I did above:

 

-You would want indexes on some fields.

-You might not want to use a 32 bit integer if you plan on using fruits....  A short would probably be fine.  32k fruit (or 65k unsigned) is a lot of fruit.  (Even a small int might be ok with 255 numbers when unsigned, but you never know.)

[xtopolis]

I tried to solve this earlier using the exact same structures and display it CSV style(CONCAT_WS) like he asked in the OP.  I used similar, but different naming conventions however.

My "equivalent" query was:

SELECT f.fruit_id, f.name, CONCAT_WS(',',c.name) as colors FROM fruits f INNER JOIN Fruit_Colors fc ON fc.fruit_id = f.fruit_id INNER JOIN colors c ON c.color_id = fc.color_id
GROUP BY f.fruit_id

 

Which doesn't give me a csv list as expected... (for row 1)