How to make MYSQL query to sort the articles by date ??

[worth2talk]

Hi All,

I am struggling since last 3 hrs and yet not able to find a way to get this done. Let me describe my problem.

 

I am developing a site using PHP and MYSQL where i can post articles. Each Article has features like author, date, data etc... I want to find out top 5 recent articles.

 

I store the date of an article in database as  $timeStamp = Date("F j, Y");

 

when i make a query to retrieve the latest article by date as

 

$result = mysql_query("SELECT * FROM postData ORDER BY postDate DESC LIMIT 0, 5");

 

it doesn't give any output...

 

Can anyone figure our what is wrong ?

 

Provide me a way to get this done... Your quick reply will be appreciated.

 

Thanks in advance.

[darkfreaks]

$result = mysql_query("SELECT * FROM postData ORDER BY postDate DESC LIMIT 5");

 

because you were returning zero records from 5 records see what happens when you return 5 records.

 

[worth2talk]

Thats its not retuning 0 records otherwise i cant able to get out data like author name, and post data. The issue is specifically with date...

[rarebit]

Easiest way is to store date as you get it from time(), e.g. as a unix timestamp. This is basically returns an int, which is great for comparing things to. Then when you wish to show the user, then you format it to a specific format, this is because it may be different depending upon timezone etc...

 

The way your doing it is ordering them by the the numeric value of the character string and the way they sum up is not in the same order as the way the months, days, etc are spelled! (if that's clear?)

[worth2talk]

Can you give me an example, how to store it and retrieve it from database ?

 

thanks in advance

[rarebit]

Sample code for table creation:

CREATE TABLE tn { postDate int, etc };

 

Insert it:

$postDate = time();
$result = mysql_query("INSERT INTO tn VALUES (x, y, z, '$postDate'");

 

Query:

$result = mysql_query("SELECT * FROM postData ORDER BY postDate");

[worth2talk]

it works..

Defining date as an integer and store...will help me getting it by DEC order...

 

Post in programming talk at http://www.programmingtalk.com/archive/index.php/t-31240.html also helps me.

[darkfreaks]

feel free to hit topic solved and your welcome