Help! How do I add the date the record is created into mysql date field

tuscan

I am still learning although I seem to be learning fast. But I am stuck on a date field. I have added a date field to the mysql database with type DATE, not sure this is correct.

What I want to do is when a record is created in the database the DATE field is completed with the date of the record was created.

This is what I have done but all the database shows is 0000-00-00.

// get the date
$recorded = date("Y-F-d");  

echo $recorded ;   

// the echo is so I can see what it is

// then insert $recorded into the date field in the database

$result = mysql_query ( "INSERT INTO users SET  date = '$recorded' "  ) ;

If I use YEAR as the type I get 2008 in the database which is correct.

But I want it to show the whole date dd/mm/yyyy.

Can anyone out there hep me please.

Thanks
Tuscan

theelectricwiz

I believe just
$result = mysql_query ( "INSERT INTO users SET date = NOW()" ) ;
should do it

tuscan

Thank you theelectricwiz very much for your quick reply.

That was easy but I didn't know about NOW() so it was hard.

This works but can I change the format at the moment it displays yyyy-mm-dd but I would like dd-mm-yyyy.

Any help appreciated.

Thanks
Tuscan

theelectricwiz

um.... yes... There are many ways to do it, the easiest is directly through the mysql query
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename

tuscan

At the moment I display all the data in a table using the SELECT below.

$sql="SELECT * FROM users

Using the code you supplied it would look like this, I think

$sql2 =SELECT DATE_FORMAT(date, '%d/%m/%Y') FROM users

So will I have to use two SELECT statements one for everything except the date and one for the date or is there a way to use one SELECT if so any clues as to what it would look like?

Hope that makes sense.

Thanks
Tuscan

theelectricwiz

You only need one SQL statement like

$sql="SELECT *, DATE_FORMAT(date, '%d/%m/%Y') date_form FROM users";