Mysql date : 0000-00-00 00:00:00

Question

In my query:

       $cselect = mysql_real_escape_string($_POST['cselect']);
       ---------------
       ---------------
       $sql = sprintf("INSERT INTO content
       (id, catID, title, abstract, body, status, published, date, description_meta,   keywords_meta)
        VALUES ('', '%s', '%s','%s','%s','%s','%s','%s','', '' )", $cselect,$chead, $cabst,$ctext, $cp, $cradio,  'TIMESTAMP: Auto NOW()');

ouptput for date is: 0000-00-00 00:00:00. What is wrong in my query? Thanks in advance

Answer 1

TIMESTAMP: Auto NOW() is definitely not a correct value for a timestamp string and MySQL silently (unless you check warnings or enable strict mode) converts it to zero timestamp.

You should either use a function NOW() (without quotes around it) or rather CURRENT_TIMESTAMP instead.

Answer 2

u can use date('Y-m-d h:i:s') instead of 'TIMESTAMP: Auto NOW()'

for getting time now

Answer 3

Any reason you're using sprintf() for this? You're not formatting any of the values, other than perhaps forcing things to be treated as strings. The following non-function version would be far more readable, using a heredoc

$sql = <<<EOL
INSERT INTO content
    (id, catID, title, abstract, body, status, published, date, description_meta, keywords_meta)
VALUES
    ('', '$cselect', '$chead','$cabst','$ctext','$cp','$cradio', NOW(),'', '' );
EOL;

Note the NOW() call to fill in the date field. That returns the current date/time at the moment the query executes.

Please note that 'date' is a reserved word in MySQL and will cause syntax errors. You'll have to change the field name to something safe, and/or surround it with backticks (`) to 'escape' it.

As well, note that this way of building the query doesn't get around the fact that if any of this information is coming from untrusted sources you're wide open to SQL injection.

Answer 4

Hi Jasmine.

Instead of specifying the Date in your query!

Why don't you change your date column to a Timestamp type, and then set its default value to CURRENT_TIMESTAMP.

That way you wouldn't even need to bother with the date column in your queries, once MySql will handle that for you, by inserting the actual date when he receives your insert query.

Here goes Sql query to change you date column:

ALTER TABLE  `content ` CHANGE  `date`  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Hope this helps.