OK, if I were to use PHP to insert a date into a table and I wanted to SELECT all rows form the table and display it ORDERED by date DESC, it would view date as text and not a date and would then order it according to this.
Now my question, how can I use PHP to insert the date into a MySQL table so that I can order the table by date? Also, how would I specify if I were to request all rows posted AFTER a certain date? (e.g. WHERE date > '10 March 2004' ?)
Hope this makes sense, as it's quite tricky to explain.OK, managed to get it right. I case anybody wants to know:
Save the time to dtb using time();
This saves the time as a large number (number of seconds since 1 January 1970 I think) and can then be easily sorted.
Also, to display these seconds in a nice format, use date(format,time_in_secs);standard method of storing dates as a timestamp
You can also use the MySQL functions FROM_UNIXTIME(); NOW(); etc.Originally posted by DarkStreetDev
Also, how would I specify if I were to request all rows posted AFTER a certain date? (e.g. WHERE date > '10 March 2004' ?)
Hope this makes sense, as it's quite tricky to explain.
if you store the date as time() then you cannot search for a date of "10 March 2004" as it would have no clue what it is. you will need to convert one or the other befroe you search
Now my question, how can I use PHP to insert the date into a MySQL table so that I can order the table by date? Also, how would I specify if I were to request all rows posted AFTER a certain date? (e.g. WHERE date > '10 March 2004' ?)
Hope this makes sense, as it's quite tricky to explain.OK, managed to get it right. I case anybody wants to know:
Save the time to dtb using time();
This saves the time as a large number (number of seconds since 1 January 1970 I think) and can then be easily sorted.
Also, to display these seconds in a nice format, use date(format,time_in_secs);standard method of storing dates as a timestamp
You can also use the MySQL functions FROM_UNIXTIME(); NOW(); etc.Originally posted by DarkStreetDev Also, how would I specify if I were to request all rows posted AFTER a certain date? (e.g. WHERE date > '10 March 2004' ?)
Hope this makes sense, as it's quite tricky to explain.
if you store the date as time() then you cannot search for a date of "10 March 2004" as it would have no clue what it is. you will need to convert one or the other befroe you search