How to update multiple rows in one query (using for loop)

Question

For example, I have two data on my table:

|   ID   |   Name   |   Age   |

---

|   1     |   Steve   |    25    |
|   2     |   Bob      |    28    |

When i updating one value (for example: change "Bob" to "George"), it changes all value. This is the result:
|   ID   |   Name   |   Age   |

---

|   1     |   George   |    28    |
|   2     |   George   |    28    |

How to updating multiple rows in one query? To collect values, I use for loop like this:

<?php
...
$id_array = $_POST['id'];
$name_array = $_POST['name'];
$age_array = $_POST['age'];

$id = array();
$name = array();
$age = array();

for ($i = 0; $i < count($id_array); $i++) {
//count($id_array) --> if I input 4 fields, count($id_array) = 4)

   $id[] = mysql_real_escape_string($id_array[$i]);
   $name[] = mysql_real_escape_string($name_array[$i]);
   $age[] = mysql_real_escape_string($age_array[$i]);                    
}
mysql_query("UPDATE member SET name = '$name', age = '$age' WHERE id = '$id'");
}
...
?>

Can you help me? Thank you.

Answer 1

Construct your query within the loop:

<?php
...
$id_array = $_POST['id'];
$name_array = $_POST['name'];
$age_array = $_POST['age'];

for ($i = 0; $i < count($id_array); $i++) {
//count($id_array) --> if I input 4 fields, count($id_array) = 4)

   $id = mysql_real_escape_string($id_array[$i]);
   $name = mysql_real_escape_string($name_array[$i]);
   $age = mysql_real_escape_string($age_array[$i]);

   $query .= "UPDATE member SET name = '$name', age = '$age' WHERE id = '$id';";
}

mysql_query($query);
}
...
?>

Hope that helps..!