We have been trying to find a way to update multiple records in a mysql database.
First we are trying to display several records into a form. The user will then be changing the order of the records by editing the display text field. When the form submits it is supposed to update all of the records and then displays the records that have been updated. (Our db connection string is in a header file.)
We have tried a few different variations but came across this tutorial. But it is not working either as the database is not being updated. If anyone can help we would really appreciate it. We are new at this and have been working on it for over a week!
<?php
//Initialize counter variables
$index = 0;
$index_count = 0;
echo "<form method=post action=$PHP_SELF>\n";
echo "<table>\n";
echo "<tr><td><b>Display</b></td><td><b>Member</b></td></tr>\n";
$sql = "SELECT * FROM tablename order by display ASC";
//execute the select statment
$result = mysql_query($sql, $db);
/*
Assuming we already have retrieved the records from the database into an array setting
$myrow = mysql_fetch_array(). The do...while loop assigns a value to the $xstr variable
by taking the name and concatenating the value of $index to the end starting with 0. So
the first time through the loop $SubmissionIDStr would have a value of SubmissionID0 the
next time through it would be SubmissionID1 and so forth.
*/
do {
$recidStr = recid.$index;
$displayStr = display.$index;
$memberStr = member.$index;
//This section would print the values onto the screen one record per row
printf("<tr><td><input type=hidden name=%s value=%s><input type=text name=%s value=%s></td>
<td><input type=text name=%s value=%s></td></tr>\n",
$recidStr, $row["recid"], $displayStr, $row["display"], $memberStr, $row["member"]);
//Increase counter values by 1 for each loop
$index++;
$index_count++;
} while ($row = mysql_fetch_array($result));
// I also had to create an index count to keep track of the total number of rows.
echo "<INPUT TYPE=hidden NAME=counter VALUE=$index_count>\n";
echo "<INPUT TYPE=submit></form>\n";
?>
<?php
//This loops through all the records that have been displayed on the page.
for ($index = 0; $index <= $counter; $index++) {
/*
This part sets a variable with the names we created in the first section.
We start with 0 and go until the number saved in the $index_count variable.
*/
$varrecid = 'recid'.$index;
$vardisplay = 'display'.$index;
$varmember = 'member'.$index;
/*
This is the variable variable section. We take the value that was assigned
to each name variable. For example the first time through the loop we are
at the record assigned with SubmissionID0. The value given to SubmissionID0
is set from the first section. We access this value by taking the variable
variable of what SubmissionID0 is.
*/
$recidvalue = $$varrecid;
$displayvalue = $$vardisplay;
$membervalue = $$varmember;
//Update the database
$sql2 = "UPDATE tablename SET display='$displayvalue' WHERE recid='$recidvalue'";
}
?>
