Date not inserting in mysql_query insert

Question

I am trying to insert a row into database. All entries insert correctly, however the date is inserted as:

0000-00-00

I have tried formatting the date manually:

        $updated = $a["updated"][0];

        $updated = explode(" ", $updated);
        $updated[1] = explode("/", $updated[1]);

        $y = date('Y');
        $m = $updated[1][1];
        $d = $updated[1][0];

        $updated = $y . '-' . $m . '-' . $d;

        $updated = strtotime( $updated );
        echo '<pre>'. $updated .'</pre>';
        $upd = date('Y-m-d', $updated);

        $a["updated"][0] = $updated;            
        echo '<pre>'. $upd .'</pre>';

This is the output of the var_dump($a):

1342134000

2012-07-13

array(3) {
  ["metal"]=>
  string(6) "Silver"
  ["am"]=>
  array(3) {
    [0]=>
    string(7) "2748.00"
    [1]=>
    string(8) "1779.220"
    [2]=>
    string(8) "2253.200"
  }
  ["updated"]=>
  array(1) {
    [0]=>
    int(1342134000)
  }
}

I have also tried using mysql_date_format but the row is not inserted at all:

$updated = mysql_date_format($updated);

This is my insert query

mysql_query("INSERT INTO LondonFixes VALUES(null,'" . $a["metal"] . "','AM'," . $a["am"][1] . "," . $a["am"][0] . "," . $a["am"][2] . "," . $a["updated"][0] . ", null)");

This is my table structure

    CREATE TABLE IF NOT EXISTS `LondonFixes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Metal` varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `AmPm` varchar(2) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `GBP` decimal(10,5) NOT NULL,
  `USD` decimal(10,5) NOT NULL,
  `EUR` decimal(10,5) NOT NULL,
  `Updated` date NOT NULL,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Entry` (`Metal`,`AmPm`,`Updated`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=229 ;

There is probably a very simple solution to this, but go easy on me please!

Have you tried hard-wiring a date in to your statement, just to make sure that the format your query is using is the same one that the database is expecting? — andrewsi
– andrewsi, Commented Jul 13, 2012 at 15:31
The value of $a["updated][0] appears to an integer unix timestamp value, rather than a formatted date string. To pass in the integer value, wrap it in a FROM_UNIXTIME in your SQL statement, -or- pass in the the formatted date string. (See my answer.) — spencer7593
– spencer7593, Commented Jul 13, 2012 at 16:21

Daniel Aranda · Accepted Answer · 2012-07-13 15:39:35Z

2

Month and day require leading zero.

Use:

$upd = date('Y-m-d', $updated);

Also be sure that your date is enclosing by quotations like string.

,'" . $a["updated"][0] . "'

In your code you do not have quotations for the date.

edited Jul 13, 2012 at 15:39

answered Jul 13, 2012 at 15:29

Daniel Aranda

6,5622 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sam Dufel Over a year ago

The n token is month without leading zeros - did you mean Y-m-d ?

Daniel Aranda Over a year ago

Check my last comment I added missing quotation that you have. Dates require a quotation like string.

spencer7593 Over a year ago

Actually, the month and day do not require leading zeros. MySQL handles date strings that are missing the leading zeros just fine. I think it's good practice to use the leading zeros. I prefer strings that are canonical (that is, sort by date value correctly when sorted as strings.) However, the MySQL INSERT statement doesn't require a two position month or two position day.

Daniel Aranda Over a year ago

Thank You @spencer7593 may be the leading zero could be more a good practice, rather than a current Mysql requirement, I just remember that the 0000-00-00 dates are commonly because two issues, one is the missing leading zero(old versions) and the other one is missing quotations.

spencer7593 Over a year ago

@Daniel, another way to get the "zero date" is to assign a unix timestamp integer, which is what the OP's code is doing... That's what the code he posted does anyway; it's not putting a formatted string in the SQL text.

Collectives™ on Stack Overflow

Date not inserting in mysql_query insert

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related