CODEWITHSUNDEEP
phpmysqldate
Phil
Phil

Reputation: 1453

PHP - Putting a date into a MySQL table

I have what is most likely a very simple question.. I am designing a simple blogging system and I am trying to put the current date into the table where the blog post is stored whilst waiting for administrator approval. but the method I have used puts 0000-00-00 into the date column! What I am using is as follows:

$query = "INSERT INTO blogentry VALUES ('".$mnam."','".date('d-m-Y h:m:s') ."\n"."','".$mcom."','".$approve."')";

I am relatively new to php so stumble accross errors like this all the time... but I cant seem to google this one!

Thanks guys!

Upvotes: 0

Views: 459

Answers (4)

kitti
kitti

Reputation: 14844

So the easiest way to do this is just let MySQL handle it with the NOW() function:

INSERT INTO blogentry VALUES( ..., NOW(), ... )

Another option is to use TIMESTAMPs by changing your table - set the column to type TIMESTAMP with DEFAULT CURRENT_TIMESTAMP, and you can just ignore that column when inserting - it will automatically be filled with the current time. You will need to specify the columns you're inserting to in order to skip a column:

INSERT INTO blogentry( column1, column2 ) VALUES( column1value, column2value )

Finally, you NEED to sanitize your inputs. Preferably using prepared statements and PDO (http://php.net/manual/en/pdo.prepared-statements.php), or at least using mysql_real_escape_string.

Upvotes: 3

drew010
drew010

Reputation: 69977

From the MySQL manual on DATE, DATETIME

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

This means you have to insert the dates in YYYY-MM-DD format. You are using date('d-m-Y h:m:s') format. Change that to date('Y-m-d') and it should insert correctly.

If you want the time as well, then you need to change the column datatype to DATETIME and then insert using the format date('Y-m-d H:i:s').

As other mention, you can use an INT column type instead and store a Unix timestamp which is stored in UTC so it is more portable. You can then easily manipulate the timestamp to output the date any way you would like.

Upvotes: 2

Captain Insaneo
Captain Insaneo

Reputation: 470

You might need to give the timestamp to the date function:

date('d-m-Y h:m:s', strtotime('now'))

Also, to do a standard datetime format:

date('Y-m-d H:i:s', strtotime('now'))

Upvotes: -1

Scott
Scott

Reputation: 5379

Try just storing a strtotime() result. It creates a unique timestamp, which can then be parsed however you need it in the future.

Upvotes: 1

Related Questions