Multiple rows update

aarontan78

Hi,

I have this page that when user make a selection, the page will show multiple records in table form.
I insert a text box in each of the record displayed for user to edit the field.
My problem is that when the user clicks SUBMIT button, it updates all the field to 0.
Please help!

Regards,
Aaron

<html>
<head>
<title>Competition Order</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">

<?php
include("connect.inc");

if($submit){
foreach ($_POST[EVENT_PARTICIPANT_ID] as $id => $EVENT_PARTICIPANT_ID) {

$EVENT_ORDER = $_POST[EVENT_ORDER][$id];

$sql1 = "UPDATE EVENT_PARTICIPANTS SET
EVENT_ORDER = ".$EVENT_ORDER."
WHERE EVENT_PARTICIPANT_ID =".$EVENT_PARTICIPANT_ID;
$result = mysql_query($sql1);
}
echo "dhseh";

}

?>
<form method="POST" action="comp_order.php">
<table align="center">
<tr><td><b>Event</b></td>
<td><select size="1" name="EVENT_ID">
<option selected></option>
<option value="1">CQ</option>
<option value="2">NQ</option>
</select></td><td></td><td></td>
<td><b>Sex</b></td><td><select size="1" name="GENDER">
<option selected></option>
<option>M</option>
<option>F</option>
</select></td>
</table>
<p align="center"><input type="submit" value="Search"><input type="reset" value="Reset"></p>

<?php

if(($EVENT_ID) && ($GENDER)) {

$query3 = "SELECT B.EVENT_ORDER, A.ATHLETE_NAME, A.ATHLETE_STATE, B.EVENT_PARTICIPANT_ID, A.ATHLETE_CNAME
FROM ATHLETES A, EVENT_PARTICIPANTS B
WHERE B.PARTICIPANT_ID = A.ATHLETE_ID
AND B.EVENT_ID = ".$EVENT_ID."
AND A.ATHLETE_GENDER = '".$GENDER."'
ORDER BY B.EVENT_ORDER";

$result = mysql_query($query3);

$query4 = "SELECT EVENT_DESC
FROM EVENTS
WHERE EVENT_ID = ".$EVENT_ID;

$result1 = mysql_query($query4);
$EVENT_NAME = mysql_result($result1,0);

echo " <table width=\"550\" align=\"center\" cellpadding=\"1\" cellspacing=\"3\">";
echo " <td colspan=\"6\" bgcolor=\"#dddddd\"><div align=\"center\"><b>".$EVENT_NAME." ($GENDER)"."</b></div></td></tr>";
echo " <tr bgcolor=\"#DDDDDD\">";
echo " <td width=\"150\"><center><b>Order</b></center></td>\n";
echo " <td width=\"150\"><center><b></b></center></td>\n";
echo " <td width=\"300\"><center><b>Competitor Name</b></center></td>\n";
echo " <td width=\"150\"><center><b>State</b></center></td>\n";
echo " </tr>\n";

while ($row=mysql_fetch_array($result))
{
echo "<tr bgcolor=\"#EEEEEE\">\n";
echo "<td><input type=\"text\" name=\"EVENT_ORDER[id]\" size=\"5\" value=\"$row[0]\" ></td>\n";
echo "<td>$row[4]</td>\n";
echo "<td>$row[1]</td>\n";
echo "<td>$row[2]</td>\n";
echo "<input type=hidden name=\"EVENT_PARTICIPANT_ID[".$row[EVENT_PARTICIPANT_ID]."]\" value=\"$row[EVENT_PARTICIPANT_ID]\">";
echo "</tr>\n";
}
echo "</table>\n";
}
echo "<p align=\"center\"><input type=\"submit\" name=\"submit\" value=\"Submit\"></p>";
?>
</form>
</body>
</html>

mungra

seems that you are making multiple queries at the same time. Try to freee each resultset

before making another query.

try that!!

johany

Have you tried

$sql1 = "UPDATE EVENT_PARTICIPANTS SET
EVENT_ORDER = '$EVENT_ORDER'
WHERE EVENT_PARTICIPANT_ID =".$EVENT_PARTICIPANT_ID;
$result = mysql_query($sql1);

You should try not to write everything in CAPs. It makes it very hard to read.
Try to keep your variables small and your constants in caps.

$sql = "UPDATE table SET type_field = '$var'
                           WHERE type_field =".$int_var;"
$result = mysql_query($sql);