ON DUPLICATE KEY on many rows


#1

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)

[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); [/php]

actually my code looks like this:

        [php]$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();

[/php]
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?


#2

In case the OP is still in need of help with this (do any of the ‘guest’ poster’s ever return), the error you are getting means that $sql is a false value because the ->prepare() call failed, most likely due to an sql syntax error. If you had error handling for all the database statements (enabling exceptions for the php database extension you are using is the easiest way of adding error handling), you would be getting the actual error information about what’s causing the ->prepare() call to fail.

Not sure why you posted this in the general database forum section, since you are using a mysql database, the only database that can be used with the php mysqli extension, but the VALUES() function used in the ON DUPLICATE KEY UPDATE … part of the query does not have a term for each value. It references the column name being used in the INSERT part of the query that you want to use the value from. That would be Sort, so the end of the sql query would just be -

ON DUPLICATE KEY UPDATE Sort=VALUES(Sort)