joining two tables based on cookie data

Question

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?

Answer 1

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.

Answer 2

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