Check if user has favorited an item

Question

I'm trying to build a system that allows users to add or remove items from their favorites. I started from this post, so I created a join table this way (I'm keeping the original OP's project for the example):

CREATE TABLE user_favorites (
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  PRIMARY KEY (user_id, movie_id),
  FOREIGN KEY (user_id) REFERENCES users (user_id),
  FOREIGN KEY (movie_id) REFERENCES movies (movie_id)
)

In my own project, I'm echoing several different "movies" on a page:

try{
    $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $query="SELECT * FROM movies";
    $res=$con->query($query);
    $movies=$res->fetchAll(PDO::FETCH_ASSOC);
    return $movies; 
}

catch(PDOException $e){
    echo 'Error : '.$e->getMessage();
}

I then format them to fit my needs and echo them on the page:

echo (formatMovies($movies));

(formatMovies is basically a foreach that loops through all the items and builds the necessary HTML.)

But here's the thing: I'd like to apply different styles to the movies, according to whether the user has favorited them or not.

My question is: what is the best way to check if the user favorited the movie, and when should I do it? Should I do it in the formatMovies loop, which would mean that I'd send an additional query for every item in $movies? It seems quite inefficient to me.

Thank you all!

Answer 1

You could do this in a single query, using a join...

$query = $con->prepare("
    SELECT m.*, f.movie_id IS NOT NULL AS has_favorited
    FROM movies AS m
    LEFT JOIN user_favorites AS f ON(f.user_id = :userId AND m.movie_id = f.movie_id)
");

$query->execute(array(':userId' => $userId));
$movies = $query->fetchAll(PDO::FETCH_ASSOC);

Then in your loop, just check if($movies['has_favorited']), then you'll know if it was favorited.