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']}'";
}
}
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’
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:
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` <= '2011-03-24 09:35:15'";
$query = $this->db->query($query);
$sql = "INSERT INTO `today` (`idx`) VALUES ('" .$idx ."')";
$query = $this->db->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->db->query($query);
$i2 = 1;// iterator used for new line separator
$result_today = array();
foreach($query->result() as $xx):
$idx = $xx->idx;
$tot = $xx->total;
// get joke->title
$sql = "SELECT id,xrl,title FROM jokes WHERE id=$idx;";
$url = $this->db->query($sql);
$xrl = $url->row()->xrl;
$title = $url->row()->title . ' (' .$tot .')' ;
$short = character_limiter($url->row()->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;
}
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.
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…
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.
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.
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.
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.
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)