I'm trying to create a favorite system for my website. I have a user table, job table and a favorites table. When a user adds a job to their favorites, their userId and that jobsId is saved to the favorites table. When the user wants to see their favorites I've been trying to select all the rows in the favorites table that have the same userId as the current user. I then need to select the jobId's from these rows and select all the rows in the job table that have a matching jobId.
I've been trying variations of this query but haven't had any luck.
$sqlQuery = "SELECT * FROM job WHERE id = favorites.jobId AND :userId = favorites.userId"
You want all records from the jobs table whose IDs are in the user's favorites. This translates to:
select * from jobs where id in ( select jobid from favorites where userid = :userid );