Storing PHP time() in mySQL and getting problem to sorting recods

[shonir]

im working on a project repoting.database have almost up to 50000 records in a table. in this project i need to sort out records by days or month or by year. but problem is every recods have a column “addtime” there time() has been stored when record enterd.

i checked and with date(“d/m/Y”,$records[‘addtime’]) working fine. but how i can show by day or year and month below is my code please help its very importent and urgent.

[php]$result = $sm->queryfnc(“select * from {tbl}authors where status=1 and YEAR(addtime)=2011”, ‘Get Result’);
$rows = $sm->rowsfnc($result, ‘Get Rows’);
if($rows>0){
while($records = $sm->recordsfnc($result, ‘Get Records’)){
echo date(“d/m/Y”, $records[‘addtime’]).’
';

}
}[/php]

Thanks in advance for your attention

[ErnieAlex]

Shonir, I am not exactly sure what you are requesting, but, how I handle this is I create a timestamp using this formula : date(‘Y-m-d H:i:s’) This give the year-month-day: hours seconds. I add insert this into the database in a field called “LastUpdated”. Each time a record is editted or rewritten, this “last-update” field is also updated. In this manner you can use this code to insert it into the database when updated:
[php]
$query = “UPDATE tablename SET field1=‘data1’ , LastUpdated=’” . date(‘Y-m-d H:i:s’) . “’ WHERE searchname = ‘something’”;
$dbResID = mysql_query ( $query, $dbLinkID);
if (mysql_affected_rows()==0) {
$query = “INSERT INTO tablename (field1, LastUpdated) VALUES (‘something’ , '” . date(‘Y-m-d H:i:s’) . “’)”;
$dbResID = mysql_query ( $query, $dbLinkID);
}
mysql_close($dbLinkID);
[/php]

Why write it this way? The timestamp is alway unique and with this code you can UPDATE without errors if the record doesn’t exist it does an insert automatically.

Next, to order by the timestamp, your do your query and add " ORDER by LastUpdated asc" to the end to sort the query results by date entered. Or replace asc with desc. If you need to sort for one day, you would have to alter the query to add “where LastUpdated => starting date <= ending date”. You would have to parse the lastupdated field date(‘Y-m-d H:i:s’) and pull out the date by year/month/day…

This might get you started. I wasn’t clear exactly what you needed, but, this is how I handle timestamps.
Good luck with it…