根据cookie数据连接两个表
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.
我正在制作一个基于cookie的收藏系统,需要根据存储在cookie中的唯一用户ID连接来自两个表的数据,这样我就可以知道哪些项目被标记为收藏夹。
I know I need to do a JOIN but have not used them much and dont really have my head around them yet.
我知道我需要做一个JOIN但是没有太多使用它们并且还没有真正了解它们。
Existing query that selects the items from the db:
从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.
我的收藏夹表设置为:ID FAVORITE USERID,其中ID是主键,FAVORITE是表歌曲中的歌曲ID,USERID是存储在cookie中的哈希。
I need to join in all the rows from the favorites table where the USERID field matches the cookie hash variable.
我需要加入来自favorites表的所有行,其中USERID字段与cookie哈希变量匹配。
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?
我还需要收集与歌曲ID匹配的收藏夹中的总行数,这样我就可以显示将项目设置为收藏的人数,这样我就可以显示有多少人喜欢它。但也许需要将其作为单独的查询?
2 个解决方案
#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:
然后要获得返回的总行数,您只需在结果上调用mysql_num_rows:
$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.
通过将它从INNER JOIN切换到LEFT JOIN,即使它们在收藏夹表中没有相应的记录,我们也会选择所有歌曲。任何提供的user_id收藏的歌曲都会为favorite_id设置非NULL值。