SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    MOVIES:
    userid INT NOT NULL,
    moviename varchar(255) DEFAULT NULL,
    genreid INT NOT NULL,
    movieregion char(3) DEFAULT NULL,
    moviestatus varchar(10) DEFAULT NULL

    moviestatus is either set as WATCHED or UNWATCHED

    GENRES:
    id INT NOT NULL,
    genre_name varchar(255) DEFAULT NULL,
    genre_abbreviation varchar(25) DEFAULT NULL

    I have this code

    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:
    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/>\n";
    	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

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    With a count you need to use a group by as well.

    So, for example, you might do something like:
    Code:
    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.

  3. #3
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,805
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    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:
    PHP Code:
    CREATE TABLE IF NOT EXISTS `today` (
      `
    idint(5NOT NULL AUTO_INCREMENT,
      `
    datetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `
    idxint(5NOT NULL,
      
    PRIMARY KEY (`id`),
      
    KEY `date` (`date`,`idx`),
      
    KEY `idx` (`idx`)
    ENGINE=MyISAM  DEFAULT CHARSET=ucs2 AUTO_INCREMENT=54 
    Step 2:
    PHP Code:

    //========================
    //
    //  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:
    PHP Code:
    //============================================
    //
    //  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()->title20);
            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:
    PHP Code:

     
    echo '<pre';
       
    print_rget_today(25) );
     echo 
    '</pre'

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    I'm not really sure how that applies to this topic...

  5. #5
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,805
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    >>> 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.



    .

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samanime View Post
    With a count you need to use a group by as well.

    So, for example, you might do something like:
    Code:
    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!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, it would

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

    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.

  10. #10
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok let me take a stab at this, so am I correct in thinking I need to modify

    Code:
    $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

    Code:
    $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?

  11. #11
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    $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

    Code:
    $result = mysqli_query($link, "SELECT moviename, moviestatus 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.

  14. #14
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Sorry, let me rephrase.

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

  15. #15
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    $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:
    Code:
    echo $watches[$genre_abbreviation]['watched'] . '/' . $watches[$genre_abbreviation]['unwatched'];
    in your foreach.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by portem View Post
    Code:
    SELECT moviename, moviestatus genres.genre_abbreviation FROM MOVIES INNER JOIN genres ON movies.genreid=genres.id
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    $watches[$row['genre_abbreviation'])] = array('watched' => 0, 'unwatched' => 0);
    Removed it, now just getting Undefined index on watched and unwatched, in my output. Trying to figure out why.

  18. #18
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Can you paste the code you implemented in those two bits?

  19. #19
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  20. #20
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Cool, congrats.

  21. #21
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can't thank you enough. Posted elsewhere on the SP forums asking for a book recommendation so I can learn this level of stuff properly.

    Thank you x 100.

  22. #22
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    one final question if I may...

    how would I get each movie itself to display, for example, a different background color based on whether or not it is watched or unwatched? or even a little icon img to say it's watched or unwatched. either. I would like to sort and color code them based on their status.

    GENRE - 2/1 (Watched/Unwatched)
    -------------
    movie1 <-these
    movie2 <-these
    movie3 <-these

    would that just involve adding to the same array code or is that more complicated?

  23. #23
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Nope, in fact, what you need is already in the array.

    Where you have that <div> for your movie, just add class="{$movie['moviestatus']}".

    That'll put the movie status as a class for each movie.

    Then in your CSS, just create the CSS for each of them:
    Code:
    .U { background-color: #F00; }
    .W { background-color: #00F; }

  24. #24
    SitePoint Zealot
    Join Date
    May 2010
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it ok to have it like this?

    Code:
    echo '<div id="movie" class="$movie[moviestatus]">';
    I ask because I got errors the other way ("Parse error: syntax error, unexpected T_STRING, expecting ',' or ';'")

    but then for some reason the CSS doesn't seem to be working (been a long time since I've used any CSS so I may be doing it wrong heh)

    CSS looks like this:

    #movie .U {background-color: #F00;}
    #movie .W {background-color: #00F;}

  25. #25
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,805
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by portem View Post
    Is it ok to have it like this?

    Code:
    echo '<div id="movie" class="$movie[moviestatus]">';
    I ask because I got errors the other way ("Parse error: syntax error, unexpected T_STRING, expecting ',' or ';'")

    but then for some reason the CSS doesn't seem to be working (been a long time since I've used any CSS so I may be doing it wrong heh)

    CSS looks like this:

    #movie .U {background-color: #F00;}
    #movie .W {background-color: #00F;}
    Try this instead:
    PHP Code:

    echo "<div id='movie' class='" .$movie[moviestatus] ."'>"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •