Need help with a count query

Databases
1

Hi guys, just need help with a query…

I have two tables

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

2

I assume there’s a 3rd table somewhere that links these two – could you share the structure of that? You’ll want to join the 3 tables together.

3

oh yes, duh, sorry. my bad.

3rd table is movies (movie_id, movie_name, genre_id, movie_region, release_date)

and my users table is users (id, username, email, password)

4 
SELECT
  COUNT(*)
FROM usermovies
INNER JOIN movies ON usermovies.movieid = movies.movie_id
WHERE
  usermovies.status = 'watched' 
  AND movies.genre_id = 3
5

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?

6

Join the genres table

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'
7

thank you! that works great and is exactly the query I need. and bonus: I think I understand how inner joins work now :stuck_out_tongue:

now I just have to figure out why my $genre_abbreviation variable is not working, that’s a question for the php forum I guess. :slight_smile: