joining two tables based on cookie data

I am making a cookie based favorite system and need to join data from two tables based on the unique user id stored in the cookie so I can tell what items that use has marked as favorites.

I know I need to do a JOIN but have not used them much and dont really have my head around them yet.

Existing query that selects the items from the db:

$query = mysql_query("SELECT *, UNIX_TIMESTAMP(`date`) AS `date` FROM songs WHERE date >= DATE_SUB( NOW( ) , INTERVAL 2 WEEK ) ORDER BY date DESC");

My favorites table is setup as: ID FAVORITE USERID where ID is the primary key, FAVORITE is the song ID from table songs and USERID is a hash stored in a cookie.

I need to join in all the rows from the favorites table where the USERID field matches the cookie hash variable.

I also need to gather the total number of rows in favorites that match the song id so I can display a count of the number of people who set the item as favorite so I can display how many people like it. But maybe need to do that as a separate query?

From stackoverflow

• This should do it, I would imagine:

  $user_id = intval($_COOKIE['user_id']);
  $query = mysql_query(sprintf("
      SELECT *
      FROM songs s
         INNER JOIN favorites f
         ON f.favorite = s.id
      WHERE f.userid = %s
  ", $user_id));
  

  You should probably read up on the different types of joins.

  And then to get the total amount of rows returned, you can just call mysql_num_rows on the result:

  $favorite_song_count = mysql_num_rows($query);
  

  EDIT: To select all songs but note which are favorited, you would do this:

  $query = mysql_query(sprintf("
      SELECT s.*, f.id as favorite_id
      FROM songs s
         LEFT JOIN favorites f
         ON f.favorite = s.id AND f.userid = %s
  ", $user_id));
  

  By switching it from an INNER JOIN to a LEFT JOIN we are selecting all songs even if they don't have a corresponding record in the favorites table. Any songs that are favorites of the user_id provided will have a non-NULL value for favorite_id.

  ian : This works but only returns the songs that match as favorites. I need to return all the songs but be able to note which are favorites and not by that particular user.

  ian : LEFT JOIN still returns only 1 record... But I will play with it see if I can fix it.

  Paolo Bergantino : You're right, it wasn't the LEFT JOIN's fault but the where clause. It should be fixed now, I tested it.

  ian : Finally got it going! Thanks!

  ian : I got the query going with some alterations: $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)); however it will now basically create a query that contains all favorites... So if i favorite a song in two browsers it will display the song twice... any ideas?

  Paolo Bergantino : add a GROUP BY s.id at the end

• You can have logical (and, or, ...) operators in join conditions:

  select t1.*
  from t1
  join t2 on t1.id = t2.fid and t2.foo = 'blah'
  

  If you are also querying the total number of times each song has been "favorited" then you need a group by construct also, like this way:

  select *, count(f.id)
  from songs as s
  left join favorites as f on s.id = f.favorite and f.userid = <hash>
  group by s.id