Ammer Posted June 10, 2010 Share Posted June 10, 2010 I put this code together to update multiple MySQL rows and columns. A big form is generated with input fields populated with product prices. You can change the pricing and press a submit button. The database is then updated successfully for multiple rows and multiple columns. My code if functioning successfully but I was wondering if any experts out there can tell me if this is the right way to do this or if there is a better way? Thanks in advance. <?php virtual('/MySQL-connection-file.php'); $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { foreach($_POST['record_id_one'] as $key=>$value){ $sql1=sprintf("UPDATE products SET ProductPrice='%s' WHERE products_id='%d'", mysql_real_escape_string($value) , $key); $result1=mysql_query($sql1); } foreach($_POST['record_id_two'] as $key=>$value){ $sql2=sprintf("UPDATE products SET ListPrice='%s' WHERE products_id='%d'", mysql_real_escape_string($value) , $key); $result2=mysql_query($sql2); } foreach($_POST['record_id_three'] as $key=>$value){ $sql3=sprintf("UPDATE products SET SalePrice='%s' WHERE products_id='%d'", mysql_real_escape_string($value) , $key); $result3=mysql_query($sql3); } foreach($_POST['record_id_four'] as $key=>$value){ $sql4=sprintf("UPDATE products SET ProductCost='%s' WHERE products_id='%d'", mysql_real_escape_string($value) , $key); $result4=mysql_query($sql4); } echo "Update Complete"; } mysql_select_db($database, $credentials); $query_result = "SELECT products_id, productcode, productmanufacturer, productname, ProductPrice, ListPrice, SalePrice, ProductCost FROM products"; $result = mysql_query($query_result, $credentials) or die(mysql_error()); $row_result = mysql_fetch_assoc($result); $totalRows_result = mysql_num_rows($result); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1"> <table border="1"> <tr> <td>Part Number</td> <td>Manufacturer</td> <td>Product</td> <td>Product Price</td> <td>List Price</td> <td>Sale Price</td> <td>Product Cost</td> </tr> <?php do { ?> <tr> <td><?php echo $row_result['productcode']; ?></td> <td><?php echo $row_result['productmanufacturer']; ?></td> <td><?php echo $row_result['productname']; ?></td> <td><input type="text" name="record_id_one[<?php echo $row_result['products_id']; ?>]" value="<?php echo $row_result['ProductPrice']; ?>" /></td> <td><input type="text" name="record_id_two[<?php echo $row_result['products_id']; ?>]" value="<?php echo $row_result['ListPrice']; ?>" /></td> <td><input type="text" name="record_id_three[<?php echo $row_result['products_id']; ?>]" value="<?php echo $row_result['SalePrice']; ?>" /></td> <td><input type="text" name="record_id_four[<?php echo $row_result['products_id']; ?>]" value="<?php echo $row_result['ProductCost']; ?>" /></td> </tr> <?php } while ($row_result = mysql_fetch_assoc($result)); ?> </table> <input type="hidden" name="MM_update" value="form1" /> <input type="submit" value="Update record" /> </form> </body> </html> <?php mysql_free_result($result); ?> Quote Link to comment Share on other sites More sharing options...
Mchl Posted June 10, 2010 Share Posted June 10, 2010 You can update many column using one UPDATE query. If your POST array uses same indexing for all columns you could do it like this: foreach($_POST['record_id_one'] as $key=>$value){ $sql1=sprintf("UPDATE products SET ProductPrice='%s', ListPrice='%s', SalePrice='%s', ProductCost='%s' WHERE products_id='%d'", mysql_real_escape_string($value), mysql_real_escape_string($_POST['record_id_two'][$key]), mysql_real_escape_string($_POST['record_id_three'][$key]), mysql_real_escape_string($_POST['record_id_four'][$key]), $key); } Quote Link to comment Share on other sites More sharing options...
Ammer Posted June 10, 2010 Author Share Posted June 10, 2010 You can update many column using one UPDATE query. If your POST array uses same indexing for all columns you could do it like this: foreach($_POST['record_id_one'] as $key=>$value){ $sql1=sprintf("UPDATE products SET ProductPrice='%s', ListPrice='%s', SalePrice='%s', ProductCost='%s' WHERE products_id='%d'", mysql_real_escape_string($value), mysql_real_escape_string($_POST['record_id_two'][$key]), mysql_real_escape_string($_POST['record_id_three'][$key]), mysql_real_escape_string($_POST['record_id_four'][$key]), $key); } Wow! Thank you for such a fast response!! I had to re add the $result1=mysql_query($sql1) under it for it to work, and it is perfect! I hope this bit of code is a good example/starting point for others. I searched on Google and never found any good examples. Thanks again for your help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.