genre (id, genre_name, genre_abbreviation) and usermovies (userid, movieid, status)
status can either be ‘unwatched’ or ‘watched’
I am trying to do the following:
$sql = mysqli_query($link, "SELECT COUNT(*) FROM usermovies WHERE status = 'watched'");
$row = mysqli_fetch_array($sql);
if (!$row)
{
$error = 'Error.';
include 'error.php';
exit();
}
$watched = $row['COUNT(*)'];
$sql = mysqli_query($link, "SELECT COUNT(*) FROM usermovies WHERE status = 'unwatched'");
$row = mysqli_fetch_array($sql);
if (!$row)
{
$error = 'Error.';
include 'error.php';
exit();
}
$unwatched = $row['COUNT(*)'];
so that I can use the $watched and $unwatched variables in my display script.
that works, but I need to add ‘WHERE genre.id = x’ for each of those queries so that my stats for each section are related to genre (my movies in the display page are sorted by genre) and I’m having trouble figuring out the syntax… any tips?
I’m sure I can also condense that code and use an array or something to get all the variables I need with one query? but I’m still very new to arrays
that works perfectly, but I just realized, I have to have it like this:
SELECT
COUNT(*)
FROM usermovies
INNER JOIN movies ON usermovies.movieid = movies.movie_id
WHERE
usermovies.status = 'watched'
AND [B]genre.genre_abbreviation = '$genre_abbreviation'[/B]
that doesn’t work though
because I am (trying to) displaying a list of users movies with an array, sorted by genre, like this:
Horror - watched: X, unwatched X
movie
movie
movie
Action - watched: x, unwatched X
and I only have the $platform_abbreviation variable set before this SELECT COUNT(*) query, how can I implement that in the query you gave me?
SELECT
COUNT(*)
FROM usermovies
INNER JOIN movies ON usermovies.movieid = movies.movie_id
INNER JOIN genre ON movies.genre_id = genre.id
WHERE
usermovies.status = 'watched'
AND genre.genre_abbreviation = 'horror'