[SOLVED] Subquery Query

[kingnutter]

I have been looking into table JOINs and it appears my query below may be best sorted with a query containing a subquery.

 

I am recalling and iterating through TEXT values into a form for the user to re-edit.

 

At present I get the error "mysql_fetch_row(): supplied argument is not a valid MySQL result resource in..."

 

With a bit of tinkering it has echoed just one of the moj_genre but without iterating through the whole set.

 

Can anybody help?

 

Here is the code:

 

$query="SELECT moj_genre FROM genres WHERE genre_id = (SELECT genre_id FROM genrelinkcd WHERE moj_id='$id')";

$result=mysql_query($query);
while ($row = mysql_fetch_row ($result))

{

echo "$row". ', ';

}

 

And this is an example of the tables.

 

Table: genres

 

genre_id      moj_genre

1                Rock

2                Pop

3                Jazz

4                Soul

 

 

Table: genrelinkcd

 

id      genre_id      moj_id

1          1                1

2          1                2

3          2                2         

4          3                3

 

 

 

[Fruct0se]

I havent tested but this should work or at least point you to what your trying to do:

 

$query="SELECT moj_genre, genre_id FROM genres, genrelinkcd WHERE genre.genre_id = genrelinkcd.moj_id AND genrelinkcd.moj_id='$id'";

$result=mysql_query($query);
while ($row = mysql_fetch_row ($result))
{
extract($row);
echo "moj_genre: $moj_genre, genre_id: $genre_id";
}

[kingnutter]

Great stuff. Thank you.