使用包含空值的数组执行PDO [英] Execute PDO with an array containing null values

问题描述

我需要更新数据库，并且使用PDO的execute()方法，方法是给它提供一个数组作为参数.

I need to update a database and I use PDO's execute() method by giving it an array as parameters.

这个想法是，当尝试插入NULL值时，它给了我一个错误...

The idea is that it gives me an error when trying to insert a NULL value...

以下是发送查询/参数的示例:

Here's an example of query / parameters sent:

生成的查询:

UPDATE table SET name=?, id_extra1=?, id_extra2=? WHERE id_something=?

参数数组:

array (size=8)
  'name' => string 'testing' (length=6)
  'id_extra1' => string '2' (length=1)
  'id_extra2' => null
  'id_something' => string '1958' (length=4)

所以NULL值用于id_extra2

在id_extra2的代码中，我有一个类似的条件(想法是我有一个ID，或者为0，然后必须用NULL更新DB值) :

In the code for id_extra2 I have a condition like this (the idea is that I have either an ID, either 0 and then I have to update the DB value with NULL):

if ($_POST['id_extra2']==0) {
    $_POST['id_extra2'] = null;
}

我尝试将$_POST['id_extra2']设置为''并设置为NULL并设置为'null'，但仍然无法正常工作...

I tried setting $_POST['id_extra2'] to '' and to NULL and to 'null' but it's still not working...

任何想法都值得赞赏！谢谢！

Any idea is appreciated! Thanks!

推荐答案

请考虑使用bindValue而不是传递数组来执行.就像此处:

Please consider using bindValue instead of passing the array to execute. As it says here:

所有值均视为PDO :: PARAM_STR.

All values are treated as PDO::PARAM_STR.

应该有可能使它对应用程序的其余部分相当透明，因为您已经具有要作为数组更新的值.尝试例如像这样的东西:

It should be possible to make this pretty transparent to the rest of your application, since you already have the values you want to UPDATE as an array. Try e.g. something like this:

<?php
function executeWithDataTypes(PDOStatement $sth, array $values) {
    $count = 1;
    foreach($values as $value) {
        $sth->bindValue($count, $values['value'], $values['type']);
        $count++;
    }

    return $sth->execute();
}

$sth = $handle->prepare("UPDATE table SET name = ?, id_extra1 = ?, id_extra2 = ? WHERE id_something = ?");

$values = array();
$values[] = array('value' => 'testing', 'type' => PDO::PARAM_STR);
$values[] = array('value' => 2, 'type' => PDO::PARAM_INT);
$values[] = array('value' => null, 'type' => PDO::PARAM_NULL);
$values[] = array('value' => 1958, 'type' => PDO::PARAM_INT);

$result = executeWithDataTypes($sth, $values);
?>

正如您指出的那样，过去使用bindParam会让您头疼，请注意 bindValue 和 bindParam .就我个人而言，我从来没有使用bindParam，因为它有副作用，这使脚本的理解变得更加困难，尽管在某些情况下，这些作用会派上用场.

As you noted that using bindParam gave you headaches in the past, please be aware of the subtle difference between bindValue and bindParam. Personally, I never use bindParam because of side effects which make it harder to understand scripts, though there are of course cases where these effects will come in handy.

您当然可以通过以下类似的操作来进一步简化函数并摆脱将类型指定为传递的数组中的其他键的需要:

You could of course simplify the function even more and get rid of the need of specifying the type as additional key in the passed array by doing something like:

$type = PDO::PARAM_STR;
switch(true) {
    case is_null($value): $type = PDO::PARAM_NULL; break;
    case is_numeric($value): $type = PDO::PARAM_INT; break;
    // ...
    default: break;
}

并根据数组中传递的值的类型确定类型；但是，由于例如浮点数也是数字，这会导致在上面的switch语句中做出错误的决定，但我想为完整起见，我会提到它.

and determine the type based on the type of the value passed in the array; however, that's more error-prone, since e.g. floats are also numeric and that would lead to a wrong decision in the above switch statement, but I thought I'd mention it for the sake of completeness.

这篇关于使用包含空值的数组执行PDO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！