Search only where the user has set a favourites

[austinplatt]

Hi

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

SELECT id FROM content WHERE (name LIKE '%query%' OR actual_name LIKE '%query%') ORDER BY vote_rating DESC LIMIT 0, 9

but only on the users favourites.

Any ideas?

Many thanks!

[r937]

yes, all you need is a join...

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, 9

by the way, remove the id column from your favourites table --

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
);