Author Topic: ON DUPLICATE KEY on many rows  (Read 200 times)

Ola Hansen

  • Guest
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)

   
1
  INSERT INTO boka_homeworktasks (Id,SortVALUES (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 ,17ON 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:

           
1
2
3
4
5
6
7
8
9
10
11
  $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:

Quote
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?

PHP Help Forum

ON DUPLICATE KEY on many rows
« on: June 22, 2017, 01:39:09 am »



PHP Help Forum

Re: ON DUPLICATE KEY on many rows
« Reply #1 on: July 14, 2017, 01:51:11 pm »