Help with COUNT on a group

portem · March 25, 2011, 1:38am

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:

MOVIES:

[INDENT]userid INT NOT NULL,
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

GENRES:

[INDENT]id INT NOT NULL,
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 movies.genreid=genres.id");
if (!$result)
		{
			$error = 'Error.';
			include 'error.php';
			exit();
		}
$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:


<?php
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’

movie
movie
movie

COMEDY

movie
movie
movie

samanime · March 25, 2011, 2:18am

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.

John_Betong · March 25, 2011, 2:58am

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

In a nutshell:

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

Step 1:


CREATE TABLE IF NOT EXISTS `today` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `idx` int(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`,`idx`),
  KEY `idx` (`idx`)
) ENGINE=MyISAM  DEFAULT CHARSET=ucs2 AUTO_INCREMENT=54 ;

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 '");
		}else{
			$result_today[] =	anchor($xrl, $short .br(2), " title='$title '");
		}
	endforeach;
	// die;
	return $result_today;
}

Step 4:



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

samanime · March 25, 2011, 3:21am

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

John_Betong · March 25, 2011, 3:56am

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

.

samanime · March 25, 2011, 4:03am

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.

r937 · March 25, 2011, 8:12am

samanime:

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.

yes, it would

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

portem · March 25, 2011, 1:40pm

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)

movie
movie
movie

GENRE - 3/3 (Watched/Unwatched)

movie
movie
movie

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…

samanime · March 25, 2011, 4:02pm

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.

portem · March 25, 2011, 7:20pm

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 movies.genreid=genres.id");

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?

samanime · March 25, 2011, 8:59pm

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.

portem · March 25, 2011, 9:32pm

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.

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

portem · March 26, 2011, 1:00am

out of my depth I guess. been hammering at it all night, I’m terrible
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 movies.genreid=genres.id");

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

samanime · March 26, 2011, 1:02am

Sorry, let me rephrase.

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

samanime · March 26, 2011, 1:12am

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)) {

if(!isset($watches[$row['genre_abbreviation']]))
   $watches[$row['genre_abbreviation'])] = array('watched' => 0, 'unwatched' => 0);
$watches[$row['genre_abbreviation']][$row['watchstatus']]++;

$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.

r937 · March 26, 2011, 1:12am

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)

portem · March 26, 2011, 2:04am

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.

samanime · March 26, 2011, 2:39am

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

portem · March 26, 2011, 2:44am

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.

samanime · March 26, 2011, 2:47am

Cool, congrats.