使用PDO更新具有多个数组的SQL表 [英] update SQL table with multiple arrays using PDO

问题描述

我想使用PHP和PDO更新SQL表.但是我不断收到以下错误

I would like to update a SQL table using PHP with PDO. However I keep getting the following error

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\xampp\htdocs\core\functions\update_projects.php on line 31

我只是不知道我要去哪里错了.

I just can't make sense of where I'm going wrong.

    $j = 1;
    $chunk_count = count($update)/7;
    $backwards = array_reverse($update);
    $chunks = array_chunk($backwards, 7);

    var_dump($chunks[1]);       

    try {
        for($i = 0; $i < $chunk_count; $i++ ) {
            $update_project = $db->prepare('
                UPDATE projects
                SET comments = ?,
                    contact = ?,
                    est_end = ?,
                    est_start = ?,  
                    apm = ?,  
                    pm = ?                              
                WHERE id = ?
            ');

            foreach ($chunks[$i] as $field => $val) {               
                $update_project->bindValue($j++, $val, PDO::PARAM_STR);                                 
            }
            $update_project->execute();
        }   

        echo 'Projects Updated';        

    } catch(PDOException $e) {
        die($e->getMessage());
    }

如果我var_dump($chunks[1])我看到以下值

array(7) { [0]=> string(13) "some comments" [1]=> string(7) "jim doe" [2]=> string(6) "1-1-14" [3]=> string(7) "12-1-13" [4]=> string(8) "jane doe" [5]=> string(7) "jon doe" [6]=> string(2) "16" }    

那么我的代码中的问题出在哪里?感谢您的帮助

So where is the problem in my code? Any help is appreciated

推荐答案

是的，SQL参数从1开始编号(我不知道为什么该答案的所有者删除了它).

It's true, SQL parameters start numbering from 1 (I don't know why the owner of that answer deleted it).

参数编号是在SQL/CLI标准中定义的，该标准可以追溯到1980年代，当时发明了零号. ;-)

The parameter numbering is defined in the SQL/CLI standard, which dates back to the 1980's, before the number zero was invented. ;-)

至于为什么您的代码没有更新，我希望确保id值位于您期望的位置.反转并分块数组后，如果id值未在正确的位置结束，则它可能会尝试更新行，但不匹配任何行.

As for why your code isn't updating, I'd look to make sure the id values are positioned where you expect them. After reversing and chunking the array, if the id value doesn't end up in the right spot, it might try to update rows, but match none.

这是编写此例程的另一种方法:

Here's an alternative way to code this routine:

$backwards = array_reverse($update);
$chunks = array_chunk($backwards, 7);

var_dump($chunks[1]);       

try {
    $update_project = $db->prepare('
        UPDATE projects
        SET comments = ?,
            contact = ?,
            est_end = ?,
            est_start = ?,  
            apm = ?,  
            pm = ?                              
        WHERE id = ?
    ');
    $n = 0;
    foreach ($chunks as $chunk) {
        $update_project->execute($chunk);
        $n += $update_project->rowCount();
    }   

    echo 'Projects Updated, affected $n rows';        

} catch(PDOException $e) {
    die($e->getMessage());
}

这篇关于使用PDO更新具有多个数组的SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！