使用包含空值的数组执行PDO预处理语句

I currently have an array that contains values from a form to insert into a database using PDO.

我目前有一个数组，其中包含要使用PDO插入数据库的表单中的值。

Some of the values are optional fields and will therefore be populated sometimes and NULL others.

某些值是可选字段，因此有时会填充，而其他值则为NULL。

The table I am inserting into is roughly 50 rows long and I am using the following:

我插入的表大约有50行，我使用以下内容：

$sth = Database::get()->prepare("INSERT INTO $this->_insertTbl
                                 (field1, field2, field3)
                                 VALUES
                                 (:field1, :field2, :field3)");
$sth->execute($dataArr);

The $dataArr can sometimes contain null values such as this:

$ dataArr有时可以包含空值，例如：

Array
(
    [field1] => 44
    [field2] => NULL
    [field3] => Jammin
}

And other times not:

其他时候不是：

Array
(
    [field1] => 44
    [field2] => Harry
    [field3] => William
}

That works normally with fully populated variables as well as empty strings, but doesn't seem to work with some NULL values.

这通常与完全填充的变量以及空字符串一起工作，但似乎不适用于某些NULL值。

Normally, as far as I understand you would use bindValue instead of bindParam, however as I am executing the statement with an array is there any way round that, or do I need to list each one individually?

通常，据我所知你会使用bindValue而不是bindParam，但是当我用数组执行语句时，是否有任何方法，或者我是否需要单独列出每个？

1 个解决方案

#1

NULL doesn't work with bound params in my experience. Just skip the assignment if the value is NULL.

在我的经验中，NULL不适用于绑定参数。如果值为NULL，则跳过赋值。

I've also replaced NULL with empty strings before...

我之前用空字符串替换了NULL ...

$params = array_map(function($param) {
    return is_null($param) ? '' : $param;
}, $params);

CodePad.

键盘。

(Assuming you are lucky enough to be >= PHP 5.3.)

（假设你很幸运> = PHP 5.3。）

Update

Turn your column names and values into an array and join them with join().

将列名和值转换为数组并使用join（）连接它们。

This allows you to easily build certain members if required.

这允许您在需要时轻松构建某些成员。