Update mutiple rows with different values.

[phpDave]

Hi,
I'm trying to update multiple rows with diferent values.
The amont of values depends on the number of people created by the user.
I created some code that works for only single row.
Can anyone help? I'm not sure how to do this.

<?php
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE status SET status=%s WHERE user_id=%s",
                       GetSQLValueString($_POST['status'], "text"),
                       GetSQLValueString($_POST['hiddenField2'], "int"));

  mysql_select_db($database_ConnAdmin, $ConnAdmin);
  $Result1 = mysql_query($updateSQL, $ConnAdmin) or die(mysql_error());

  $updateGoTo = "question.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_ConnAdmin, $ConnAdmin);
$query_Recordset1 = "SELECT * FROM status";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $ConnAdmin) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
?>
//
<div id="mainContent">
    <form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
      <table width="50%" border="1" cellspacing="5" cellpadding="5">
        <th>
        <td>Status</td>
        <td></td>
        <td>User Id</td>
        </th>
        <?php do { ?>
        <tr>
        <td><?php echo $row_Recordset1['status_id']; ?></td>
        <td><select name="status" id="status">
          <option value="Great">Great</option>
          <option value="Good">Good</option>
          <option value="Bad">Bad</option>
          </select>
          </td>
          <td><?php echo $row_Recordset1['status']; ?></td>
          <td><?php echo $row_Recordset1['user_id']; ?>
          <input name="hiddenField" type="hidden" id="hiddenField" value="<?php echo $row_Recordset1['status_id']; ?>" />
          <input name="hiddenField2" type="hidden" id="hiddenField2" value="<?php echo $row_Recordset1['user_id']; ?>" /></td>
          <td><?php echo $row_Recordset1['status']; ?></td>
        </tr>
        <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
      </table>
      <p>
        <input type="submit" name="btn_1" id="btn_1" value="Submit" />
      </p>
      <input type="hidden" name="MM_update" value="form1" />
    </form>
    <h1>&nbsp;</h1>
    <!-- end #mainContent --></div>

Can anyone help? I'm not sure how to do this.

Read this (both links have a code and query that can update multiple-rows-with-different-values):

http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

http://stuporglue.org/update-multiple-rows-at-once-with-different-values-in-mysql/

[Webville312]

Use the foreach loop. For instance, if this is the code for fetching the user id;

<input type="text" name="user_id[]" size="10" />

Then, to insert it into the database, use;

foreach($userId as $key=>$user_id) 
{
// Your update query
}

That should do ...

[phpDave]

Thanks,
I have been studying the foreach loop but still having trouble.
I tried but got errors.
This code works for one row. I was wondering if anyone can see whats wrong.

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

foreach($_POST as $key=>$_POST[]) {

$updateSQL = sprintf("UPDATE status SET status=%s WHERE user_id =4",
                      GetSQLValueString($_POST['status'], "text"));
}

[masterjiraya]

foreach($_POST as $key=>$_POST[]) // I cannot get it

I cannot get it why you have $_POST then assigns as $key then having a circular reference to an array of $_POST again ?
Let me tell you this.
You have a $_POST array then you assinged it as $key and then assigns it $_POST array. In these action, the previous elements of $_POST array will be replaced. This is called the circular referencing which is an erroneous techinique of programming

[phpDave]

Ok, I see what you are saying. My mistake. I tried this after failed attempts with errors.
I'm trying to get data from a drop down menu that's in a do while loop.
If there are 10 emplyees then 10 rows should be updated but I get errors.
Can this even be done?

[masterjiraya]

Dave can you give me a scenario of this. for example your going to tell me what your program will do regarding this matter.

the reason:
as I can see you may have some sort of array injection functions that you should do. you may not need to do some do while if ever you explained it to me correctly and step by step.