join query returning odd results

Question

I use this query to display a list of songs and show what songs have been clicked as a favorite by a user.

$query = mysql_query(
  sprintf("
    SELECT 
      s.*,
      UNIX_TIMESTAMP(`date`) AS `date`,
      f.userid as favoritehash
    FROM
      songs s
    LEFT JOIN
      favorites f
    ON 
      f.favorite = s.id
      AND f.userid = %s",
  $userhash)
);

The songs table is setup as: id artist title duration etc. etc.

The favorites table is setup as: id favorite userid

The userid is a hashed value stored in a cookie to reference a unique user.

The query works fine but for some reason if I mark a song as a favorite in one browser. And then mark the same song as favorite in another browser to simulate multiple users the song will be displayed twice... once for each time it is marked favorite but the favorite indicator a <3 will still display correctly.

Any ideas?

Well got it to work via removign the sprintf() but curious to know why this is if anyone has any ideas.

Answer 1

I had a similar issue I think if you change the And F.userid = %s to Where f.userid = %s it should fix it?.

Answer 2

i think your ON-clause is wrong.

try this instead:

ON f.favorite = s.id
WHERE f.userid = %s

Answer 3

I believe that the previous suggestions would actually defeat the LEFT JOIN, causing it to act as an INNER JOIN; f.userid would sometimes be NULL, and NULL always compares as false.

I would start by looking at the contents of the favorites table. If I understand your schema, you might want to establish a unique key on favorites over favorite and userid to make sure that a given user can only favorite each song once. That is, you may be getting duplicate rows, and hence duplicate results.

Answer 4

Since you are using a 'left join' I am assuming that you want a list of all the songs and that you want the users favorites to be easily distinguishable. i.e. something like this:

song1 details | date | null
song2 details | date | userhash  (favorite)
song3 details | date | null

Try the following:

SELECT s.*, UNIX_TIMESTAMP(`date`) AS `date`, f.userid as favoritehash
FROM
  songs s
LEFT JOIN
  (SELECT userid, favorite FROM favorites WHERE userid = %s) f
ON 
  f.favorite = s.id

Answer 5

You are using sprintf and %s (string), but you are not enclosing the resulting string value in quotes. If the user ID is a string, then you need to enclose it in quotes, otherwise use %d instead of %s. Since it works fine when you remove sprintf, that would seem to be the problem.