Construct your query within the loop:
<?php
...
$id_array = $_POST['id'];
$name_array = $_POST['name'];
$age_array = $_POST['age'];
for (
i < count($id_array); $i++) {
//count($id_array) --> if I input 4 fields, count($id_array) = 4)
$id = mysql_real_escape_string($id_array[
name = mysql_real_escape_string($name_array[
age = mysql_real_escape_string($age_array[
query .= "UPDATE member SET name = '$name', age = '$age' WHERE id = '$id';";
}
mysql_query($query);
}
...
?>
Hope that helps..!
Answer from BizzyBob on Stack Overflow Top answer 1 of 2
3
Construct your query within the loop:
<?php
...
$id_array = $_POST['id'];
$name_array = $_POST['name'];
$age_array = $_POST['age'];
for (i < count($id_array); $i++) {
//count($id_array) --> if I input 4 fields, count($id_array) = 4)
$id = mysql_real_escape_string($id_array[name = mysql_real_escape_string($name_array[age = mysql_real_escape_string($age_array[query .= "UPDATE member SET name = '$name', age = '$age' WHERE id = '$id';";
}
mysql_query($query);
}
...
?>
Hope that helps..!
2 of 2
0
Answer to your Question
MySQL updates all rows matching the WHERE clause, so to update multiple rows with the same value, you should use a condition matching all rows. To update all rows, dont set any where clause.
To update multiple rows with different values, you can't, use several queries.
Answer to your issue
In your code, 
name and $age are arrays so you can not use it in a string, this will not work. You should do the update in your FOR loop.
I advise you to try to respect resource oriented principe that all properties are assigned to their item (with associative array or object).
If you dont check the result, you could do all queries in one using a semi-colon.
PHPpot
phppot.com › php › updatedelete-multiple-rows-using-php
Update/Delete Multiple Rows using PHP - PHPpot
July 14, 2022 - So, on submitting this multiple user information, an array of user information for each user will be passed to the PHP code. By iterating over this multidimensional array, each user record will be updated by setting current iteration values to the MySQL UPDATE query.
sql - Multiple Updates in MySQL - Stack Overflow
I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name id Col1 Col2 Row1 ... More on stackoverflow.com
mysql - Update Multiple Rows at Once in PHP - Stack Overflow
I want to display table of student in "Class 1". User can change the information and update it into database. I want to update multiple rows at a time, but it ends up updating only 1 row (the last ... More on stackoverflow.com
Updating multiple MYSQL rows with one submit button - PHP - SitePoint Forums | Web Development & Design Community
My problem is similar to here: PHP Questions and Answers - Page 750 - Bytes … -php-mysql except I changed my code to have one button at the top instead of one at the end of every row, to make things easier for the users. I take values from a MYSQL table and print them into an HTML table using ... More on sitepoint.com
0
April 4, 2011mysql - php update multi rows at once - Stack Overflow
I have form with multi rows. It has select menu with ID of the row not used More on stackoverflow.com
Videos
13:54
Update or delete multiple rows using check box | Library Management ...
08:37
Update Multiple Selected Records using PHP Mysql - YouTube
33:13
Update Multiple Mysql Data using Checkbox with Ajax in PHP - YouTube
06:36
PHP Using a For Loop to do a Multi Row Insert into Mysql DB - YouTube
03:19
Updating Multiple Database Records - YouTube
30:29
update & delete multiple records PHP mysql tutorial [ENG] - YouTube
Student Tutorial
studentstutorial.com › php › php-update-multiple-row
How to Update Multiple Row In PHP
<?php $host="localhost"; /* Host name */ $username="root"; /* Mysql username */ $password=""; /* Mysql password */ $db_name="test"; /* Database name */ $tbl_name="test"; /* Table name */ /* Connect to server and select databse. */ mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); /* Count table rows */ $count=mysql_num_rows($result); ?> <?php /* Check if button name "Submit" is active, do this */ if(isset($_POST['Submit'])) { $count=count($_POST["id"]); for($i=0;$i<$count;$i++){ $sql1="UPDATE $tbl_name SET name='" .
Top answer 1 of 16
729
Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.
Using your example:
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
2 of 16
147
Since you have dynamic values, you need to use an IF or CASE for the columns to be updated. It gets kinda ugly, but it should work.
Using your example, you could do it like:
UPDATE table SET Col1 = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 4 THEN 10
ELSE Col1
END,
Col2 = CASE id
WHEN 3 THEN 3
WHEN 4 THEN 12
ELSE Col2
END
WHERE id IN (1, 2, 3, 4);
Top answer 1 of 3
5
You need to use input arrays. So your inputs should look like this instead (notice the name attribute):
echo "<td>Sex :<input type='text' name='name[]' value='".$row['name']."' /></td>";
Then in PHP $_POST['name'] will be an array:
foreach($_POST['name'] as $name){
Hope this helps...
2 of 3
3
You should create your sql dynamicly and execute the latest sql sentence. You can use a foreach loop to check all ids and simply append each update statement to a string variable. You can find an example here: http://www.youtube.com/watch?v=ImirOX73atc
SitePoint
sitepoint.com › php
Updating multiple MYSQL rows with one submit button - PHP - SitePoint Forums | Web Development & Design Community
April 4, 2011 - My problem is similar to here: PHP Questions and Answers - Page 750 - Bytes … -php-mysql except I changed my code to have one button at the top instead of one at the end of every row, to make things easier for the users. I take values from a MYSQL table and print them into an HTML table using PHP, in a while loop.
Stack Overflow
stackoverflow.com › questions › 40446476 › php-update-multi-rows-at-once
mysql - php update multi rows at once - Stack Overflow
It has select menu with ID of the row · <select name="TYPE[700205]"> <option value="">not used</option> <option value="1">enter</option> <option value="0">exit</option> </select> <select name="TYPE[700206]"> <option value="">not used</option> <option value="1">enter</option> <option value="0">exit</option> </select> ... $TYPE = $_POST['TYPE']; foreach($TYPE as $key=>$value){ // die(var_dump($TYPE));// // echo "key ".$key. "value " . $value; $sql= ("UPDATE NEWPER.WORK_DAYS_LOG set IN_OUT=:VALUE WHERE LOG_ID=:ID "); $rs = $this->db->Query($sql,array('ID'=>$key,'VALUE'=>$value)); }
Stack Overflow
stackoverflow.com › questions › 18929978 › how-to-update-multiple-rows-in-php
mysql - how to update multiple rows in php - Stack Overflow
April 15, 2016 - extract($_POST); if (isset($Submit)) { for($i=0;$i<$count;$i++) { $update=("UPDATE tbl_name SET name='$name[$i]', lname='$lastname[$i]',email='$email[$i]' WHERE id='$id[$i]'"); $res=mysql_query($update); } } if(isset($res)) { header("location:try.php"); } ?> <?php while($rows=mysql_fetch_array($result)){ ?> Share · Improve this answer · Follow · answered Sep 21, 2013 at 7:28 ·
Stack Overflow
stackoverflow.com › questions › 22158726 › update-multiple-rows-at-one-time-in-php
mysql - Update Multiple rows at one time in PHP - Stack Overflow
If you're updating hours WHERE formid equals something then it's going to update all the rows where formid is equal to some value. Is formid a unique value? ... Actually, id is the unique value - I updated my query to $stmt = $mysqli->prepare("UPDATE work_hours SET hours = ?
Etutorialspoint
etutorialspoint.com › index.php › mysql-exercises › mysql-update-multiple-rows-in-one-query
MySQL update multiple rows in one query
The best way to update multiple rows in just one statement is use CASE WHEN ELSE statement. In this, the statement will update the matched case and end otherwise, like- UPDATE 'table_name' SET 'field_name' = CASE 'id' WHEN '1' THEN 'value 1' WHEN '2' THEN 'value 2' WHEN '3' THEN 'value 3' ELSE ...
Post.Byes
post.bytes.com › home › forum › topic › php
How to update multiple rows (php/mysql) for little league database - Post.Byes
if (page request is from submitting a form) { Go through and validate $_POST and update MySQL table with values Set $message = "Table successfully updated"; } Call data from MySQL table Print HTML and make the form submit to the current page if (isset($message)) { echo $message; } That way, there is no header changes, your update code only runs if a form was submitted, but the rest of the page loads the same all the time. Also all of your PHP is at the top, and all your HTML is at the bottom - which is a good practice to get into.
Edureka Community
edureka.co › home › community › categories › web development › php › mysql - update multiple rows with different...
MySQL - UPDATE multiple rows with different values in one query | Edureka Community
June 1, 2020 - MySQL - UPDATE multiple rows with different...
Makitweb
makitweb.com › home › php › update multiple selected records with php
Update Multiple Selected Records with PHP - Makitweb
November 13, 2022 - <div class='container'> <!-- Form --> <form method='post' action=''> <!-- Submit button --> <input type='submit' value='Update Selected Records' name='but_update'><br><br> <!-- Record list --> <table border='1' style='border-collapse: collapse;' > <tr style='background: whitesmoke;'> <!-- Check/Uncheck All--> <th><input type='checkbox' id='checkAll' > Check</th> <th>Username</th> <th>First Name</th> <th>Last Name</th> <th>Age</th> <th>Salary</th> <th>Email</th> </tr> <?php $query = "SELECT * FROM users"; $result = mysqli_query($con,$query); while($row = mysqli_fetch_array($result) ){ $id = $ro
Top answer 1 of 2
1
You could use VALUES to get the new value you are using in the update portion. Also, if you use prepare and bind_param you will prevent SQL injection:
$mysqli = new mysqli('host', 'user', 'password', 'db');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$stmt = $mysqli->prepare("INSERT INTO content(name, fieldcontent)
VALUES ('home_title', ?), ('home_text', ?)
ON DUPLICATE KEY UPDATE fieldcontent = VALUES(fieldcontent)");
$stmt->bind_param('ss', $home_title, $home_text);
$stmt->execute();
2 of 2
0
Your second SQL statement is being added to your first creating one long statement that doesn't make sense. Separate these into two different statements.
T4Tutorials
t4tutorials.com › update-and-delete-multiple-records-together-in-php-mysql
Update and Delete multiple records together in PHP MYSQL – T4Tutorials.com
Delete Multiple records together in PHP MySQL · connection.php · PHP · UpdateMultiple.php · PHP · DeleteMultiple.php · PHP · Database view in PHP Myadmin · Figure: delete update multiple rows together in PHP MY SQL · Delete and Update Multiple Rows Using PHP ·
PHP Freaks
forums.phpfreaks.com › php coding › php coding help
Update multiple records in PHP / MYSQL - PHP Coding Help - PHP Freaks
June 29, 2023 - Hi there, I am trying to update a multiple rows for a single column and the code that I have wrote runs through fine and does not show any error messages but does not update the rows or column.
Medium
medium.com › @yssyogesh94 › update-multiple-selected-records-with-php-ab24a9e9c9b9
Update Multiple Selected Records with PHP | by Yogesh Singh | Medium
August 4, 2018 - if(isset($_POST['but_update'])){ if(isset($_POST['update'])){ foreach($_POST['update'] as $updateid){ $fname = $_POST['fname_'.$updateid]; $lname = $_POST['lname_'.$updateid]; $age = $_POST['age_'.$updateid]; $salary = $_POST['salary_'.$updateid]; $email = $_POST['email_'.$updateid]; if($fname !='' && $lname !='' && $age >0 && $salary >0 && $email != '' ){ $updateUser = "UPDATE users SET fname='".$fname."',lname='".$lname."', age=".$age.",salary=".$salary.",email='".$email."' WHERE id=".$updateid; mysqli_query($con,$updateUser); } } } } For multiple records, selection use checkbox elements and assign record id in the value attribute for accessing it on form submit for select and update records.
Reddit
reddit.com › r/phphelp › mysql - how can i insert multiple rows with insert on update
r/PHPhelp on Reddit: MySql - How can I insert multiple rows with INSERT ON UPDATE
August 31, 2024 -
Hello,
Been stuck on this one for a while.
Using Mysqli, how can I insert (or update if the key already exists) multiple rows in one transaction?
I would like to loop over this array and execute the query only once, instead of on each iteration of the loop
foreach($challengeData as $challengeId => $status) {
$this->conn->execute_query('
INSERT INTO my_table
(
challenge_id,
status
)
VALUES
(
?,?
)
ON DUPLICATE KEY UPDATE
status = VALUES(status)
',
[
$challengeId,
$status
]
);
} Top answer 1 of 4
5
$values = ""; $data = []; foreach($challengeData as $row) { $values .= $values ? "," : ""; // no comma before first block $values .= "(?,?)"; $data[] = $challengeId; $data[] = $status; } $sql = "INSERT INTO my_table (challenge_id, status) VALUES $values ON DUPLICATE KEY UPDATE status = VALUES(status)"; $this->conn->execute_query($sql, $data);
2 of 4
2
In MySQL you can insert multiple rows in one statement, but for your case where you wish to update the row if it exists this may not be suitable. Note that using ON DUPLICATE KEY UPDATE will increment your auto counter even if a row is not inserted (i.e. updated), it may be better to look up the row first, then either update or insert. If performance is your concern, make sure to prepare the statement once, and then execute multiple times, this will limit the impact of sending multiple queries. To make the operations atomic, start a transaction before your update loop and commit it afterwards.
DaniWeb
daniweb.com › programming › web-development › threads › 378187 › update-multiple-rows-in-mysql-5-using-php
update multiple rows in mysql 5 using php | DaniWeb
August 19, 2011 - Post the primary keys back with each row. Second, do not rely on the pre-submit $count; iterate over what was actually posted. Also, you echo output before calling header(), which can prevent the redirect and hide errors. Put the update logic at the top of the script, before any HTML. See the compact pattern below. <?php if (!empty($_POST['id'])) { mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $db = new mysqli('localhost','***','***','***'); $db->set_charset('utf8mb4'); $db->begin_transaction(); $stmt = $db->prepare('UPDATE competition SET `dates`=?, `day`=?, `type`=? WHERE `id`=?'); foreach ($_POST['id'] as $i => $id) { $stmt->bind_param('sssi', $_POST['dates'][$i], $_POST['day'][$i], $_POST['type'][$i], (int)$id); $stmt->execute(); } $db->commit(); header('Location: update_multiple.php'); exit; } ?>
SitePoint
sitepoint.com › php
How to update multiple rows in mysql with php - PHP - SitePoint Forums | Web Development & Design Community
August 30, 2014 - Hi I’m trying to get this script to work, it almost works but doesn’t update ALL the fields after you click submit. Two fields don’t display at all (the ones that don’t update) . One field isn’t suppose to update but should display i.e. record_number, the other field i.e. Avalibity, ...