Help with COUNT on a group

I am displaying a list of movies into sections, divided by genre, and am trying to count and display watched/unwatched stats from each genre.

My tables look like this:


moviename varchar(255) DEFAULT NULL,
genreid INT NOT NULL,
movieregion char(3) DEFAULT NULL,
moviestatus varchar(10) DEFAULT NULL[/INDENT]

moviestatus is either set as WATCHED or UNWATCHED


genre_name varchar(255) DEFAULT NULL,
genre_abbreviation varchar(25) DEFAULT NULL[/INDENT]

I have this code

$result = mysqli_query($link, "SELECT moviename, genres.genre_abbreviation FROM MOVIES INNER JOIN genres ON");
if (!$result)
			$error = 'Error.';
			include 'error.php';
$platform_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']}'";

which displays using this code:

foreach ($groups as $genre_abbreviation => $movies) {
	echo '<div id="genre">';
	echo "$genre_abbreviation";
	echo '</div>';
foreach ($movies as $movie) {
	echo '<div id="movie">';
	echo "$movie[moviename]" . "<br/>\
	echo '</div>';
	echo '<br/>';

It works great and sorts all my movies into their respective genre, I just can’t work out how to count and display the stats… any help would be much appreciated, I’m stuck.

looks like this at the moment

HORROR I want the stats here: ‘watched: x, unwatched: x’




With a count you need to use a group by as well.

So, for example, you might do something like:

SELECT COUNT(moviestatus) FROM MOVIES WHERE moviestatus = 'WATCHED' GROUP BY genreid

That would give you the count for just those that are watched. You would then do another one for those that are true.

However, that adds two extra queries for every genre.

A better solution, since it looks like you already build an array of movies would be to just include the moviestatus in your results, then count it in PHP when you create your movies array.

Yesterday I introduced a dynamic method to display the last 25 most viewed pages.

In a nutshell:

  1. Created a new table in the database.
  2. Created a database SQL script to insert a record into the new table.
  3. Created SQL to extract the results of today table into an array.
  4. Displayed the results.

Step 1:

  `idx` int(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`,`idx`),
  KEY `idx` (`idx`)

Step 2:

//  Delete all records greater than 24 hours
//  Insert into 'table' record number of viewed page 
function _today($idx)
  // remove all records greater than 24 hours
 	$yesterday = date('Y-m-d H:i:s', time() - 24*60*60);
  $sql = "DELETE FROM `today` WHERE `date` &lt;= '2011-03-24 09:35:15'";
	$query = $this-&gt;db-&gt;query($query);	
  $sql = "INSERT INTO `today` (`idx`) VALUES ('" .$idx ."')";
	$query = $this-&gt;db-&gt;query($query);	

Step 3:

//  get last 25 most viewed pages 
function get_today($last=25)
	$query = "SELECT idx, COUNT(idx) AS total FROM today GROUP BY idx ORDER BY total DESC LIMIT 0," .$last; 
	$query = $this-&gt;db-&gt;query($query);	
	$i2 = 1;// iterator used for new line separator
	$result_today = array();
	foreach($query-&gt;result() as $xx):
		$idx = $xx-&gt;idx;
		$tot = $xx-&gt;total;

    // get joke-&gt;title
		$sql = "SELECT id,xrl,title FROM jokes WHERE id=$idx;";
		$url = $this-&gt;db-&gt;query($sql);
		$xrl 	 = $url-&gt;row()-&gt;xrl;
		$title = $url-&gt;row()-&gt;title . ' (' .$tot .')' ;
		$short = character_limiter($url-&gt;row()-&gt;title, 20);
		if ($i2++ % 5)// maybe add new line separator
			$result_today[] =	anchor($xrl, $short, " title='$title '");
			$result_today[] =	anchor($xrl, $short .br(2), " title='$title '");
	// die;
	return $result_today;

Step 4:

 echo '&lt;pre';
   print_r( get_today(25) );
 echo '&lt;/pre';

I’m not really sure how that applies to this topic…

>>> I am displaying a list of movies into sections, divided by genre,
>>> and am trying to count and display watched/unwatched stats from each genre.

The script I submitted totals the number of pages viewed.

Please delete the post if you think it is not applicable to this thread.


Sorry. I reread this thread and I realized that what you posted could be very helpful.

However, I think one of us are misunderstanding the question. I thought he had some kind of stat for a personal journal or something. I think you’re thinking of some kind of automatic “looked at” system.

Some clarification from the OP would be helpful. :slight_smile:

yes, it would

however, you would have no idea which count goes with which genre!!


Sorry guys, some clarification.

I have a simple website that has my own user.php page (displaying the list of movies I own, divided by genre), and I have a form that I use to add movies (one by one) to the list and can select whether or not I have seen them (watched) or not (unwatched).

Each movie can have either watched or unwatched status, so the website might eventually look like this

GENRE - 2/1 (Watched/Unwatched)


GENRE - 3/3 (Watched/Unwatched)


If I need to explain anything else do please let me know! I am very grateful for the help. I actually kind of fluked getting them to show up sorted by genre to be honest… :smiley:

Doh, you’re right. There should be a genre id or something SELECTed as well. =p

portem, I think you’re best bet, since you seem to be processing it a bit already would be to count them as you process it. So, wherever you create your movies, you could either create some parallel arrays or an object and keep track of each genres unwatched and watched counts.

Creating those counts from the database would require quite a few more queries, which would be a lot slower than the PHP method.

Ok let me take a stab at this, so am I correct in thinking I need to modify

$result = mysqli_query($link, "SELECT moviename, genres.genre_abbreviation FROM MOVIES INNER JOIN genres ON");

to also grab moviestatus

and then modify

$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']}'";

to include the moviestatus?

Yes, that would be correct. What I’d do is add two new values, watched and unwatched, then you’ll check the value from the row, then increment the value of watched or unwatched accordingly.

Ok thank you very much, I am trying to figure this out right now.

I will come back if I can’t and ask for examples or something. :smiley:

I just have ‘moviestatus’ in my movies table, and that will either be ‘watched’ or ‘unwatched’. need to do it that way.

out of my depth I guess. been hammering at it all night, I’m terrible :smiley:
I need to somehow figure how my main code actually works:

$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']}'";

since I admittedly just fluked it by reading examples etc. heh any tips or what I should search for exactly so that I can actually learn and understand how to get this to work.

I’m pretty sure I did the first part right

and that was change this by adding moviestatus

$result = mysqli_query($link, "SELECT moviename, [B]moviestatus[/B] genres.genre_abbreviation FROM MOVIES INNER JOIN genres ON");

but then I just have no idea how to include that information in my array code… been trying to nail it all night. hehe.

Sorry, let me rephrase.

I’d add two new values -to your array in PHP-. Your database is perfectly fine. =p

Hmmm, looking over your code, it looks like you don’t do much processing ahead of time (which is actually a good thing… works faster =p).

I think this should do you:

$genre_filter = array();
$watches = array();

while ($row = mysqli_fetch_assoc($result)) {

   $watches[$row['genre_abbreviation'])] = array('watched' => 0, 'unwatched' => 0);

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

So, basically, create a new array. I named it $watches.

Then, in your while, first check if you’re created the specific array in $watches (using the abbreviation you use for groups). If we haven’t, we’ll create two keys in there, watched and unwatched (the same values you use in the database).

Then all you have to do is increment whichever one watchstatus is set to.

Now, when you’re loop is finished, you’ll have that array which is filled with all your counts.

Then you can just output them like:

echo $watches[$genre_abbreviation]['watched'] . '/' . $watches[$genre_abbreviation]['unwatched'];

in your foreach.

that’s invalid as it stands – add a comma between moviestatus and genres.genre_abbreviation

and a tip: whenever you have more than one table in a query, you should qualify ~every~ column in the query with its table name (like you did for genres.genre_abbreviation)

Thanks r937, I’ve actually been wondering that - updated all my code to the right way. )

And thank you samanime, implementing that now.

Got an unexpected ), here:

$watches[$row['genre_abbreviation'][B])[/B]] = array('watched' => 0, 'unwatched' => 0);

Removed it, now just getting Undefined index on watched and unwatched, in my output. Trying to figure out why.

Can you paste the code you implemented in those two bits?

I got it working, woohoo. my watched/unwatched needed to be Watched/Unwatched. gonna change those items in the DB to just be W and U.

Cool, congrats.