PHP 5: How to sort results of a query by date?

Question

I created a blog site using PHP 5. When a user saves a blog, the date and time of submission is saved to a field in a database, using the NOW() function (using the format 2007-02-25 18:07:42).

Later, I would like to display a list of all the blogs saved to the database, listing the most recently saved blog first.

Displaying a list of the blogs is no problem. I display the list like this:

foreach($blog_array AS $key)
{
echo "key[0];
}

But how do I sort the entries by date, listing the most recent date
first?

Also, I'd like to be able to display the date in a different format that the one stored in the database. I'd like to be able to display the date as "February 28, 2007, 7:48 AM", or something like that. How can I do this?

Answer 1

If u are using a SQL database, select your recordset like this:

$sql = "SELECT * FROM ur_table WHERE some_condition ORDER BY ur_date_column";

Then, the loop to go thru ur recordset will be more efective if u fetch it like:

$res = mysql_query($sql, $db)
while ($row = mysql_fetch_object($res)) {
echo $row->somecolumn;
echo $row->anothercolumn;
}

Plus, what the previous guy said is way too much work... To format the date go like

echo date("F d, Y", strtotime( $row->date_column ) );

Hope it helps...

Answer 2

I like to use the Unix Timestamp to save dates. You can make one of these using the time() function.

Then, you can use the date() function to format your data in all sorts of different ways:

http://us3.php.net/date

I see that you are not using a database to hold your data. If you are using something like MYSQL, the syntax would be something like: SELECT * FROM `blog` ORDER BY `date`

So technicaly I did not answer your question, but you should be on your way.

Answer 3

For the sorting, you can do this in the query just append it at the end of ur query "order by 'YOUR_DATE_COLUMN'";

To change the date formate for display purpose, use

date("F d, Y", mktime(0,0,0,$nMonth, $nDay, $nYear));

Where $nMonth, $nDay, $nYear will be from the date value in database. You will have to fetch these things from that date field.