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?

-------------Problems Reply------------

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.

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

Category:php Views:1 Time:2009-07-14
Tags: php mysql

Related post

  • MYSQL join tables based on column data and table name 2011-11-17

    I'm wondering if this its even posible. I want to join 2 tables based on the data of table 1. Example table 1 has column food with its data beeing "hotdog". And I have a table called hotdog. IS it possible to do a JOIN like. SELECT * FROM table1 t jo

  • Joining two tables based off of parsed column content 2011-07-12

    I am trying to join two tables based off of two columns that have been combined (often imperfectly) into another tables column. I am trying to join the tables so the correct records are associated with each other, so I can compare the FDebit and the

  • Creating table based on the data inputs php 2011-09-09

    I have this several lines of code. It's function is that it will create a table based on the date that is inputted. Example if the date today is Monday, then it will result into 5 columns (mon, tue, wed, thu, fri) or if today is Tuesday, it will resu

  • Update pivot table based on a date 2014-12-28

    I want to update a pivot table based on a date in another cell. Each time it updates the table, i want it to only use the new dates that are in. the following code i have puts the dates in, but just keeps adding them to the old dates. i want to desel

  • How can I check for average concurrent events in a SQL table based on the date, time and duration of the events? 2009-04-17

    I have a set of call detail records, and from those records, I'm supposed to determine the average concurrent active calls per system, per hour (at a precision of one minute). If I query 7pm to 8pm, I should see the average concurrent calls for the h

  • Can I JOIN a table based on an SQL if-statement? 2009-09-14

    I have three tables being used for this problem: songs, blacklist, and whitelist. The songs table has a column named "accessType" which stores one of these four values: public, private, blacklist, whitelist. I'm trying to fetch a list of all the song

  • Join two tables, then Order By date, BUT combining both tables 2011-03-12

    Alright, I'm trying to figure out why I can't understand how to do this well... I have two tables: invoices: id userID amount date payments: id userID amount date So, the goal here is to join both tables, where the userID matches whatever I want it t

  • PHP join three tables when if the data is not in one of them 2011-09-22

    I am working on joining three tables together, but in one of the tables no information will be in there, unless the user opens the email we sent them I am currently using this sql but it seems not to work correctly SELECT email.senton, customer.*, co

  • Join tables based on lagged date 2013-05-20

    I need to perform a LEFT JOIN to bring in a variable from table B to A by joining on the date with the new variable in Table B having a lagged date of the date in Table A: Table A: Date Var1 1/3/07 1 Table B: Date Var2 1/2/07 2 1/3/07 3 JOINED TABLE:

  • SQL Server get next previous rows from a table based on a date 2009-11-20

    I have the following SQL Server query and I am trying to get the next and previous row from a given Id. All works great unless there are two/more dates that are the same, then my logic breaks down. If you set @currentId = 4 then I get back id 7 (it s

  • SQL Query to Join Two Tables Based Off Closest Timestamp 2010-11-01

    I have two tables in SQL and I need to be able to do a join based off of the timestamp in table B that is earlier than or equal to the timestamp in table A. So, here is some fake data for two tables and the desired output: Closed Cases (Table A) | id

  • SQL update and join three tables based on rows in one table and not another 2012-03-09

    I have a bit of a complicated sql query I need to do, and I'm a bit stuck. I'm using SQLite if that changes anything. I have the following table structure: Table G --------- G_id (primary key) | Other cols ... ==================================== 21

  • Auto-Populate a field in one table based on the data imputed in two fields of another table 2013-12-21

    I am attempting to create a database that tracks homesites that we are building, and homeowners that purchase the homesites. Table one contains homebuyer information. Table two contains the Lot and address information of the homes that are for sale.

  • Join two tables based on nearby timestamps 2015-03-03

    Table1 (1422 rows) sn1 | dateee | shift | linee ---------+---------------------+-------+------- 8419404 | 2015-02-27 09:45:50 | D | 2 8419383 | 2015-02-27 09:46:10 | D | 2 8419410 | 2015-02-27 09:46:40 | D | 2 8419385 | 2015-02-27 09:50:40 | D | 2 84

  • Inner Joining two tables based on all "Key/Value" Pairs matching exactly 2010-03-02

    Lets say I have two tables - Person and Clothes and both of these tables have associated Key/Value tables which store attributes about a Person and an item of Clothing. A joined version of Person to Attributes might look like: PersonID | AttributeKey

  • Reload table based on POST data 2010-11-29

    I am trying to create a table that would change its content based on user input. The table displays some data from a database; it starts the selection starting with a given calendar date that defaults to current date. I took a look at jQuery - Reload

  • MySQL join two tables based on a time field returning the largest previous time 2011-06-03

    I have two tables like so: EventTable (EID, Name, EventTime) VideoTable (VID, StartTime, Video, DurationSecs) The EventTable contains events that occur at a particular time. The VideoTable contains a list of video files and their starting times. What

  • Joining excel records based on column data 2011-10-19

    I have 3 excel spreadsheets with the same columns and (supposedly) the same data. I need to line up all 3 documents and look for inconsistencies within the data. The data is server information at a data center and I would like the server location(row

  • Deleting rows from id table based off of data from three corresponding tables 2012-03-13

    I have a table 'node' wich has a column 'nid'. This id corresponds to the column 'entity_id' in three other tables. The three tables we'll call 'pop', 'city', 'state'. The 'city' and 'state' tables have city_value and state_value columns respectively

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.073 (s). 11 q(s)