...

View Full Version : Resolved Group news posts by date?



Jakez
04-29-2009, 10:42 AM
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

Eran
04-29-2009, 11:50 AM
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.

Jakez
04-29-2009, 12:04 PM
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.. :(

Eran
04-29-2009, 12:33 PM
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.



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

}

Jakez
04-29-2009, 12:53 PM
Still not sorting by the views.. here's what I put:



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

Eran
04-29-2009, 01:23 PM
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.

Jakez
04-29-2009, 10:12 PM
Ok here is what I have and what it is printing out, it's not ordering by the views AFAIK:



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


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.

Jakez
04-30-2009, 04:39 AM
Anyone? It can't be that hard.. :(

Fou-Lu
04-30-2009, 05:10 AM
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:


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

Jakez
04-30-2009, 10:55 PM
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



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 :(

Jakez
05-01-2009, 01:59 AM
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;

Fou-Lu
05-01-2009, 03:13 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum