Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group news posts by date?

    What is the best way to group news posts by their day?

    I have about 10 new MySQL entries per day with a timestamp and on the site I want to list them in a group by their date. For example:

    April 29 2009
    News headline for a story from the 29th - 20 views
    Another new headline for a story from the 29th - 2 views
    Yet ANOTHER new headline.. for a story from the 29th - 420 views

    April 28 2009
    A news headline for a story from the 28th - 2 views
    Gee it's another new headline for a story from the 28th! - 3 views
    Just one more new headline.. for a story.. 28th! - 1 view


    If someone could help me with that that would be awesome.. BUT, here's the twist if you're up for it. I want to sort each days posts by views also. For example:

    April 29 2009
    Yet ANOTHER new headline.. for a story from the 29th - 420 views
    News headline for a story from the 29th - 20 views
    Another new headline for a story from the 29th - 2 views

    April 28 2009
    Gee it's another new headline for a story from the 28th! - 3 views
    A news headline for a story from the 28th - 2 views
    Just one more new headline.. for a story.. 28th! - 1 view

  • #2
    Regular Coder
    Join Date
    Apr 2009
    Location
    somewhere over the rainbow
    Posts
    105
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Code:
    SELECT post_date, topic, views FROM news ORDER BY post_date, views
    it orders first by date and when he gets several same dates it sorts by views.

  • #3
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Eran View Post
    Code:
    SELECT post_date, topic, views FROM news ORDER BY post_date, views
    it orders first by date and when he gets several same dates it sorts by views.
    Hm, that isn't sorting it by the views..

  • #4
    Regular Coder
    Join Date
    Apr 2009
    Location
    somewhere over the rainbow
    Posts
    105
    Thanks
    2
    Thanked 14 Times in 14 Posts
    you don't want to use `group by` because you want the all records.
    this is your SQL statement and on the while loop just save the date and print it once for the hall group.

    PHP Code:
    $q mysql_query('SELECT post_date, topic, views FROM news ORDER BY post_date, views') or die(mysql_error());
    while(
    $a mysql_fetch_array($q))
    {
        if (
    $post_date != $a['post_date']) {
            
    $post_date $a['post_date'];
            echo 
    $a['post_date'];
        }
        
        
    loop the rest cols..
            


  • #5
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Still not sorting by the views.. here's what I put:

    Code:
    $q = mysql_query('SELECT time, title, views FROM songs ORDER BY time, views') or die(mysql_error());
    while($a = mysql_fetch_array($q))
    {
    	$date=date("l F j",$a[time]);
        if ($post_date != $date) {
            $post_date = $date;
            echo $date." - ".$a[title]." - ".$a[views]."<br>";
        }else{
            echo $date." - ".$a[title]." - ".$a[views]."<br>";
    	}
        
            
    }
    It's grouping them into dates fine but the ones with the most views are still in random places in the middle according to their timestamp.

    I'm thinking everything needs to be thrown into some complex array(s) and use sort() somewhere.. but I suck at arrays..
    Last edited by Jakez; 04-29-2009 at 02:07 PM.

  • #6
    Regular Coder
    Join Date
    Apr 2009
    Location
    somewhere over the rainbow
    Posts
    105
    Thanks
    2
    Thanked 14 Times in 14 Posts
    i don't know if this is your cols names but you confused with my example.
    and you can do
    ORDER BY time asc, views desc

    second, the if is for displaying the date only once like you did on bold.

  • #7
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok here is what I have and what it is printing out, it's not ordering by the views AFAIK:

    Code:
    	$result=mysql_query("SELECT * FROM songs ORDER BY time DESC, views DESC");
    	while($row=mysql_fetch_array($result))
    	{
    		$date=date("F j",$row[time]);
    		print "$date - $row[views] views - $row[artists] - $row[title]<br>";
    	}
    Results:
    Code:
    April 29 - 0 views - Red Cafe Ft. Jadakiss, Rick Ross, Fabolous, OJ Da Juiceman & Diddy - Hottest in the Hood (Remix)
    April 28 - 0 views - Chrisette Michele - Blame It On Me
    April 28 - 0 views - Lloyd Banks - Shut Your Bloodclot Mouth Freestyle
    April 28 - 0 views - Rock City - Think About Us (Toni Braxton Demo)
    April 28 - 0 views - Jeremih Ft. Teairra Mari - Birthday Sex (Remix)
    April 28 - 0 views - Black Eyed Peas Ft. Busta Rhymes - Boom Boom Pow (Remix)
    April 28 - 0 views - Kid Cudi - Conglomerate Freestyle
    April 28 - 0 views - Ace Hood Ft. The Broward County All Stars - Ride For My City
    April 28 - 0 views - Gucci Mane Ft. Busta Rhymes & Shawty Lo - Do It (Prod. by Fat Boi)
    April 28 - 0 views - The Game Ft. Dirtbag - In a Hurry
    April 28 - 0 views - Drake Ft. Tanya Morgan - Right To Left
    April 28 - 0 views - Rick Ross - Deeper Than Rap Lost Intro
    April 28 - 0 views - Statik Selektah Ft. Styles P & Talib Kweli - The Thrill Is Gone
    April 28 - 0 views - Ya Boy (YB the Rockstar) Presents One Nation Ft. Cik.Money - Hate (I Gotta Watch)(Prod by Pete G)
    April 28 - 0 views - L.M.S. Ft. J-Roc - Get Ya Number
    April 28 - 0 views - R.Kelly Ft. Clyde Carson - Take It To The Hotel (Remix)
    April 28 - 0 views - Tyga - Diddy Bop
    April 27 - 0 views - Bobby Creekwater - Hey
    April 27 - 0 views - Trae & Jadakiss - Life
    April 27 - 0 views - Young Steff - Private Dancer
    April 27 - 0 views - Drake Ft. Juice - Best I Ever Had (Remix)
    April 27 - 0 views - Birdman Ft. Jay Sean - Written On Her
    April 27 - 0 views - Swizz Beatz - When I Step In The Club (Prod By Swizz Beatz)
    April 27 - 0 views - R.Kelly Ft. OJ Da Juiceman - Superman High
    April 27 - 0 views - Art of Raw Ft. Gucci Mane - Hella Bad (Produced by Inkredibles)
    April 27 - 0 views - Lloyd Banks - Flight Scool Freestyle
    April 27 - 0 views - Cassie - Not What Love Is
    April 27 - 10 views - Ron Paul Ft. Max B - Seven Digits
    April 27 - 0 views - Lloyd Banks - Shut Ya Bloodclot Mouth Freestyle
    April 27 - 0 views - Electrik Red Ft. Ludacris - We **** You (Remix)
    April 27 - 0 views - Sean Paul - So Fine
    April 27 - 0 views - Crooked I - Slaughterhouse Is A Big Deal
    April 27 - 0 views - Jim Jones Ft. NOE & Sandman - BG Brawl (Prod. By Don Cannon)
    April 27 - 0 views - Huey Ft. Tydis - Celebrate
    April 27 - 0 views - Huey Ft. Rock City & Trey Songz - No Make Up (Remix)
    April 27 - 0 views - Yung L.A. Ft. Yung Woo & Sed - One Dunnnnn (prod. by Drum Majors)
    April 27 - 5 views - Rick Ross Ft. Usher - Everything You Want (Long Snip)
    April 27 - 0 views - BlackEyedPeas Ft. 50 Cent & Gucci Mane - Boom Boom Pow (Remix)
    April 27 - 0 views - Jerimiah Ft. Fabolous - Birthday Sex (Remix)
    April 21 - 0 views - Boo Rossini - Ultimate High
    April 21 - 0 views - Rick Ross Ft. Jim Jones - NanaNana
    April 21 - 0 views - Nicki Minaj Ft. Lil Wayne - Go Hard
    April 21 - 0 views - Lil Boosie - Loaded
    April 21 - 0 views - Chris Brown - Lets Smash
    April 21 - 0 views - Pastor Troy - Is That Your Girl
    April 21 - 10 views - Gucci Mane - Take It Back
    April 21 - 0 views - Lil Wayne - In The Morning
    April 21 - 0 views - Clipse Ft. Kanye West - Kinda Like A Big Deal
    April 21 - 0 views - Tyga Ft. Lil Wayne - Lay You Down
    See, the ones with the most views in their respective date are still in the middle somewhere instead of at the top/bottom of that date.

  • #8
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Anyone? It can't be that hard..

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You're time field will need formatting during you're selection. What you have is correct otherwise. From the looks of it, you're `time` is a timestamp (integer) insertion. This is fine, but it is also the reason why you can't order it by the views since each of the time is different than the other ones.
    Try:
    PHP Code:
    $sQry "SELECT *, DATE_FORMAT(`time`, '%M %e %Y') AS formatTime FROM songs ORDER BY formatTime DESC, views DESC";
    $result=mysql_query($sQry); 
    I'm not thinking very straight today, so try that and see if it works.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #10
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well that kinda worked.. except all the posts with the highest clicks are at the top no matter the date.. and the rest is still sorted into dates.. lol

    Code:
    April 21 - 10 views - Gucci Mane - Take It Back
    April 27 - 10 views - Ron Paul Ft. Max B - Seven Digits
    April 27 - 5 views - Rick Ross Ft. Usher - Everything You Want (Long Snip)
    April 21 - 0 views - Tyga Ft. Lil Wayne - Lay You Down
    April 21 - 0 views - Clipse Ft. Kanye West - Kinda Like A Big Deal
    April 21 - 0 views - Lil Wayne - In The Morning
    April 21 - 0 views - Pastor Troy - Is That Your Girl
    April 21 - 0 views - Chris Brown - Lets Smash
    April 21 - 0 views - Lil Boosie - Loaded
    April 21 - 0 views - Nicki Minaj Ft. Lil Wayne - Go Hard
    April 21 - 0 views - Rick Ross Ft. Jim Jones - NanaNana
    April 21 - 0 views - Boo Rossini - Ultimate High
    April 27 - 0 views - Jerimiah Ft. Fabolous - Birthday Sex (Remix)
    April 27 - 0 views - BlackEyedPeas Ft. 50 Cent & Gucci Mane - Boom Boom Pow (Remix)
    April 27 - 0 views - Huey Ft. Tydis - Celebrate
    April 27 - 0 views - Huey Ft. Rock City & Trey Songz - No Make Up (Remix)
    April 27 - 0 views - Yung L.A. Ft. Yung Woo & Sed - One Dunnnnn (prod. by Drum Majors)
    April 27 - 0 views - Crooked I - Slaughterhouse Is A Big Deal
    April 27 - 0 views - Jim Jones Ft. NOE & Sandman - BG Brawl (Prod. By Don Cannon)
    April 27 - 0 views - Electrik Red Ft. Ludacris - We **** You (Remix)
    April 27 - 0 views - Sean Paul - So Fine
    April 27 - 0 views - Lloyd Banks - Shut Ya Bloodclot Mouth Freestyle
    April 27 - 0 views - Lloyd Banks - Flight Scool Freestyle
    April 27 - 0 views - Cassie - Not What Love Is
    April 27 - 0 views - Bobby Creekwater - Hey
    April 27 - 0 views - Trae & Jadakiss - Life
    April 27 - 0 views - Young Steff - Private Dancer
    April 27 - 0 views - Drake Ft. Juice - Best I Ever Had (Remix)
    April 27 - 0 views - Birdman Ft. Jay Sean - Written On Her
    April 27 - 0 views - Swizz Beatz - When I Step In The Club (Prod By Swizz Beatz)
    April 27 - 0 views - R.Kelly Ft. OJ Da Juiceman - Superman High
    April 27 - 0 views - Art of Raw Ft. Gucci Mane - Hella Bad (Produced by Inkredibles)
    April 28 - 0 views - Tyga - Diddy Bop
    April 28 - 0 views - R.Kelly Ft. Clyde Carson - Take It To The Hotel (Remix)
    April 28 - 0 views - L.M.S. Ft. J-Roc - Get Ya Number
    April 28 - 0 views - Ya Boy (YB the Rockstar) Presents One Nation Ft. Cik.Money - Hate (I Gotta Watch)(Prod by Pete G)
    April 28 - 0 views - Statik Selektah Ft. Styles P & Talib Kweli - The Thrill Is Gone
    April 28 - 0 views - Rick Ross - Deeper Than Rap Lost Intro
    April 28 - 0 views - Drake Ft. Tanya Morgan - Right To Left
    April 28 - 0 views - The Game Ft. Dirtbag - In a Hurry
    April 28 - 0 views - Gucci Mane Ft. Busta Rhymes & Shawty Lo - Do It (Prod. by Fat Boi)
    April 28 - 0 views - Ace Hood Ft. The Broward County All Stars - Ride For My City
    April 28 - 0 views - Kid Cudi - Conglomerate Freestyle
    April 28 - 0 views - Black Eyed Peas Ft. Busta Rhymes - Boom Boom Pow (Remix)
    April 28 - 0 views - Jeremih Ft. Teairra Mari - Birthday Sex (Remix)
    April 28 - 0 views - Rock City - Think About Us (Toni Braxton Demo)
    April 28 - 0 views - Lloyd Banks - Shut Your Bloodclot Mouth Freestyle
    April 28 - 0 views - Chrisette Michele - Blame It On Me
    April 30 - 0 views - Red Cafe Ft. Jadakiss, Rick Ross, Fabolous, OJ Da Juiceman & Diddy - Hottest in the Hood (Remix)
    April 30 - 0 views - Lil Wayne & Playaz Circle - Big Dog
    April 30 - 0 views - T.I. & Chris Brown - Don't Matter
    April 30 - 0 views - Ludacris - People
    April 30 - 0 views - Shawty Lo Ft. Rick Ross, Bun B & Lyfe Jennings - 911
    April 30 - 0 views - Plies & Young Buck - **** What They Talkin Bout
    April 30 - 0 views - Lil' Twist Ft. Lil' Wayne - The Leak
    April 30 - 0 views - Lil Wayne - Brand New Money
    April 30 - 0 views - Lil Wayne - Go
    April 30 - 0 views - Ludacris Ft. Snoop Dogg, Katt Williams and Nate Dogg - Good Relationships
    April 30 - 0 views - Ludacris Ft. Gucci Mane and Trae - Don't Love Her
    April 30 - 0 views - Ludacris - Can't Live
    April 30 - 0 views - Wiz Khalifa Ft. Ya Boy & Mistah FAB - Boarding Pass (Remix)
    April 30 - 0 views - Mucho Deniro - 501 Denims (Prod By Trak D)
    April 30 - 0 views - Lil Wayne - Fix My Hat
    April 30 - 0 views - Clipse Ft. Kanye West & Bun B - Kinda Like A Big Deal (Remix)
    April 30 - 0 views - Willy Northpole Ft. Bobby Valentino - One More Dance
    April 30 - 0 views - Bobby Valentino Ft. Mavado - Hands On Me (Remix)
    April 30 - 0 views - B.o.B aka Bobby Ray - Wait Your Turn
    April 30 - 0 views - Alchemist Ft. Three 6 Mafia & Juvenile - That'll Work
    April 30 - 0 views - R.O.M.E Ft. Mistah FAB - Star Children
    April 30 - 0 views - Day26 - Tellin Me Nothin'
    April 30 - 0 views - Raekwon - Resolution
    April 30 - 0 views - Raekwon - Gorilla Rap
    April 30 - 0 views - Uncle Murda - Where We At !
    April 30 - 0 views - Juicy J Ft. Webbie & Project Pat - Ugh Ugh Ugh
    April 30 - 0 views - Kesha Ft. Three 6 Mafia - Slow Motion
    April 30 - 0 views - Chrisette Michele - Porcelain Doll
    If I could just get each dates worth of posts into their own group, and THEN sort each group by clicks, that would work.. but like I said I suck at array
    Last edited by Jakez; 04-30-2009 at 11:58 PM.

  • #11
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Found a solution for those interested. I think the unix timestamp was part of the reason it wasn't working here but here is the query that got this done for me nonetheless:

    select from_unixtime(time,"%Y-%m-%d") as date, views, title from test group by time order by date desc, views desc;

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Great to hear mate!
    I knew we were close, I thought it was the format that was killing it (MeY), but completely forgot that the from_unixtime existed.

    Today is another head in the clouds day too

    Only suggestion I have is to use uppercase for you're SQL commands and functions. This is by no means necessary, but I find it is easier to later identify you're fields versus builtin syntax.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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