Mysql pdo different behaviour at 2 servers

Question

Why at 1st server such code on update set "00-00-00 00:00:00" and at the 2d set current time

$pdo = new PDO;
$sth = $pdo->prepare("INSERT INTO `tbl_process`
                      SET `good` = :good, `type` = :type, `pid` = :pid, `time` = :time
                      ON DUPLICATE KEY UPDATE `time` = :time");
$sth->bindParam(':good', $good);
$sth->bindParam(':type', $type);
$sth->bindParam(':pid', $pid);
$sth->bindParam(':time', $time);
$sth->execute();

if i change code to this (add :time2)- i get the right time in both cases

$pdo = new PDO;
$sth = $pdo->prepare("INSERT INTO `tbl_process`
                      SET `good` = :good, `type` = :type, `pid` = :pid, `time` = :time
                      ON DUPLICATE KEY UPDATE `time` = :time2");
$sth->bindParam(':good', $good);
$sth->bindParam(':type', $type);
$sth->bindParam(':pid', $pid);
$sth->bindParam(':time', $time);
$sth->bindParam(':time2', $time);
$sth->execute();

Is emulation on? `You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.` -http://www.php.net/manual/en/pdo.prepare.php — chris85, Apr 20 '16 at 15:00
Make sure you configure PDO to throw useful exceptions. By default, except for initial connection, it errors silently. See: http://php.net/manual/en/pdo.error-handling.php. — Mike, Apr 20 '16 at 15:00

score 0 · Answer 1 · answered Apr 20 '16 at 15:01

0

Placeholders must be UNIQUE within a query. You're not allowed to re-use them:

  SET `good` = :good, `type` = :type, `pid` = :pid, `time` = :time
                                                             ^^^^^
  ON DUPLICATE KEY UPDATE `time` = :time");
                                    ^^^^^

The second query works because you've used a different placeholder name.

answered Apr 20 '16 at 15:01

Marc B

356,200
43
426
500

You don't read the question attentively – yeryr Apr 20 '16 at 18:02
and did you even TRY this solution? you've re-used a placeholder, which is not permitted. – Marc B Apr 20 '16 at 18:04

score -3 · Answer 2 · edited Apr 20 '16 at 15:03

PDO has three error handling modes.

PDO::ERRMODE_SILENT acts like mysql_* where you must check each result and then look at $db->errorInfo(); to get the error details. PDO::ERRMODE_WARNING throws PHP Warnings PDO::ERRMODE_EXCEPTION throws PDOException. In my opinion this is the mode you should use. It acts very much like or die(mysql_error()); when it isn't caught, but unlike or die() the PDOException can be caught and handled gracefully if you choose to do so.

Getting the Last Insert Id

<?php
$result = mysql_query("INSERT INTO table(firstname, lastname) VALUES('John', 'Doe')") or die("Insert Failed ".mysql_error());
$insert_id = mysql_insert_id();

So far we've only shown simple statements that don't take in any variables. These are simple statements and PDO has the shortcut methods query for SELECT statements and exec for INSERT, UPDATE, DELETE statements. For statements that take in variable parameters, you should use bound parameter methods to execute your queries safely. Consider the following mysql_* code.

This does not answer the question and according to the last sentence it is incomplete as well. — Mike, Apr 20 '16 at 15:02
OP is using `PDO`, why go back to `mysql_`? This also doesn't relate to the issue the OP is having. — chris85, Apr 20 '16 at 15:03

Mysql pdo different behaviour at 2 servers

2 Answers2