[RESOLVED] Help on updating multiple records of a table

[tnylsej]

Hi all..

I would like to update multiple records of a table in the database.

Part of my codes:

$arrTask = array("Category","Location","Hardware","Vendor","Production","Status","User","Privilege","Equipment","Log");

$arrRole = array($strCategory,$strLocation,$strHardware,$strVendor,$strProduction,$strStatus,$strUser,$strPrivilege,$strEquipment,$strLog);

$sql1 = "SELECT * FROM Privileges";

for ($i=0;$i<=10;$i++) {
		$sql2 = "UPDATE Privileges SET Role='".$arrRole[$i]."' WHERE Task='".$arrTask[$i]."'";
		if (mysql_query($sql2)) { ?>
			<script>
				alert('Privileges updated successfully.')
				window.location= "privileges.php"
			</script>
		<?php } else { ?>
			<script>
				alert('Unable to update privileges.')
				window.location = "privileges.php"
			</script>
		<?php }	
} ?>

It is unable to update. Is there anything wrong with my codes?

[Piranha]

If you have already connected to the database I can't see any problem with this code. To find problems you could try to echo $sql2 and see if it looks like you want it to.

[tnylsej]

hmm..

I shall paste the full codes here.. sry if my codes look very noob coz I've just learnt php.

Part of the codes for privileges.php (the form):

<?php
mysql_select_db($database_tsis, $tsis);
$query_Recordset2 = "SELECT * FROM `privileges` ORDER BY Description";
$Recordset2 = mysql_query($query_Recordset2, $tsis) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>
<div id="main">
     <fieldset><legend class="heading">Privileges</legend>
	 <form action="updatePrivileges.php" method="POST">
	 <table width="950" border="1">
       <tr>
         <th>&nbsp;</th>
         <th>Admin</th>
         <th>Engineer</th>
	   </tr>
       <?php do { ?>
        <tr>
          <td><b><?php echo $row_Recordset2['Description']; ?></b></td>
<?php $strRole = explode(",", $row_Recordset2['Role']); ?>
          <td width="300" align="center"><input name="<?php echo $row_Recordset2['Task'];?>[]" type="checkbox" value="Admin" <?php foreach ($strRole as $role) { if ($role == "Admin") { ?> checked <?php } } ?>/></td>
          <td width="300" align="center"><input name="<?php echo $row_Recordset2['Task'];?>[]" type="checkbox" value="Engineer" <?php foreach ($strRole as $role) { if ($role == "Engineer") { ?> checked <?php } } ?>/></td>
        </tr>
         <?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2)); ?>

   <tr>
     <td colspan="3" align="center"><input type="submit" value="Update"></td>
   </tr>
 </table>
 </form>
 </fieldset>
</div>
<?php include "footer.php"; ?>
<?= "<script>" ?>
<?php 
 if(isset($_GET['updatePrivileges']) && $_GET['updatePrivileges']=="false")
 	echo "alert('".$_SESSION['errorMessage']."')";
?>
<?= "</script>" ?>

updatePrivileges.php

<?php
if (!isset($_SESSION)) {
  session_start();
}

$conn = mysql_connect("localhost","root","");
mysql_select_db("tsg",$conn);
$category = $_POST['Category'];
$location = $_POST['Location'];
$hardware = $_POST['Hardware'];
$vendor = $_POST['Vendor'];
$production = $_POST['Production'];
$status = $_POST['Status'];
$user = $_POST['User'];
$privilege = $_POST['Privilege'];
$equipment = $_POST['Equipment'];
$log = $_POST['Log'];
$errorMessage = '';
$updatePrivileges = true;

$countCategory = count($category);
$countLocation = count($location);
$countHardware = count($hardware);
$countVendor = count($vendor);
$countProduction = count($production);
$countStatus = count($status);
$countUser = count($user);
$countPrivilege = count($privilege);
$countEquipment = count($equipment);
$countLog = count($log);

if ($countCategory == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Category ";
	$updatePrivileges = false;
} else {
	$strCategory = implode(",", $category);
}

if ($countLocation == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Location ";
	$updatePrivileges = false;
} else {
	$strLocation = implode(",", $location);
}	

if ($countHardware == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Hardware ";
	$updatePrivileges = false;
} else {
	$strHardware = implode(",", $hardware);
}

if ($countVendor == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Vendor ";
	$updatePrivileges = false;
} else {
	$strVendor = implode(",", $vendor);
}				

if ($countProduction == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Production ";
	$updatePrivileges = false;
} else {
	$strProduction = implode(",", $production);
}

if ($countStatus == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Status ";
	$updatePrivileges = false;
} else {
	$strStatus = implode(",", $status);
}	

if ($countUser == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for User ";
	$updatePrivileges = false;
} else {
	$strUser = implode(",", $user);
}				

if ($countPrivilege == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Privileges ";
	$updatePrivileges = false;
} else {
	$strPrivilege = implode(",", $privilege);
}	

if ($countEquipment == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Equipment ";
	$updatePrivileges = false;
} else {
	$strEquipment = implode(",", $equipment);
}

if ($countLog == 0) { 
	$errorMessage = $errorMessage."- You have not made a selection for Activity Log ";
	$updatePrivileges = false;
} else {
	$strLog = implode(",", $log);
}				

if ($updatePrivileges == true) {
	$arrTask = array("Category","Location","Hardware","Vendor","Production","Status","User","Privilege","Equipment","Log");
	$arrRole = array($strCategory,$strLocation,$strHardware,$strVendor,$strProduction,$strStatus,$strUser,$strPrivilege,$strEquipment,$strLog);

	$sql1 = "SELECT * FROM Privileges";

	for ($i=0;$i<=10;$i++) {
		$sql2 = "UPDATE Privileges SET Role='".$arrRole[$i]."' WHERE Task='".$arrTask[$i]."'";
		if (mysql_query($sql2)) { ?>
			<script>
				alert('Privileges updated successfully.')
				window.location = "privileges.php"
			</script>
		<?php } else { ?>
			<script>
				alert('Unable to update privileges.')
				window.location = "privileges.php"
			</script>
		<?php }	}					
} else {
	$_SESSION['errorMessage'] = $errorMessage;
	header("Location: privileges.php?updatePrivileges=false");
	exit;
}
?>

I know my codes are very lengthy.. haha =X

Anyway, I notice that only the first element in the array from the form is being passed to updatePrivileges.php.

May I know what's wrong with my codes?