[RESOLVED] Working with dates

QuizToon

Simple question really.

How do I get a date to insert into my mysql database. I just want a date to be inserted when a user hits the submit button on the form. The user doesnt input a date, i assume it gets the date from the server.

also what is the correct sql to set up the field in my table. I have looked at the sql help but cant seem to work it out.

I currently have
field name: date
type: date
null: not null
default: 0000-00-00

Thanks in advance

cgraz

to get a timestamp of when the data was INSERTed into the database, use a timestamp field. It will automatically stamp the field when you run an INSERT, no need for you to do anything.

Then to retrive, just use mysql's DATE_FORMAT() when pulling the timestamp and format the date/time anyway you'd like.

QuizToon

Great

thank you, that has worked a treat. I have got it displaying now.

1 more thing though, i know how to get the rows to order in date order newest at the top but how do i get it to display in date and time order but without displaying the time?

my select query is:

$query = "SELECT id, name, email, web,  DATE_FORMAT(date,'%D %M %Y') as date FROM users ORDER by date DESC";

Thanks again

Installer

This should work (I'm assuming you've changed "date" to a timestamp field):

$query = "SELECT id, name, email, web, date " .
         "DATE_FORMAT(date,'%D %M %Y'), AS dateformatted " .
         "FROM users ORDER by date DESC";

Then use dateformatted in your display.

#2000 🆒

QuizToon

sorted, thanks very much

🙂