Results 1 to 2 of 2
Jul 27, 2007, 13:19 #1
- Join Date
- May 2005
- 0 Post(s)
- 0 Thread(s)
Search only where the user has set a favourites
I was just wondering if it was possible to perform a query of the following nature. I have limited MySQL knowledge but can do the basic functions. This is really the only time, I have need something more advanced and would be grateful for any guidance on the matter.
Basically I have two tables, one called 'content', this has details of each item of content. The columns are
id, views, vote_rating, category, name, actual_name, date_added
The other table is called 'favourites'. This has the following rows:
id, cid, uid
Where cid is the id of the row in the content table, and uid is the id of the user who has it set as a favourite.
I am trying to do a search, on the users favourites. So I know the uid. Basically, I am trying to do the following query
but only on the users favourites.
Jul 27, 2007, 14:29 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 49 Post(s)
- 1 Thread(s)
yes, all you need is a join...Code:
SELECT content.id , content.views , content.vote_rating , content.category , content.name , content.actual_name , content.date_added FROM favourites INNER JOIN content ON content.id = favourites.cid AND ( content.name LIKE '%query%' OR content.actual_name LIKE '%query%' ) WHERE favourites.uid = 937 ORDER BY content.vote_rating DESC LIMIT 0, 9Code:
CREATE TABLE favourites ( uid INTEGER NOT NULL , cid INTEGER NOT NULL , PRIMARY KEY ( uid , cid ) , FOREIGN KEY ( uid ) REFERENCES users ( id ) ON DELETE CASCADE , FOREIGN KEY ( cid ) REFERENCES content ( id ) ON DELETE CASCADE );