My head hurts - display array data code

hi guys, I have a script that I have put together with the help of this amazing forum, and it works great.

currently it displays a list of movies, sectioned by genre, like so:

genre
movie
movie
movie
genre
movie
movie
movie

but in my database, each movie has a status, watched or unwatched, and I am trying to display those stats next to each genre, like this:

genre watched: x, unwatched x
movie
movie
movie

genre watched: x, unwatched x
movie
movie
movie

I have it working, but unfortunately after hours of trying I only got it to display one genre’s stats for every genre. I was wondering if anyone could give me some tips on this code so that I can get it to display each genres watched/unwatched stats properly.

here is my code


<?php
$result = mysqli_query($link, "SELECT movies.movie_name, genre.genre_abbreviation FROM users INNER JOIN usermovies ON usermovies.userid = users.id INNER JOIN movies ON movies.movie_id = usermovies.movieid INNER JOIN genre ON genre.id = movies.genre_id WHERE users.username = '$username' ORDER BY genre.genre_abbreviation, movies.movie_name");

$groups = array();
while ($row = mysqli_fetch_assoc($result)) {
$groups[ $row['genre_abbreviation'] ][] = $row;
$genre_abbreviation = $row['genre_abbreviation'];
}

$sql = mysqli_query($link, "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 = '$genre_abbreviation'");
$row = mysqli_fetch_array($sql);
$watched = $row['COUNT(*)'];

$sql = mysqli_query($link, "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 = 'unwatched' AND genre.genre_abbreviation = '$genre_abbreviation'");
$row = mysqli_fetch_array($sql);
$unwatched = $row['COUNT(*)'];

foreach ($groups as $genre_abbreviation => $movies) {
	echo '<div id="genre">';
	echo "$genre_abbreviation";
	echo '</div>';
	echo '<div id ="status">';
	echo '<img src="./watched.gif">' . "$watched" . '<img src="./unwatched.gif">' . "$unwatched" . "<br/>\
";
	echo '</div>';
foreach ($movies as $movie) {
	echo '<div id="movie">';
	echo "$movie[movie_name]" . "<br/>\
";
	echo '</div>';
	}
	echo '<br/>';
}
?>

if I’ve left out any important info please let me know

Is there a reason you have a watched and unwatched field? I would make a single enum field with Y,N as the options.
then you could do

Select count(movieWatched) as watched, genre
FROM Movies, Genre
...
Where watched = 'Y';
...

sorry should have added, I plan on adding more status fields than just watched/unwatched

such as owned, notowned, etc etc

could I still use the same method you described?

also this is the DB design I am using

movies (movie_id, movie_name, genre_id, movie_region, release_date)
usermovies (userid, movieid, status)
genre (id, genre_name, genre_abbreviation)
users (id, username, email, password)

‘watched’ and ‘unwatched’ are stored in status of usermovies

There are better ways of doing what your trying to achieve but the short of it is that genre_abbreviation needs to be an array of all genres, not a single genre. As a single genre it will only return a single row.


$result = mysqli_query($link, "SELECT movies.movie_name, genre.genre_abbreviation FROM users INNER JOIN usermovies ON usermovies.userid = users.id INNER JOIN movies ON movies.movie_id = usermovies.movieid INNER JOIN genre ON genre.id = movies.genre_id WHERE users.username = '$username' ORDER BY genre.genre_abbreviation, movies.movie_name");
$groups = array();

/*------------------ Modified ------------------ */

/*
* Genres to filter on
*/
$genre_filter = array();
while ($row = mysqli_fetch_assoc($result)) {
	$groups[ $row['genre_abbreviation'] ][] = $row;
	
	/*
	* Add row to generes filter if not already present - quote each one
	*/
	if(!in_array("'{$row['genre_abbreviation']}'",$genre_filter)) {
		$genre_filter[] = "'{$row['genre_abbreviation']}'";
	}
}



$sql = mysqli_query($link,sprintf(
	"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 IN (&#37;s)"
	,implode(',',$genre_filter)
));

$row = mysqli_fetch_array($sql);
$watched = $row['COUNT(*)'];


$sql = mysqli_query($link, sprintf(
	"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 = 'unwatched' AND genre.genre_abbreviation IN (%s)"
	,implode(',',$genre_filter)
));

$row = mysqli_fetch_array($sql);
$unwatched = $row['COUNT(*)'];

/*------------ End Modify ----------------- */

foreach ($groups as $genre_abbreviation => $movies) {
    echo '<div id="genre">';
    echo "$genre_abbreviation";
    echo '</div>';
    echo '<div id ="status">';
    echo '<img src="./watched.gif">' . "$watched" . '<img src="./unwatched.gif">' . "$unwatched" . "<br/>\
";
    echo '</div>';
	foreach ($movies as $movie) {
    	echo '<div id="movie">';
   		echo "$movie[movie_name]" . "<br/>\
";
    	echo '</div>';
    }
    echo '<br/>';
}

However, back to what I was saying you can achieve this using a single query to get all genres and the count calculations. Than you could eliminate the second and third query.

As an aside, I’d recommend moving to a Bitwise Operator for your watched/unwatched field. Essentially, you assign a value to each action (watched, owned, etc). It’s a slightly more complex way to do it, and it will take a bit of doing on your end right now, but it means that you’ll be able to add fields at any time to your action possibilities with minimal modification to your code.

Well an enum field is similar to a select drop down, so whatever you list are the valid results. So you could have Watched enum(‘y’,‘n’); Owned enum(‘y’,‘n’) etc. I would recommend you making a new field for each new status you want

Off Topic:

I started this post just before dinner and didn’t come back till 7:30 am PST

I updated to this code, and I’m not getting any errors but my $watched and $unwatched variables are showing as zero now. while before they were watched: 2 and unwatched: 1. hmm.

thanks I am reading about this now. I’m admittedly starting to get in over my head a little, I’ve had trouble ‘getting’ array stuff in general… really need to find some documentation that suits my brain when it comes to it.

this is the code I currently have:


<?php
$result = mysqli_query($link, "SELECT movies.movie_name, genre.genre_abbreviation FROM users INNER JOIN usermovies ON usermovies.userid = users.id INNER JOIN movies ON movies.movie_id = usermovies.movieid INNER JOIN genre ON genre.id = movies.genre_id WHERE users.username = '$username' ORDER BY genre.genre_abbreviation, movies.movie_name");

$genre_filter = array();
while ($row = mysqli_fetch_assoc($result)) {
$groups[ $row['genre_abbreviation'] ][] = $row;
    if(!in_array("'{$row['genre_abbreviation']}'",$genre_filter)) {
        $genre_filter[] = "'{$row['genre_abbreviation']}'";
    }
}

$sql = mysqli_query($link,sprintf(
    "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 IN (%s)"
    ,implode(',',$genre_filter)
));
$row = mysqli_fetch_array($sql);
$watched = $row['COUNT(*)'];

$sql = mysqli_query($link,sprintf(
    "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 = 'unwatched' AND genre.genre_abbreviation IN (%s)"
    ,implode(',',$genre_filter)
));
$row = mysqli_fetch_array($sql);
$unwatched = $row['COUNT(*)'];

foreach ($groups as $genre_abbreviation => $movies) {
	echo '<div id="genre">';
	echo "$genre_abbreviation";
	echo '</div>';
	echo '<div id ="status">';
	echo '<img src="./watched.gif">' . "$watched" . '<img src="./unwatched.gif">' . "$unwatched" . "<br/>\
";
	echo '</div>';
foreach ($movies as $movie) {
	echo '<div id="movie">';
	echo "$movie[movie_name]" . "<br/>\
";
	echo '</div>';
	}
	echo '<br/>';
}
?>

problem solved. was a slight error in one of my queries. :slight_smile:

however the script is still returning the stats for one genre for every genre and I’m not sure why

You will need to add a group by by clause to each of those count queries to group by genre_abbreviation. You will than need to loop through the result of each and map the corresponding data to the first result set for each genre.

By the way it doesn’t make much sense to have two columns for what is really a boolean value. A movie can either be watched or it can’t be watched so a tinyint and a single watched column would suffice. When watched = 1 the movie has been watched and when watched = 0 the movie has not be watched. No need to have two separate columns.

This code has not been tested at all. So I;m not certain how close I am but this is likely the way I would handle it unless you want to resort to subqueries which would probably be less efficient in this case.


<?php
class DAOMovie {
	
	/*
	* Run query and collect result set as array
	*/
	private function _query($strSQL) {
	
		// run query
		$objResult = mysqli_query($strSQL);
		
		// collect result data
		$arrReturn = array();
		while($arrRow = mysqli_fetch_assoc($objResult)) {
			$arrReturn[] = $arrRow;
		}
		
		return $arrReturn;
		
	}
	
	/*
	* Escape string
	*/
	private function _escape($mixValue) {
		//return mysql_real_escape_string($mixValue);
		return $mixValue;
	}
	
	/*
	* Fetch all users movies.
	*/
	public function fetchUsersMovies($strUsername,$boolMixin=false) {
	
		$strSQL = sprintf(
			"SELECT 
			     m.movie_name
			     ,g.genre_abbreviation 
			  FROM 
			     users u
			 INNER 
			  JOIN 
			     usermovies um
			    ON 
			     um.userid = u.id 
			 INNER 
			  JOIN 
			     movies m
			    ON 
			     m.movie_id = u.movieid 
			 INNER 
			  JOIN 
			     genre g
			    ON 
			     g.id = m.genre_id 
			 WHERE 
			     u.username = '&#37;s' 
			 ORDER 
			    BY 
			     g.genre_abbreviation
			    ,m.movie_name"		    
			,$this->_escape(strUserName)
		);
		
		// get all users movies
		$arrMovies = $this->_query($strSQL);
		
		// mixin users movie genre data
		if($boolMixin === true) {
			$arrMixin = $this->fetchUsersMovieGenres(strUsername);
		}
		
		$arrReturn = array();
		
		foreach($arrMovies as $arrMovie) {
		
			// mixin genre specific data
			if($boolMixin === true) {
				$arrReturn[$arrMovie['genre_abbreviation']]['watched'] = isset($arrMixin[$arrMovie['genre_abbreviation']])?$arrMixin[$arrMovie['genre_abbreviation']]['watched_movies']:0;
				$arrReturn[$arrMovie['genre_abbreviation']]['unwatched'] = isset($arrMixin[$arrMovie['genre_abbreviation']])?$arrMixin[$arrMovie['genre_abbreviation']]['unwatched_movies']:0;
			}
			
			// add movie to genre
			$arrReturn[$arrMove['genre_abbreviation']]['movies'][] = $arrMovie;
		}
		
		return $arrReturn;
	
	}
	
	/*
	* Fetch genres of movies for user with
	* watch and unwatched calculation per genre.
	*/
	public function fetchUsersMovieGenres($strUsername) {
	
		$strSQL = sprintf(	
			"SELECT 
			      g.genre_abbreviation
			      ,SUM(IF(um.status = 'watched',1,0)) watched_movies
			      ,SUM(IF(um.status = 'unwatched',1,0)) unwatched_movies
			   FROM 
			      users u
			  INNER
			   JOIN
			      usermovies um
			     ON
			      um.userid = u.id 
			  INNER 
			   JOIN 
			      movies m
			     ON 
			      um.movieid = m.movie_id 
			  INNER 
			   JOIN 
			      genre g
			     ON 
			      m.genre_id = g.id 
			  WHERE 
			      u.username = '%s'
			  GROUP
			     BY
			      g.genre_abbreviation"	      
			,$this->_escape(strUserName)
		);
		
		$arrRows = $this->_query($strSQL);
		$arrReturn = array();
		
		foreach($arrRows as $arrRow) {
			$arrReturn[$arrRow['genre_abbreviation']] = $arrRow;
		}
		
		return $arrReturn;
	
	}

}

$objDAOMovie = new DAOMovie();

// fetch movies w/ genres and mixin watched and unwatched data for each genre
$groups = $objDAOMovie->fetchUsersMovies($username,true);

foreach ($groups as $genre_abbreviation => $genre) {
    echo '<div id="genre">';
    echo "$genre_abbreviation";
    echo '</div>';
    echo '<div id ="status">';
    echo '<img src="./watched.gif">' . $genre['watched'] . '<img src="./unwatched.gif">' . $genre['unwatched'] . "<br/>\
";
    echo '</div>';
	foreach ($genre['movies'] as $movie) {
    	echo '<div id="movie">';
   	 	echo "$movie[movie_name]" . "<br/>\
";
    	echo '</div>';
    }
    echo '<br/>';
}
?>

What I did was factored out the responsibility of getting the movie data from that of the genre data for a particular user. Than I added the ability to mixin the result set of fetching movies with that of fetching the unwatched data.