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
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 (%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.
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 = '%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.
