Hi all.I use $Timestamp=date("g:i A l, F j Y."); to collect time and date the visitors entered my site . It inserts the data in to mysql database in this format :



3:02 AM Monday, July 24 2006





mysql_query("INSERT INTO logdisplay VALUES('$ID','$column1','$Timestamp','$column2','$ columns3')");





$affected_rows = $sql->a_rows;





But when i query the database and use order by ,the data does not get displaied in order of newest on the top!!



select * from logdisplay order by Timestamp





could any one help me so that i get newst log records on the top .In another how to query my table baced on time an data.Thanks

I would set the field type to INT and store the time as timestamp, and not as formatted date. This way you will be able to order the records by time. You can change the format later when you output it.


date("g:i A l, F j Y.", $row['timestamp']);



mysql_query("INSERT INTO logdisplay VALUES('$ID', '$column1','". time() ."','$column2', '$columns3')");


Going to move this to database btw...

Right, if you store the date as a string the DB will treat it as a string, not a date.

In your case it would order by the time, then the day name, then the month name, then the month day, and finally by the year. If you are storing a date, try using one of the date-type columns.

I would set the field type to INT and store the time as timestamp, and not as formatted date. This way you will be able to order the records by time. You can change the format later when you output it.


date("g:i A l, F j Y.", $row['timestamp']);



mysql_query("INSERT INTO logdisplay VALUES('$ID', '$column1','". time() ."','$column2', '$columns3')");


Going to move this to database btw...

thank u for u reply. I use your insert query and now i get some thing like this in my database :1153840748

How i can read that ? or how i can query that ? could u show me how this time can be meaningful for me and how to output it ?Thanks

Right, if you store the date as a string the DB will treat it as a string, not a date.

In your case it would order by the time, then the day name, then the month name, then the month day, and finally by the year. If you are storing a date, try using one of the date-type columns.

could u show me how to store the date and be able to sort them acording to date later.thank

thank u for u reply. I use your insert query and now i get some thing like this in my database :1153840748

How i can read that ? or how i can query that ? could u show me how this time can be meaningful for me and how to output it ?Thanks

This is the timestamp. To convert it to a date format use.

date("g:i A l, F j Y.", $row['timestamp'])


The timestamp holds the number of seconds between 1st january 1970 and now. This will help you to order your records by date, and to output it in any format you want.

first i do not know how to stor both date and time in a way that i output all my records baced on time and date in accending or decending order ? could u just show me how to out the log history in that way?Thanks

Here's an example.


// Descending...
$query = mysql_query("SELECT * FROM logdisplay ORDER BY timestamp DESC");

// .. or ascending.
$query = mysql_query("SELECT * FROM logdisplay ORDER BY timestamp ASC");

// Loop through all entries.
while ($row = mysql_fetch_array($query))
{
// Do whatever

// To convert the timestamp to date use:
echo date("g:i A l, F j Y.", $row['timestamp']);
}

You could store it as a DATETIME type rather than a timestamp.

DATETIME is formatted as:
YYYY-MM-DD HH:MM:SS



I use your insert query and now i get some thing like this in my database :1153840748

How i can read that ? or how i can query that ? could u show me how this time can be meaningful for me and how to output it ?Thanks

It's easier to read straight out of the database, sortable using ASC/DESC, covers a much broader range than a timestamp, and you can use MySQL functions - such as UNIX_TIMESTAMP() - or PHP functions - such as strtotime() - to convert it to a timestamp for conversion with the PHP date() function.

You can also use the mysql DATE_FORMAT(date,format) function in your SELECT query string to format this the way you want.