inner join query question

[shaunthomson]

Hi

I've done inner joins before, and I figured this one would be doing the same thing as the others, but it isn't working, and I was wondering if someone could see the error?

I have 3 tables - a DJs table, a favs table, and a members table (favs table stores members favorite djs).

The favs table contains 3 columns - fav_id, member_id and the dj_id.

The DJs table contains 2 - dj_id, and name.

I'm trying to retrieve the name of each DJ that the member likes.

$sql  = "SELECT DJs.name
	FROM favs
	INNER JOIN DJs
	ON favs.dj_id = DJs.dj_id
	WHERE favs.member_id = '{$my_id}'	
	ORDER BY DJs.name";

Should that not return a result set if $my_id appears in favs, and there are matches between favs.dj_id and DJs.dj_id?

Thanks for taking a look.

[Old Pedant]

Looks right to me. So maybe the problem is in your PHP code?

Start by debugging:

$sql = "SELECT DJs.name FROM favs INNER JOIN DJs 
           ON favs.dj_id = DJs.dj_id WHERE favs.member_id = '{$my_id}'
           ORDER BY DJs.name";

echo "<hr/>DEBUG SQL: " . $sql . "<hr/>";

...

Look at the debug: Does it have the right member_id value?

Oh, and if member_id is a NUMERIC field, then kill the apostrophes around $my_id.

[shaunthomson]

Wanna know what I did? I forgot to encase the query in mysql_query() !!! I've done that a few times now, and have thoroughly learned my lesson!!!