Sorting by Date

[mchit]

I am trying to retrieve the most recent date in my "news" table. The "DATE" field is type DATE with the format 0000-00-00. In the end I'd like it to display as Feb. 3rd, 2005 but for now I just want to figure out how to get just the most recent date extracted from the table.

This is what I have so far:

$result = @mysql_query('SELECT date FROM news ORDER BY date DESC'); 
    if (!$result) { 
    exit('<p>Error performing query: ' . mysql_error() . '</p>'); 
    }

Thanks for any help with this.

[devinemke]

your query syntax is correct but be advised that you are only grabbing the date, you will probably want to grab other fields as well.

to format the date use the DATE_FORMAT function.

[mchit]

Ok, so if I do a SELECT * instead of the date that will get all the fields, correct?

Also, I searched around and found the "while" loop below but all I want is the very first date. This prints out all the dates. I know I'm close, but how to get just the most recent date from this?

Thanks!

$result = @mysql_query('SELECT * FROM news ORDER BY date DESC');  

    if (!$result) {  

    exit('<p>Error performing query: ' . mysql_error() . '</p>');  

    }


while ($row = mysql_fetch_array($result)) {
        echo '<p>' . $row['date'] . '</p>';
}

[devinemke]

if you only want the most recent:

SELECT * FROM news ORDER BY date DESC LIMIT 1

[mchit]

$result = mysql_query("SELECT * FROM news ORDER BY date DESC"); 
    for ($i = 0; $i < 5; $i++) {
    $record=mysql_fetch_array($result);
    $date = date('M. jS, Y', strtotime($result['date']));

echo $date;
}

Ok, I've been playing around with this code and I've got something weird going on with this. It is printing out Feb. 15th, 2005 5 times in a row, and that's ok except I have no Feb. 15th, 2005 entries in my database. Anyone know why this is happening?

Thanks!

[xblue]

mchit,

I know that some folks disagree and do prefer to handle dates in php, but I think it would be a lot easier to use mysql's built-in date handling functions here as devinmeke suggested.

ex
SELECT *, DATE_FORMAT(datecolumn, '%b. %D %Y') ...

see
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

[mchit]

Ok, I've got just one more question. What TYPE should my DATE field in MySQL be?

type DATE? or type INT? or what?

Thanks!

[devinemke]

if you want to store you dates as dates then use DATE, if you want to store you dates as unix teimstamps then use INT. i personally use DATE.