首页 > php > 更新多行会出错,尝试ON DUPLICATE KEY

更新多行会出错,尝试ON DUPLICATE KEY (Updating multiple rows gives error, trying ON DUPLICATE KEY)

2017-06-24 phpsqlmariadb

问题

我让用户能够对自己的数据进行排序。可以说我每行都有多只动物,有自己的Uniqe Id。然后我让用户使用从1到x的数字对动物进行排序。所以要做到这一点,我必须更新我的sql表中的多行。我做了Idunqe。然后我尝试了这个sql语句(这是sql试图运行的,我的实际php代码低于此代码)

 INSERT INTO boka_homeworktasks (Id,Sort) VALUES (29 ,1),(38 ,2),(30 ,3),(31 ,4),(32 ,5),(33 ,6),(34 ,7),(35 ,8),(36 ,9),(37 ,10),(39 ,11),(40 ,12),(41 ,13),(42 ,14),(43 ,15),(44 ,16),(45 ,17) ON DUPLICATE KEY UPDATE Sort=VALUES(1),Sort=VALUES(2),Sort=VALUES(3),Sort=VALUES(4),Sort=VALUES(5),Sort=VALUES(6),Sort=VALUES(7),Sort=VALUES(8),Sort=VALUES(9),Sort=VALUES(10),Sort=VALUES(11),Sort=VALUES(12),Sort=VALUES(13),Sort=VALUES(14),Sort=VALUES(15),Sort=VALUES(16),Sort=VALUES(17); 

实际上我的代码看起来像这样:

        $arrsorter = $_POST["sorter"];
        for ($x=0;$x < count($arrsorter);$x++){
            $sql_sentence .= '(?,' . ($x + 1) . '),';
            array_push($arrparams, $arrsorter[$x] );
            $sql_sentence2 .= 'Sort=VALUES('. ($x + 1) .'),';
        }
        $sql_sentence = 'INSERT INTO boka_homeworktasks (Id,Sort) VALUES '. substr($sql_sentence,0,-1) .' ON DUPLICATE KEY UPDATE '. substr($sql_sentence2,0,-1) . ';' ;
        $sql = $conn->prepare($sql_sentence);
        $sql->bind_param(str_repeat("i", count($arrparams)), ...$arrparams);
        $sql->execute();

我得到这个错误:

致命错误:未捕获错误:在myfile.php中调用boolean上的成员函数bind_param():35堆栈跟踪:第35行的myfile.php中引发#0 {main}

35号线是 $sql->bind_param(str_repeat("i", count($arrparams)), ...$arrparams);

我看不出任何错误,你呢?为什么我不能更新我的桌子?

解决方法

正确的方法是:

        $arrsorter = $_POST["sorter"];
    for ($x=0;$x < count($arrsorter);$x++){
        $sql_sentence .= '(?,' . ($x + 1) . '),';
        array_push($arrparams, $arrsorter[$x] );
        $sql_sentence2 .= 'Sort=VALUES(Sort),'; //It should not be a value, but refer to a column!
    }
    $sql_sentence = 'INSERT INTO boka_homeworktasks (Id,Sort) VALUES '. substr($sql_sentence,0,-1) .' ON DUPLICATE KEY UPDATE '. substr($sql_sentence2,0,-1) . ';' ;
    $sql = $conn->prepare($sql_sentence);
    $sql->bind_param(str_repeat("i", count($arrparams)), ...$arrparams);
    $sql->execute();

我唯一改变的是:$sql_sentence2 .= 'Sort=VALUES(Sort),';

问题

Im making the user able to sort his own data. Lets say I have multiple animals in each row, with its own Uniqe Id. Then I let the user sort the animals with a number from 1 to x. So to do this I have to update multiple rows in my sql-table. I made Id uniqe. And then I tried this sql-sentence (this is what the sql is trying to run, my actual php-code is below this code)

 INSERT INTO boka_homeworktasks (Id,Sort) VALUES (29 ,1),(38 ,2),(30 ,3),(31 ,4),(32 ,5),(33 ,6),(34 ,7),(35 ,8),(36 ,9),(37 ,10),(39 ,11),(40 ,12),(41 ,13),(42 ,14),(43 ,15),(44 ,16),(45 ,17) ON DUPLICATE KEY UPDATE Sort=VALUES(1),Sort=VALUES(2),Sort=VALUES(3),Sort=VALUES(4),Sort=VALUES(5),Sort=VALUES(6),Sort=VALUES(7),Sort=VALUES(8),Sort=VALUES(9),Sort=VALUES(10),Sort=VALUES(11),Sort=VALUES(12),Sort=VALUES(13),Sort=VALUES(14),Sort=VALUES(15),Sort=VALUES(16),Sort=VALUES(17); 

actually my code looks like this:

        $arrsorter = $_POST["sorter"];
        for ($x=0;$x < count($arrsorter);$x++){
            $sql_sentence .= '(?,' . ($x + 1) . '),';
            array_push($arrparams, $arrsorter[$x] );
            $sql_sentence2 .= 'Sort=VALUES('. ($x + 1) .'),';
        }
        $sql_sentence = 'INSERT INTO boka_homeworktasks (Id,Sort) VALUES '. substr($sql_sentence,0,-1) .' ON DUPLICATE KEY UPDATE '. substr($sql_sentence2,0,-1) . ';' ;
        $sql = $conn->prepare($sql_sentence);
        $sql->bind_param(str_repeat("i", count($arrparams)), ...$arrparams);
        $sql->execute();

and I get this errors:

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in myfile.php:35 Stack trace: #0 {main} thrown in myfile.php on line 35

Line 35 is $sql->bind_param(str_repeat("i", count($arrparams)), ...$arrparams);

I cant see anything I have done wrong, can you? Why cant I update my table?

解决方法

The right way to do this is:

        $arrsorter = $_POST["sorter"];
    for ($x=0;$x < count($arrsorter);$x++){
        $sql_sentence .= '(?,' . ($x + 1) . '),';
        array_push($arrparams, $arrsorter[$x] );
        $sql_sentence2 .= 'Sort=VALUES(Sort),'; //It should not be a value, but refer to a column!
    }
    $sql_sentence = 'INSERT INTO boka_homeworktasks (Id,Sort) VALUES '. substr($sql_sentence,0,-1) .' ON DUPLICATE KEY UPDATE '. substr($sql_sentence2,0,-1) . ';' ;
    $sql = $conn->prepare($sql_sentence);
    $sql->bind_param(str_repeat("i", count($arrparams)), ...$arrparams);
    $sql->execute();

The only thing I changed was this:$sql_sentence2 .= 'Sort=VALUES(Sort),';

相似信息