ON DUPLICATE KEY to update multiple rows

[simolahansen]

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 ,,(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(,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?

[mac_gyver]

the php error is because the ->prepare() failed AND you have no error handling for your php mysqli statements, so your code continued to run after the point of the error and tried to use $sql that contains a boolean false, rather than a prepared statement object.

 

the easiest way of adding error handling for all your mysqli statements, is to use exceptions. there is information in the php.net documentation on how to enable exceptions for the mysqli extension.

 

once you have some error handling, you will find that you have a error the sql syntax. the ON DUPLICATE KEY UPDATE ... part of the query has term(s) in it for the column(s) being updated, not for each value. the ending of your sql statement should be - 

ON DUPLICATE KEY UPDATE Sort=VALUES(Sort)

the mysql VALUES() function is defined as VALUES(col_name) where the col_name is the INSERT column value to use.