Missing something? [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I have three tables

product_color
===
color_id (primary)
color_initial
red
green
blue

school_art
===
id(primary)
art_loc
series_code

set_color
===
setcolors_id
school_art_id
color_id 

What I would like to do is:

Get the red, green and blue from p开发者_运维百科roduct_color table where the product_color.color_id = set_color.color_id AND WHERE the set_color.school_art_id = school_art.id

This is what I have, but being that it is not working, clearly I am missing something. Any help would SOOO gratefully appreciated.

$colors =
"SELECT * FROM
    product_color
JOIN
    set_colors
ON
    product_color.color_id = set_colors.color_id
WHERE
    set_colors.school_art_id = '{$school_art_id}'";

$colorresult = mysql_query($colors) or die(mysql_error());
$returncolors = mysql_fetch_array($colorresult);

---

I'm assuming that since you say school_art.id you also want to pull in the school_art table. For that, you have to JOIN all three tables:

SELECT * FROM product_color
         JOIN set_color ON(product_color.color_id = set_color.color_id)
         JOIN school_art ON(school_art.id = set_color.school_art_id);

---

$q = "SELECT * FROM product_color, set_color, school_art
WHERE product_color.color_id = set_color.color_id 
AND school_art.id = set_color.school_art_id
AND set_colors.school_art_id = $school_art_id";

This should work for the query.

Also you might want to use mysql_fetch_array() in a loop.

while ($row = mysql_fetch_array($colorresult)) {
    allColors[] = $row;
}

Now each element of allColors array will contain one result row so you can do whatever you want with it.