Problem with update_batch()

[eluser]JoeDRL[/eluser]
Hello guys,

I need to update a batch of data, I preformat an array wich is outputted like that when passed to var_export():

Code:

array ( 0 => array ( 'id' => '13', 'exercice_id' => '27', 'nb_series' => '6', 'ordre' => '1', ), 1 => array ( 'id' => '14', 'exercice_id' => '28', 'nb_series' => '6', 'ordre' => '2', ), 2 => array ( 'id' => '15', 'exercice_id' => '29', 'nb_series' => '6', 'ordre' => '3', ), 3 => array ( 'id' => '16', 'exercice_id' => '30', 'nb_series' => '6', 'ordre' => '4', ), 4 => array ( 'id' => '17', 'exercice_id' => '31', 'nb_series' => '6', 'ordre' => '5', ), 5 => array ( 'id' => '18', 'exercice_id' => '32', 'nb_series' => '4', 'ordre' => '6', ), 6 => array ( 'id' => '19', 'exercice_id' => '33', 'nb_series' => '6', 'ordre' => '7', ), 7 => array ( 'id' => '20', 'exercice_id' => '34', 'nb_series' => '6', 'ordre' => '8', ), 8 => array ( 'id' => '21', 'exercice_id' => '35', 'nb_series' => '3', 'ordre' => '9', ), )

Then I pass it to $this->db->update_batch() but it always raises a codeigniter database error. My array looks good right? I do a insert_batch with a similar array and it works fine.

My table, simplified, looks like : 'id', 'exercice_id', 'ordre', 'nb_series'.

Thanks you!

JoeDRL

[eluser]JoeDRL[/eluser]
Knowing that I only need to update about 15 rows at the time, and that in some cases I will need to insert or delete data instead of updating it, would looping on an update/insert function would bring huge performance problems?

It would solve the problem, and I don't know how I can do it differently to insert or delete with a "batch" function. The only thing is the performance issue that I'm not so sure about.

Thank you!

[eluser]Nick_MyShuitings[/eluser]
and it didn't occur to you to include the db error for us to troubleshoot off of?

[eluser]JoeDRL[/eluser]
Actually there was no error.
The message only said, in french, "Il y a eu une erreur avec la base de données." which can be translated to "A database error as occured". Then it says it's on the update_batch() function, no error message from MySQL in my case like habitually.

[eluser]Nick_MyShuitings[/eluser]
Hmm... well, in reality, the update batch is just going to loop through them and update them.. so you're not getting a performance gain in any way. If that works better for you logically to allow you to insert/delete/update based on XYZ logic, then go for it.

[eluser]JoeDRL[/eluser]
Isn't update_batch() doing a request looking like "INSERT... VALUES (x1, y1, z1), (x2, y2, z2), (x3, y3, z3)"
which is faster than looping on single INSERT?

Anyway as i said I only process like 10 rows at a time so it's not that important.

Thank you Nick

[eluser]Nick_MyShuitings[/eluser]
Check out this link: http://www.frihost.com/forums/vt-68316.html

But now you've piqued my curiosity about how CI rigs up the udpate batch... I'll prolly do some tests later... but unless they've discovered some dark magic, I think you'd have a hard time using update_batch when you have different where clauses.

insert_batch would work how you sent in your example... but update... not sure.

[eluser]JoeDRL[/eluser]
Yeah thanks for the link but they didn't find a solution
So the update_batch() is meant to update the same fields of a same table with the same values and only the id is different?
Yeah insert_batch worked like a charm.

Will do more research..

Thanks again man

[eluser]danmontgomery[/eluser]
Instead of wondering, you could look at the source to see what it's doing, and see that is does, in fact, form a case statement in support of separate where clauses for each update.

https://bitbucket.org/ellislab/codeignit...infomation

$this->db->update_batch();

Generates an update string based on the data you supply, and runs the query. You can either pass an
array or an object to the function. Here is an example using an array:

Code:

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title');

// Produces:  
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE  
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')

The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.

[eluser]Nick_MyShuitings[/eluser]
That is the exact kind of dark wizardry that I was looking to find... (I just fired up my dev box to check the source but all hail to noctrum for getting it first)

I hope they update the online user guide soon as it has the insert_batch documentation duplicated with the name update_batch... http://ellislab.com/codeigniter/user-gui...tml#update

I hang my head in shame for not taking my own advice and just opening the source on the spot.