Update multiple records - MySQL / PHP

[PHTech]

I'm struggling to UPDATE multiple records in my MySQL database using ONE PHP page and form.

What I have done is, created a search wizard page displaying the filtered records. Now, that the records that is filtered is displayed, I want to UPDATE the filtered records.

I have a database with table: tbl_weeklymain
Fields for tbl_weeklymain:

MainID - Primary Key
DateMain - Different Dates
Complex - Main Complexes
Operation - Sub-divisions of each complex
Categories - Different categories corresponding with inputs
Budget - Static Info for the year
Actual - RECORDS FOR UPDATING - ALL Actual info currently default to '0'.

OK. There is currently 8060 records in the database, and now after the filter is done, it is displaying at most 8 records filtered according to the CATEGORIES field. So for each DATE, there is one COMPLEX, and for each complex there is one OPERATION, and for each operation there is 8 CATEGORIES. OK. . .

Now I want to update the filtered results. So for each CATEGORY I want to update the ACTUAL field for each CATEGORY on one record updating form. . .

ANY IDEAS how I will do it...? I am an amateur with coding, but can work it out if put with some descriptions...

Below is the code of my results page that I want to use for updating records.

Thanx in advance...

<?php require_once('Connections/conn_Database.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$updateSQL = sprintf("UPDATE tbl_weeklymain SET Actual=%s WHERE Budget=%s",
GetSQLValueString($_POST['Actual'], "int"),
GetSQLValueString($_POST['Budget'], "int"));

mysql_select_db($database_conn_Database, $conn_Database);
$Result1 = mysql_query($updateSQL, $conn_Database) or die(mysql_error());
}

$maxRows_rs_results = 10;
$pageNum_rs_results = 0;
if (isset($_GET['pageNum_rs_results'])) {
$pageNum_rs_results = $_GET['pageNum_rs_results'];
}
$startRow_rs_results = $pageNum_rs_results * $maxRows_rs_results;

$varOp_rs_results = "-1";
if (isset($_GET['select3'])) {
$varOp_rs_results = (get_magic_quotes_gpc()) ? $_GET['select3'] : addslashes($_GET['select3']);
}
$varCompl_rs_results = "-1";
if (isset($_GET['select2'])) {
$varCompl_rs_results = (get_magic_quotes_gpc()) ? $_GET['select2'] : addslashes($_GET['select2']);
}
$varDate_rs_results = "-1";
if (isset($_GET['select'])) {
$varDate_rs_results = (get_magic_quotes_gpc()) ? $_GET['select'] : addslashes($_GET['select']);
}
mysql_select_db($database_conn_Database, $conn_Database);
$query_rs_results = sprintf("SELECT DateMain, Complex, `Operation`, Categories, tbl_weeklymain.Budget, tbl_weeklymain.Actual FROM tbl_weeklymain WHERE DateMain = '%s' AND Complex = '%s' AND `Operation` = '%s'", $varDate_rs_results,$varCompl_rs_results,$varOp_rs_results);
$query_limit_rs_results = sprintf("%s LIMIT %d, %d", $query_rs_results, $startRow_rs_results, $maxRows_rs_results);
$rs_results = mysql_query($query_limit_rs_results, $conn_Database) or die(mysql_error());
$row_rs_results = mysql_fetch_assoc($rs_results);

if (isset($_GET['totalRows_rs_results'])) {
$totalRows_rs_results = $_GET['totalRows_rs_results'];
} else {
$all_rs_results = mysql_query($query_rs_results);
$totalRows_rs_results = mysql_num_rows($all_rs_results);
}
$totalPages_rs_results = ceil($totalRows_rs_results/$maxRows_rs_results)-1;
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>XCSA - Confirm Selection</title>
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, sans-serif;
font-size: 11px;
}
-->
</style></head>

<body>
<p>You have selected the following recordset for input / updating:</p>
<table width="900" border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td width="150"><div align="center"><strong>Date:</strong></div></td>
<td width="150"><div align="center"><strong>Complex:</strong></div></td>
<td width="150"><div align="center"><strong>Operation:</strong></div></td>
<td width="150"><div align="center"><strong>Categories:</strong></div></td>
<td width="150"><div align="center"><strong>Budget:</strong></div></td>
<td width="150"><div align="center"><strong>Actual:</strong></div></td>
</tr>
</table>
<?php do { ?>
<table border="1" align="center" cellpadding="2" cellspacing="2">
<tr>
<td width="150"><div align="center"><?php echo $row_rs_results['DateMain']; ?></div></td>
<td width="150"><div align="center"><?php echo $row_rs_results['Complex']; ?></div></td>
<td width="150"><div align="center"><?php echo $row_rs_results['Operation']; ?></div></td>
<td width="150"><div align="center"><?php echo $row_rs_results['Categories']; ?></div></td>
<td width="150"><div align="center"><?php echo $row_rs_results['Budget']; ?></div></td>
<td width="150"><div align="center"><?php echo $row_rs_results['Actual']; ?></div></td>
</tr>
</table>
<?php } while ($row_rs_results = mysql_fetch_assoc($rs_results)); ?>
<p>Displaying <strong><?php echo $totalRows_rs_results ?></strong> records.</p>

</body>
</html>
<?php
mysql_free_result($rs_results);
?>

 

[Deicide]

honestly i would seriously consider looking at a framework like Qcodo to help you with this project as alot of the nitty gritty db stuff is done for you in the framework and there are some really cool controls and things in it that would make your life alot easier. you can check it out at www.qcodo.com it is a free framework.