Jump to content

Updating multiple MySQL rows and columns. Is there a better way?


Ammer

Recommended Posts

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);
?>

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);
}

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.