multiple update with text boxes

[njagi]

I have this code to update the DB by subitting values in the checked columns
The problem is that it skips some entries and does not always subit the values entered

SQL Query

$thisid=$_POST['id'];
$nvalue = $_POST['newvalue'];
$chkd = $_POST['checked'];
foreach ($chkd as $key => $value) {

$sql="update invoice_subcategory set

Item_Cost='$nvalue[$value]' 
where ID='$value'";

$query=mysql_query($sql,$db);
}

html form

<?php $query = "SELECT * from invoice_subcategory ORDER BY Cat_ID ASC ";

$result_costs = mysql_query($query) ; 

while($costs = mysql_fetch_object($result_costs)){

  $is_name = $costs->SubCategory;

  $is_cost = $costs->Item_Cost;

  $id = $costs->ID;



               echo'<tr  bgcolor='.$table_background_body.'>

                          <td>'. $is_name.'</td>

                          <td>'. $is_cost.'</td>';
                          echo"<td><input type='text' name='newvalue[]'> </td>
                          <td><input type='checkbox' name='checked[]' value=$id ></td>

                        </tr>";

see screen shots below

[JeniF]

I am using a similar approach to a from I have where the admin must assign applications access to users. I have error checking in the form to avoid duplicates, but at least it should give you an idea of how I got it to work. I am using the checkboxes in an array and taking the value of the $_POST from the recordset values.

$AddData="no";
$MM_flag="MM_insert";
if((isset($_POST["insert"])) && (isset($_POST["MM_insert"])== "skills_access")) {
 
$AddData="yes";
$startWarning="no";
if (!$_POST['applications'] ) 
{ 
$AddData = "no";
$startWarning="yes";
?>
<div class="warningstart">Please complete the missing item(s) indicated. </div>
 
<?php
// if the applicaiton selection is missing - display error message
if (!isset($_POST['applications'])) { ?> 
<div class="warning"> 
<li>Select at least one Application</li></div>
<?php } ?>
<div class="warningend"></div> 
<?php
} 
//else
} 
mysql_select_db($database_mambo, $mambo);
$query_rsUserName = "SELECT * FROM mos_Corpusers ORDER BY username ASC";
$rsUserName = mysql_query($query_rsUserName, $mambo) or die(mysql_error());
$row_rsUserName = mysql_fetch_assoc($rsUserName);
$totalRows_rsUserName = mysql_num_rows($rsUserName);
mysql_select_db($database_mambo, $mambo);
$query_rsApps = "SELECT * FROM web_access_applications ORDER BY app_description ASC";
$rsApps = mysql_query($query_rsApps, $mambo) or die(mysql_error());
$row_rsApps = mysql_fetch_assoc($rsApps);
$totalRows_rsApps = mysql_num_rows($rsApps);
 
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
$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;
}
}
 
$FoundMatch="";
if ((array_key_exists('insert', $_POST)) && $AddData!="no") {
// #1 - check whether any applications have been selected and if so, loop through the selected applicaitions and build value pairs ready for insertion into the skill_access table
$applications = array();
$AppID = $_POST['applications'];
if (isset($_POST['applications'])) {
foreach($AppID as $web_app_id) {
$check = "SELECT * FROM skills_access WHERE skills_access.web_app_id='" . $web_app_id . "' and skills_access.mambo_user_id='" . $mambo_user_id . "' ";
mysql_select_db($database_mambo, $mambo);
$checkRS=mysql_query($check, $mambo) or die(mysql_error());
$FoundMatch = mysql_num_rows($checkRS);
if ((is_numeric($web_app_id)) && $FoundMatch==0) {
$insertApps = "INSERT INTO skills_access (web_app_id, mambo_user_id) VALUES ('$web_app_id', '$mambo_user_id')";
$result1 = mysql_query($insertApps) or die(mysql_error());
$insertGoTo = "index.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
else
{
mysql_select_db($database_mambo, $mambo);
$findmatch = "SELECT * FROM web_access_applications WHERE web_app_id='" . $web_app_id . "'";
$findmatchRS=mysql_query($findmatch, $mambo) or die(mysql_error());
$row_rsfindmatch = mysql_fetch_assoc($findmatchRS);
$find = mysql_num_rows($findmatchRS);
$findmambouser = "SELECT * FROM mos_Corpusers WHERE id='" . $mambo_user_id . "'";
$findmambouserRS=mysql_query($findmambouser, $mambo) or die(mysql_error());
$row_rsfindmambouser = mysql_fetch_assoc($findmambouserRS);
$foundmambouser = mysql_num_rows($findmambouserRS);
"</br>".$row_rsfindmatch['app_description']."-".$web_app_id."-".$row_rsfindmambouser['username']."-".$mambo_user_id."Match Found and Record was not inserted!</br>";
}
}
}
}
?>

this form allows for check all checkboxes or uncheck all check boxes using javascript so in the head tag place this code (if you want to allow use)

<script type="text/javascript">
var formblock;
var forminputs; 
function prepare() 
{ formblock= document.getElementById('skills_access'); <--your form name
forminputs = formblock.getElementsByTagName('input');
} 
function select_all(name, value) { for (i = 0; i < forminputs.length; i++) { 
// regex here to check name attribute 
var regex = new RegExp(name, "i"); 
if (regex.test(forminputs[i].getAttribute('name'))) 
{ 
if (value == '1') 
{ 
forminputs[i].checked = true; 
} 
else 
{ 
forminputs[i].checked = false; 
} 
} 
}
} 
if (window.addEventListener) 
{ window.addEventListener("load", prepare, false);
} 
else if (window.attachEvent) 
{ window.attachEvent("onload", prepare)
} else if (document.getElementById) 
{ window.onload = prepare;} 
</script>

now the form

<form id="skills_access" name="skills_access" method="POST" >
<?php
 
 
if (($_POST) && ($FoundMatch > 0) && $AddData!="no") {
echo "<b>Duplicate Record(s) found.<br />";
echo "Add aborted due to duplicate records.</b><br /><br />";
 
/////////
mysql_select_db($database_mambo, $mambo);
$query_rsUserInfo = "SELECT * from skills_access, mos_Corpusers, web_access_applications WHERE skills_access.mambo_user_id= mos_Corpusers.id AND skills_access.web_app_id=web_access_applications.web_app_id ";
$rsUserInfo = mysql_query($query_rsUserInfo, $mambo) or die(mysql_error());
$row_rsUserInfo = mysql_fetch_assoc($rsUserInfo);
$totalRows_rsUserInfo = mysql_num_rows($rsUserInfo);
$MamboUserName=$row_rsUserInfo['username']; 
/////////// 
///////////////
echo "<br/><br/>";
echo "Access to one of your selections was found during insert <br />";
echo "The access requested for the user was not entered. <br />";
echo "Please review the information below and re-enter the access selection <br />";
echo "<I>The information shown below indicates the users access to application(s) already assigned.</I><br/><br/>";
//$result = mysql_unbuffered_query("SELECT * from skills_access, mos_Corpusers, web_access_applications WHERE skills_access.mambo_user_id= mos_Corpusers.id AND skills_access.web_app_id=web_access_applications.web_app_id ");
$ID=$_POST['mambo_user_id'];
$username=$row_rsfindmambouser['username'];
$result = mysql_unbuffered_query("SELECT * from mos_Corpusers, web_access_applications WHERE mos_Corpusers.id= '$ID' and web_access_applications.web_app_id= '$web_app_id' ");
$set = array();
while ($record = mysql_fetch_object($result)) {
$set[$record->app_description][] = $record;
}
mysql_free_result($result);
foreach ($set as $app_description => $records) {
echo "<table>\n";
echo "<tr><th>{$username}</th> <th>{$app_description}</th><th></th></tr>\n";
foreach ($records as $record) {
// echo ($record);
}
echo "</table>\n";
}
//////////////// 
echo "<br/><br/>";
echo "<input type='button' name='OK' value='Return to List' onclick='window.history.go(-1)' />";
//}
// {
//else {
if (($_POST) && ($FoundMatch == 0) && $AddData!="no") {
echo"Access Added <br/><br/>";
echo "<input type='button' name='OK' value='Return to List' onClick=self.location='index.php' />";
}
}
//}
if ($AddData=="no"){
?>
<table width="579" border="0">
<tr>
<td colspan="2" valign="top" scope="row">&nbsp;</td>
</tr>
<tr>
<td width="83" valign="top" scope="row">UserName:</td>
<td width="486" valign="top"><select name="mambo_user_id" id="mambo_user_id">
<?php
do { 
?>
<option value="<?php echo $row_rsUserName['id']?>"><?php echo $row_rsUserName['username'] ?> - <?php echo $row_rsUserName['id']?></option>
<?php
} while ($row_rsUserName = mysql_fetch_assoc($rsUserName));
$rows = mysql_num_rows($rsUserName);
if($rows > 0) {
mysql_data_seek($rsUserName, 0);
$row_rsUserName = mysql_fetch_assoc($rsUserName);
}
?>
</select>
</td>
</tr>
<tr>
<td colspan="2" valign="top" scope="row">Select the Applications below that the user will have access to.</td>
</tr></table>
<table width="584">
<tr>
<td width="82" valign="top" scope="row"></td>
<td width="144" valign="top"> </td>
<td colspan="2" valign="top"> </td>
</tr>
<tr>
<td><span class="style7"><a href="#" onClick="select_all('applications', '1');">Check All</a><br />
<a href="#" onClick="select_all('applications', '0');">Uncheck All </a></span></td>
<td><label><strong>Application ID </strong></label></td>
<td width="342"><strong>Application Name </strong></td>
</tr>
 
 
<?php do { ?>
<tr>
<td><div align="center">
<input name="applications[]" type="checkbox" id="applications[]" value="<? echo $row_rsApps['web_app_id']; ?>" 
<?php 
$OK = isset($_POST['applications']) ? true : false;
 
if($OK && in_array($row_rsApps['web_app_id'], $_POST['applications'])) { 
?> checked="checked"
<?php } ?> 
/>
</div></td>
<td><?php echo $row_rsApps['web_app_id']; ?></td>
<td><?php echo $row_rsApps['app_description']; ?></td>
</tr>
<?php } while ($row_rsApps = mysql_fetch_assoc($rsApps)); ?>
<tr>
<td colspan="4"><label>
<div align="center">
<input type="submit" name="insert" value="Add Applications" id="insert"/>
 
&nbsp;<input name="Reset" type="reset" id="Reset" value="Reset" />&nbsp;<input type="button" name="Cancel" value="Cancel" onClick="self.location='index.php'" />
<input type="hidden" name="MM_insert" id="MM_insert" value="skills_access">
</div>
</label></td>
</tr>
</table>
<?php } 
?>
</form>

(There may be a bit more in this example than needed, but I was testing a few more things and have some additional stuff to add to it)
As I stated this is a simple example, but should get you started if nothing else.
Hope it helps!