Database performance with looped update queries?

I am currently structuring my queries for a database update model with CodeIgniter.

I getting the form posted input keys with $keys = array_keys($_POST)

To update according database fields I was wanting to do something like this

foreach($keys as $key){
    $data = $this->input->post($key);
 开发者_StackOverflow   $this->db->query("Update $table SET '$key'='$data' WHERE user_id='$the_user->id'");  }

(this is not production code, just trying to illustrate my question, hence no escaping, validation, etc...)

My question is, does this pose a performace problem by running a seperate query for each individual piece of form data submitted?

EDIT: sorry didn't intend for it to sound like a yes or no question, obviously if yes or no, the answer is yes, but I am looking for more of to what degree does it hamper the performance?

Yes.

You're issuing multiple updates, rather than one. The performance impact is less likely to be in your php loop, and more in the database connection; thus minimizing the amount of transactions between your application and the database will improve performances.

You can try:

1. build your SQL string before submitting:

   UPDATE <table> SET key1=data1, key2=data2, key3=data3 WHERE user_id=<user>
   

2. executing multiple updates in the same transaction:

   $this->db->query("
       UPDATE <table> SET key1=data1 WHERE user_id=<user>;
       UPDATE <table> SET key2=data2 WHERE user_id=<user>;
       UPDATE <table> SET key3=data3 WHERE user_id=<user>;"
   );
   

I'm not sure how PHP/MySQL handle the second option. Perl/Postgres can do it. Also, I would think that the first option would be faster.

Another thing to consider, if you're doing this for multiple users, is to use an IN (eg WHERE user_id IN ($user_list), where $user_list is a string of users in the format 'user1','user2','user3').