problem displaying records in order of time and date in php

[tony007]

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

[john tindell]

You should try storing timestamp in the database andf format it when you get it out, this will also allow you to more efficiantly sort your table by date.

MySQL has built in fucntions for formatting date,

http://dev.mysql.com/doc/refman/5.0/...functions.html

[tony007]

You should try storing timestamp in the database andf format it when you get it out, this will also allow you to more efficiantly sort your table by date.

MySQL has built in fucntions for formatting date,

http://dev.mysql.com/doc/refman/5.0/...functions.html

Thank u for u reply. This is exact way of i store time and date in my database table :3:02 AM Monday, July 24 2006

do u mean that is incorect ? could u show me how display my data baced on time and data and be able to output records of one months only too.Thanks

[lintz]

You sould store the time a user enters your site using Time() then once you query your database use the Date() function to format the time to what you want.

[tony007]

You sould store the time a user enters your site using Time() then once you query your database use the Date() function to format the time to what you want.

could u show me how i can use time . If i do not record date then how i can query the database baced on date? could u explain this to me more.thanks

[visualAd]

Thank u for u reply. This is exact way of i store time and date in my database table :3:02 AM Monday, July 24 2006

do u mean that is incorect ? could u show me how display my data baced on time and data and be able to output records of one months only too.Thanks

You have obviously set the column / field type to text. This means that any sort on the data will be alphabetical. You can do one of two things:

1. Change the timestamp column to a TIMESTAMP data type or a DATETIME as detailed here: http://dev.mysql.com/doc/refman/5.0/...ime-types.html. You can then use MySql's date & time functions within your queries to format the dates as you need them and/or sort them.
   
   
2. The second option is to use a UNIX timestamp. As you are dealing with dates after 13 Dec 1901, you won't have any problems. You can obtain a unix timestmap in PHP using the time() function, like lintz has already mentioned. You then need to store this in your database as an integer (this means you will need to change the timestamp field to an integer). Again, when formatting the timestmap you can use MYSql's date & time functions within the queries or you can use the date() function from within PHP.

[tony007]

Thanks visual . Is there a way to save current time in one column and date in seperate column and then when querying the logdisplay i just sort baced on date or time ? That would be easier way but i do not how to save those seperatly and later sort them baced on date and time!!

[lintz]

No need to save te date and time in seperate fields because if you save using UNIX timestamp then when you format the data you can format to display only the date, only the time or a combination.

[tony007]

No need to save te date and time in seperate fields because if you save using UNIX timestamp then when you format the data you can format to display only the date, only the time or a combination.

man i am not running unix server !! not i know if the hosting company is on unix!! fuethermore, how to record time and date so i can output my records in order of time and date. could any one just show me the insert statement and datatype and how to query the table so i get out put in order of time and date.

[visualAd]

We have already told you how to do it - you have been given several solutions infact. You do not need UNIX to use a unix timestamp; the unix timestamp is just a format used to store a date and time (it happens to be an integer corresponding to the number of seconds which have elapsed since 1 Jan 1970).

So if you use a UNIX timestamp, all you need to to is store it in your database as an integer and sort it as you would any other data type using the ORDER BY clause.

Give it a go and if you hit any problems post back; I for one will not just give you code and SQL to copy and paste into your scripts

[lintz]

I second that

[Matt_T_hat]

select * from logdisplay order by Timestamp

Surely the answer to get newest first would be

SELECT * FROM logdisplay ORDER BY Timestamp DESC

as the newer dates are a bigger number...

or did I miss something when I thought this threasd went off on a tangent?

[tony007]

I used unix time like and i got the date in this format :1154194814


mysql_query("INSERT INTO visitor VALUES('$ID', '$HTTP_X_FORWARDED_FOR','". time() ."','$countryName', '$country')");


do u guys think that is corect . so when i query in order of newese on the top should i do like this? :



SELECT * FROM visitor ORDER BY time DESC


do u guys think that will sort my data acording to both time and date ?

[lintz]

Yes!