Problem with JOIN statement

demosthenes705

I have this join statement

$result = mysql_query("
	SELECT 
	  a.name AS artist_name
	, b.name AS album_name
	, s.name as song_name
	, s.play_count as play_count
	FROM `".$database_table_prefix."song` s
	JOIN `".$database_table_prefix."artist` a
	ON s.artist = a.id
	JOIN `".$database_table_prefix."album` WHERE `album` = ".$album." b
	ON s.album = b.id
	ORDER BY play_count DESC
	LIMIT 500
") or die(mysql_error());

and when i run it i get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'About Jane b ON s.album = b.id ORDER BY play_count DESC LIMIT 500' at line 9

basically i want to join the album table where album is equil to the $album;

here is a url with all the source code if needed.
http://nedren.com/chris/itunes/songs.inc.phps

bradgrafelman

Looking at this right here:

WHERE `album` = ".$album." b

I see two things wrong:

You're specifying a string, so you need to surround the string with quotes in the query.
What is the b doing?

demosthenes705

I am not sure. I believe that it joins albums with song but like i said, i didn't create this script and if you want to see the entire script you can go http://nedren.com/chris/itunes/songs.inc.phps

bradgrafelman

Well, add the quotes as I said and then try it. I'm not an expert on joins, however, so I don't know how to fix that syntax.

dougal85

To fix these problems i always put the query into a string first, then echo it out so you can EASILY see what is being queried. I'd recommend doing that, and show us.

I'm assuing mySQL ? 4.x

From looking at it quickly there seems to be something wrong with your JOIN syntax.

$result = mysql_query("
SELECT
a.name AS artist_name
, b.name AS album_name
, s.name as song_name
, s.play_count as play_count
FROM ".$database_table_prefix."song s
JOIN ".$database_table_prefix."artist a
ON s.artist = a.id
JOIN `".$database_table_prefix. " b ON s.album = b." . "album
ORDER BY play_count DESC
LIMIT 500
")

I think that might be closer to what you want, but do what i said, echo out the query and i'll be able to help more, i'm having a headblock trying to read what it is at the mo 😛