How do I insert NULL values using PDO?

I'm using this code and I'm beyond frustration:

try {
    $dbh = new PDO('mysql:dbname=' . DB . ';host=' . HOST, USER, PASS);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
}
catch(PDOException $e)
{
    ...
}
$stmt = $dbh->prepare('INSERT INTO table(v1, v2, ...) VALUES(:v1, :v2, ...)');
$stmt->bindParam(':v1', PDO::PARAM_NULL); // --> Here's the problem

PDO::PARAM_NULL, null, '', all of them fail and throw this error:

Fatal error : Cannot pass parameter 2 by reference in /opt/...

---

I'm just learning PDO, but I think you need to use bindValue , not bindParam

bindParam takes a variable, to reference, and doesn't pull in a value at the time of calling bindParam . I found this in a comment on the php docs:

bindValue(':param', null, PDO::PARAM_INT);

EDIT: PS You may be tempted to do this bindValue(':param', null, PDO::PARAM_NULL); but it did not work for everybody (thank you Will Shaver for reporting.)

---

When using bindParam() you must pass in a variable, not a constant. So before that line you need to create a variable and set it to null

$myNull = null;
$stmt->bindParam(':v1', $myNull, PDO::PARAM_NULL);

You would get the same error message if you tried:

$stmt->bindParam(':v1', 5, PDO::PARAM_NULL);

---

When using INTEGER columns (that can be NULL ) in MySQL, PDO has some (to me) unexpected behaviour.

If you use $stmt->execute(Array) , you have to specify the literal NULL and cannot give NULL by variable reference. So this won't work:

// $val is sometimes null, but sometimes an integer
$stmt->execute(array(
    ':param' => $val
));
// will cause the error 'incorrect integer value' when $val == null

But this will work:

// $val again is sometimes null, but sometimes an integer
$stmt->execute(array(
    ':param' => isset($val) ? $val : null
));
// no errors, inserts NULL when $val == null, inserts the integer otherwise

Tried this on MySQL 5.5.15 with PHP 5.4.1

链接地址: http://www.djcxy.com/p/66698.html

上一篇: PDO，Mysql和本地准备的语句

下一篇: 我如何使用PDO插入NULL值？