Update multiple records with PHP

Report · Jul 01, 2011

Hey,

I want to update multiple rows with one click on the submit button.

Unfortunately, it doesn't work. What is wrong in the code?

<?php require_once('../Connections/ikon.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
}
return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

for ($j = 0, $len = count($_POST['id']); $j < $len; $j++) {
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "openingsuren_wijzigen")) {
$updateSQL = sprintf("UPDATE ikon_openingsuren SET dag=%s, voormiddag=%s, namiddag=%s WHERE id=%s",
                       GetSQLValueString($_POST['dag'] [$j], "text"),
                       GetSQLValueString($_POST['voormiddag'] [$j], "text"),
                       GetSQLValueString($_POST['namiddag'] [$j], "text"),
                       GetSQLValueString($_POST['id'] [$j], "int"));

mysql_select_db($database_ikon, $ikon);
$Result1 = mysql_query($updateSQL, $ikon) or die(mysql_error());
}
}

mysql_select_db($database_ikon, $ikon);
$query_rsWijzigOpeningsuren = "SELECT * FROM ikon_openingsuren";
$rsWijzigOpeningsuren = mysql_query($query_rsWijzigOpeningsuren, $ikon) or die(mysql_error());
$row_rsWijzigOpeningsuren = mysql_fetch_assoc($rsWijzigOpeningsuren);
$totalRows_rsWijzigOpeningsuren = mysql_num_rows($rsWijzigOpeningsuren);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title></title>
</head>
<body>
       <form action="<?php echo $editFormAction; ?>" method="POST" name="openingsuren_wijzigen" id="openingsuren_wijzigen">
       <input name="id[]" type="hidden" id="id" value="<?php echo $row_rsWijzigOpeningsuren['id']; ?>" />
      <table id="tbl_openingsuren">
        <thead>
        <tr>
          <th scope="col">Dag</th>
          <th scope="col">Voormiddag</th>
          <th scope="col">Namiddag en Avond</th>
        </tr>
        </thead>
        <tbody>
                          <?php do { ?>
                            <tr>
                              <td><input name="dag[]" type="text" id="dag" value="<?php echo $row_rsWijzigOpeningsuren['dag']; ?>" size="15" maxlength="15" /></td>
                              <td><input name="voormiddag[]" type="text" id="voormiddag" value="<?php echo $row_rsWijzigOpeningsuren['voormiddag']; ?>" size="15" maxlength="15" /></td>
                              <td><input name="namiddag[]" type="text" id="namiddag" value="<?php echo $row_rsWijzigOpeningsuren['namiddag']; ?>" size="15" maxlength="15" /></td>
                            </tr>
                            <?php } while ($row_rsWijzigOpeningsuren = mysql_fetch_assoc($rsWijzigOpeningsuren)); ?>
                        </tbody>
      </table>
         <p><input type="submit" name="submit" id="submit" value="Wijzigen" class="knop" /></p>
         <input type="hidden" name="MM_update" value="openingsuren_wijzigen" />
                        </form>
</body>
</html>
<?php
mysql_free_result($rsWijzigOpeningsuren);
?>

Report · Jul 01, 2011

PowerMilk wrote:
I want to update multiple rows with one click on the submit button.
Unfortunately, it doesn't work. What is wrong in the code?

It would be more helpful if you were to say what happens. "It doesn't work" isn't very helpful.

However, a quick look at your code reveals that your hidden field that contains the ID is outside the loop (repeat region) in your form. Move it inside the loop like this:

<?php do { ?>
<tr>
   <td><input name="dag[]" type="text" value="<?php 
echo $row_rsWijzigOpeningsuren['dag']; ?>" size="15" maxlength="15" 
/></td>
   <td><input name="voormiddag[]" type="text" value="<?php echo 
$row_rsWijzigOpeningsuren['voormiddag']; ?>" size="15" maxlength="15"
 /></td>
   <td><input name="namiddag[]" type="text" value="<?php echo 
$row_rsWijzigOpeningsuren['namiddag']; ?>" size="15" maxlength="15" 
/></td>
</tr>
<input name="id[]" type="hidden" value="<?php echo $row_rsWijzigOpeningsuren['id']; ?>" />
<?php } while ($row_rsWijzigOpeningsuren = mysql_fetch_assoc($rsWijzigOpeningsuren)); ?>

Report · Jul 01, 2011

Thank you very much. It works.

Update multiple records with PHP

1 Correct answer