PHP/MySQL question: how do you sort posts by date AND views?

[Jakez]

Ok I asked this at a coding forum this time before coming here but it seems to have left them stumped

I have a table with a 'time' field which is a timestamp, and lets just say 2 other fields 'views' and 'title'.

I want to sort the posts into dates, and then sort each dates posts by views, example below:

April 29th
5430 views - News headline
3300 views - Another News headline
2300 views - Yet Another News headline

April 28th
6630 views - News headline
3200 views - Another News headline
1060 views - Yet Another News headline

I'm sure it can't be that hard.. maybe a simple query and some arrays idk.

[Jakez]

Hellllpppp

[quantum-x]

SELECT * FROM `table` ORDER BY `time` DESC,`views` DESC
.. ?

[Jakez]

Originally Posted by quantum-x

SELECT * FROM `table` ORDER BY `time` DESC,`views` DESC
.. ?

That was their first suggestion, didn't work :/

Even tried: SELECT * FROM `table`, DATE_FORMAT(`time`, '%M %e %Y') as formatTime ORDER BY `formatTime` DESC,`views` DESC
with no luck. The first one didn't sort them by views at all, and the 2nd one had the ones with most views at the top no matter what date.

Maybe I could just sort everything by views, and then as it's looping through throw each post into the group which date it belongs to..?

[KickAssJesse]

ORDER BY date, title DESC

[Jakez]

Originally Posted by KickAssJesse

ORDER BY date, title DESC

You mean views DESC? Doesn't work.

[ProG]

Originally Posted by Jakez

That was their first suggestion, didn't work :/

That should have worked. Are you sure the 'views' field is set to an integer type?

Originally Posted by Jakez

Even tried: SELECT * FROM `table`, DATE_FORMAT(`time`, '%M %e %Y') as formatTime ORDER BY `formatTime` DESC,`views` DESC

This query looks invalid. It should be something more like this:

SELECT *, DATE_FORMAT(`time`, '%M %e %Y') as `formatTime` FROM `table` ORDER BY `formatTime` DESC, `views` DESC

Originally Posted by Jakez

Maybe I could just sort everything by views, and then as it's looping through throw each post into the group which date it belongs to..?

I think ultimately you will want to create an array for ease of outputting the data. If you only sort by views, then build the array, you will still need to sort by date (which is much easier to do with mysql).

[Jakez]

Originally Posted by ProG

That should have worked. Are you sure the 'views' field is set to an integer type?



This query looks invalid. It should be something more like this:

SELECT *, DATE_FORMAT(`time`, '%M %e %Y') as `formatTime` FROM `table` ORDER BY `formatTime` DESC, `views` DESC

I think ultimately you will want to create an array for ease of outputting the data. If you only sort by views, then build the array, you will still need to sort by date (which is much easier to do with mysql).

views field is an integer, both of those queries output the same thing, they're printing the posts with the highest views at the top of everything, and the rest is sorted by date below them, but only because 98% of the data has 0 views because this is just in the testing stages and there are only like 2-3 posts with views over 0. Otherwise it would all probably just be sorted by views and the dates would be all mixed up.

Interesting, I've seen sites like this and always wondered myself...

What I think he means is split posts by date, then under each date, list all for that date, in order, like on www.leenks.com

[Jakez]

Originally Posted by Killswitch

Interesting, I've seen sites like this and always wondered myself...

What I think he means is split posts by date, then under each date, list all for that date, in order, like on www.leenks.com

Well leenks doesn't seem to be sorting them by the views or anything. My first post pretty much explains how I want it, everything is grouped by their date and within each date the posts are sorted by views..

[quantum-x]

In that case, pull them out in order of the dates, and then use PHP or your preferred poison to split them.

[Varius]

This should help you out

mysql> desc test;
+-------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+-------+
| title | varchar(100) | YES | | NULL | |
| views | int(11) | YES | | NULL | |
| time | timestamp | YES | | CURRENT_TIMESTAMP | |
+-------+--------------+------+-----+-------------------+-------+

mysql> select * from test;
+---------------------------------+-------+---------------------+
| title | views | time |
+---------------------------------+-------+---------------------+
| news headline | 3300 | 2009-04-30 15:22:57 |
| another news headline | 2300 | 2009-04-30 15:23:13 |
| another news headline yet again | 5430 | 2009-04-30 15:23:27 |
| another news headline yet again | 1060 | 2009-04-29 15:23:48 |
| another news headline | 6630 | 2009-04-29 15:24:10 |
| news headline | 3200 | 2009-04-29 15:24:25 |
+---------------------------------+-------+---------------------+
6 rows in set (0.00 sec)

mysql> select date(time) as date, views, title from test group by time order by date desc, views desc;
+------------+-------+---------------------------------+
| date | views | title |
+------------+-------+---------------------------------+
| 2009-04-30 | 5430 | another news headline yet again |
| 2009-04-30 | 3300 | news headline |
| 2009-04-30 | 2300 | another news headline |
| 2009-04-29 | 6630 | another news headline |
| 2009-04-29 | 3200 | news headline |
| 2009-04-29 | 1060 | another news headline yet again |
+------------+-------+---------------------------------+
6 rows in set (0.00 sec)

That last one there is what you want.

Keep in mind you obviously want to index your fields, and if there are two records with identical timestamps, you either want to group by time,title so they don't get grouped as one or add a primary key (like an auto increment id) to group by time,ID (preventing the rare case you have an entry with exactly the same title and time heh).

Enjoy

[Jakez]

Originally Posted by quantum-x

In that case, pull them out in order of the dates, and then use PHP or your preferred poison to split them.

I can group them by date no problem, it's the sorting by views within each date that I can't figure out.

[Jakez]

Originally Posted by Varius

Enjoy

Looks like that will work! Kick ass. I do have an auto increment setup already too. Trying that out now thanks!

[Jakez]

Shit, it's still placing the highest viewed posts at the very top no matter the date. Does it matter that my time field is the equivalent of time(), a unix timestamp, not necessarily a timestamp like you have..? How come yours works

[sarettah]

Originally Posted by Jakez

Shit, it's still placing the highest viewed posts at the very top no matter the date. Does it matter that my time field is the equivalent of time(), a unix timestamp, not necessarily a timestamp like you have..? How come yours works

Try using FROM_UNIXTIME() on your timestamp to convert it to a mysql timestamp format.

[Varius]

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

EDIT: as sarettah said while I was off taking a shower

[Varius]

Originally Posted by Varius

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

EDIT: as sarettah said while I was off taking a shower

should prob be group by date there not time sorry typo ;p

[Jakez]

Bingo! Works like a charm now - I knew GFY would get the job done, to think I've been slowing posting back and forth for a day or so about this on a coding forum, and found a solution on here within an hour and a half.

Originally Posted by Varius

should prob be group by date there not time sorry typo ;p

Works fine with group by time, if it ain't broke don't fix it lol.

I think I might use your style timestamp from now on though so I can at least read it easier, how do I insert that type of format when I'm inserting data? I know how to structure the table just not what command to put that format.

[Varius]

Originally Posted by Jakez

Bingo! Works like a charm now - I knew GFY would get the job done, to think I've been slowing posting back and forth for a day or so about this on a coding forum, and found a solution on here within an hour and a half.



Works fine with group by time, if it ain't broke don't fix it lol.

I think I might use your style timestamp from now on though so I can at least read it easier, how do I insert that type of format when I'm inserting data? I know how to structure the table just not what command to put that format.

I think you need a better coding forum to post at

Actually myself, I always use unix timestamps, here I just thought when you said timestamp in your first post you meant column type, that's why I did it like that. I'd say there isn't much difference between things though it depends on your app, might save a few function calls here and there if you chose one type over the other.

[Jakez]

Originally Posted by Varius

I think you need a better coding forum to post at

Actually myself, I always use unix timestamps, here I just thought when you said timestamp in your first post you meant column type, that's why I did it like that. I'd say there isn't much difference between things though it depends on your app, might save a few function calls here and there if you chose one type over the other.

Ah true, I do use the numbering of the unix timestamp in a lot of ways so I might not want to change it.

I was posting at codingforums.com - probably could have found a busier forum but it seemed ok. GFY is just that all in one deal, would prefer to ask here.

Oh, I thought you ment grouping them together like Leenks does, or Evilhumor.com

Anybody have any idea on how they go about that?

Varius mind if I hit you up and pick your brain on some stuff? You seem like a good person to talk to coding wise.

[Varius]

Originally Posted by Killswitch

Varius mind if I hit you up and pick your brain on some stuff? You seem like a good person to talk to coding wise.

Sure, got some stuff to do to get ready for the beach leaving at 3am and will be back prob Sunday night, but might be online a bit later tonight (as I doubt I'll sleep) so feel free to ICQ 520468 or Skype variuscr me

Originally Posted by Varius

Sure, got some stuff to do to get ready for the beach leaving at 3am and will be back prob Sunday night, but might be online a bit later tonight (as I doubt I'll sleep) so feel free to ICQ 520468 or Skype variuscr me

It's cool, I'll message ya some other time, no big hurry. My daughter turns 1 this weekend so I'll be busy away from the boards.

[Jakez]

Originally Posted by Killswitch

Oh, I thought you ment grouping them together like Leenks does, or Evilhumor.com

Anybody have any idea on how they go about that?

Here's how I did it basically, removed all the html to make it look more simple but it should still print out fine:

<?php
$result=mysql_query("SELECT *, from_unixtime(time,'%Y-%m-%d') as date FROM songs GROUP BY time ORDER BY date DESC, views DESC");
while($row=mysql_fetch_array($result))
{
if(!$date)
{
$date=date("l F j",$row[time]);
?>
<b><?=$date?></b><br>
<?=$row[artists]?> - <?=$row[title]?><br>
<?php
}
elseif(date("l F j",$row[time])==$date)
{
?>
<?=$row[artists]?> - <?=$row[title]?><br>
<?php
}
elseif(date("l F j",$row[time])!=$date)
{
$date=date("l F j",$row[time]);
?>
<b><?=$date?></b><br>
<?=$row[artists]?> - <?=$row[title]?><br>
<?php
}
}
?>

Interesting, I was thinking about putting it in an array and parse it that way.