CodeIgniter multiple update in the same query

Question

I have a problem when I want to update multiple records with the same query in CodeIgniter.

This is a snippet of my code:

$query = "UPDATE user_data SET name = 'Name1 Surname1' WHERE uid = 'n1s1'; UPDATE user_data SET name = 'Name2 Surname2' WHERE uid = 'n2s2'";
$this->db->query($query);

This returns false so the query does not executes with success.

What can I do to run this query in CodeIgniter?

You are not updating multiple records, you are trying to execute more than 1 query by terminating them with ;. Short answer is - you can't do that. Split that into 2 queries, wrap them with transaction block and that's it. — N.B.
– N.B., Commented Jun 22, 2017 at 9:00
Why not wrapping it in a transaction and make 2 queries (docs: codeigniter.com/user_guide/database/transactions.html ) — ka_lin
– ka_lin, Commented Jun 22, 2017 at 9:02
@ka_lin I am concerned about the execution time of those queries. There are about 1000 queries. Is it more performant than trying to do it with simple php (outside CodeIgniter)? — Endri
– Endri, Commented Jun 22, 2017 at 9:07
Two people told you about transaction. You didn't even ask what it means. No, it's not more performant to do what you want. What's more performant is to use prepared statements and transactions. You're not doing it nor asking what it even is. Yes, it's MUCH faster than to create a huge string and send it to MySQL. Yes, it's fast even if you have tens of thousands of insert / update queries. Don't believe me? Measure it. You'll be surprised. There's a reason why, but it takes too much to explain. — N.B.
– N.B., Commented Jun 22, 2017 at 9:16
If you're not a beginner, you'd know what autocommit is, that what you wanted to do would quickly overflow max_allowed_packed setting in MySQL, that prepared statements lex the query once, send the data through, avoid the parsing of SQL, avoid the possible max_allowed_packet and wrapping it in transaction would use 1 I/O to flush it to HDD, making an effective use of hard drive's bandwith. But, hey, we got caught up in this debate if you're a beginner or not. Knowing how to write a query doesn't make anyone an expert. I never called you a beginner btw. — N.B.
– N.B., Commented Jun 22, 2017 at 9:29

B. Desai · Accepted Answer · 2017-06-22 09:03:12Z

2

You can use transactions.

$this->db->trans_start();
$this->db->query("UPDATE user_data SET name = 'Name1 Surname1' WHERE uid = 'n1s1'");
$this->db->query("UPDATE user_data SET name = 'Name2 Surname2' WHERE uid = 'n2s2'");
$this->db->trans_complete();

answered Jun 22, 2017 at 9:03

B. Desai

16.4k5 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CodeIgniter multiple update in the same query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related