Updating multiple tables

Bowers007

I need to be able to update multiple tables in a training database. Here is what I have done so far:
1. build a page to select the employee to update (TrainingUpdate.html)
2. after you select an employee you go to a page that lists and populates the current records in the database for that employee.(TrainingUpdate.php)
Here is my code so far:
TrainingUpdate.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<title>Update Existing Employee Training Records</title>

<link rel="stylesheet" type="text/css" href="TrainingDataBase.css">
<link rel="shortcut icon" href="../images/favicon.ico" type="image/x-icon" />

</head>

<body>

<br>
<br>

<div id="container">

<div align="center">
<p>
<h1 align="center"><u><strong><font face="arial">Update Existing Employee Training Records</font></strong></u></h1>
</p>
</div> 

<br>

<div id="select">

<br>
<br>

<div align="center">
<?php
foreach($HTTP_POST_VARS as $varname => $value)
$formVars[$varname]=$value;

require_once("config.php");

$db1=mysql_connect($dbhost, $dbuname, $dbpass);
mysql_select_db("training_db");

$query="SELECT * FROM employee WHERE Emp_Id = \"".$formVars["Emp_Id"]."\"";

$result=mysql_query($query);
$row=mysql_fetch_array($result);
$formVars = array();

$formVars["FirstName"]=$row["FirstName"];
$formVars["LastName"]=$row["LastName"];
$formVars["Dept"]=$row["Dept"];
$formVars["Shift"]=$row["Shift"];

mysql_close($db1);
?>

<div align="center">

<form method="post" action="postupdate1.php">

<table>

<col span="1" align="right">

<tr>
<td><font color="red">First Name:</font></td>
<td><input type="text" name="FirstName" 
value="<? echo $formVars["FirstName"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Last Name:</font></td>
<td><input type="text" name="LastName" 
value="<? echo $formVars["LastName"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Department:</font></td>
<td><input type="text" name="Dept" 
value="<? echo $formVars["Dept"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Shift:</font></td>
<td><input type="text" name="Dept" 
value="<? echo $formVars["Shift"]; ?>" size=50></td>
</tr>

</table>

<br>

<input type="submit" value="Submit">
</form>

</div>

<br>
<br>

<?php
foreach($HTTP_POST_VARS as $varname => $value)
$formVars[$varname]=$value;

require_once("config.php");

$db1=mysql_connect($dbhost, $dbuname, $dbpass);
mysql_select_db("training_db");

$query="SELECT * FROM annual WHERE Emp_Id = \"".$formVars["Emp_Id"]."\"";

$result=mysql_query($query);
$row=mysql_fetch_array($result);
$formVars = array();

$formVars["harassment"]=$row["harassment"];
$formVars["bbp"]=$row["bbp"];
$formVars["loto"]=$row["loto"];
$formVars["hazcomm"]=$row["hazcomm"];
$formVars["ppe"]=$row["ppe"];
$formVars["evacuation"]=$row["evacuation"];
$formVars["drug"]=$row["drug"];

mysql_close($db1);
?>

<div align="center">

<form method="post" action="postupdate2.php">

<table>

<col span="1" align="right">

<tr>
<td><font color="red">Harassment:</font></td>
<td><input type="text" name="harassment" 
value="<? echo $formVars["harassment"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">BBP:</font></td>
<td><input type="text" name="bbp" 
value="<? echo $formVars["bbp"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Loto:</font></td>
<td><input type="text" name="loto" 
value="<? echo $formVars["loto"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Haz Comm:</font></td>
<td><input type="text" name="hazcomm" 
value="<? echo $formVars["hazcomm"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">PPE:</font></td>
<td><input type="text" name="ppe" 
value="<? echo $formVars["ppe"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Evacuation:</font></td>
<td><input type="text" name="evacuation" 
value="<? echo $formVars["evacuation"]; ?>" size=50></td>
</tr>
<tr>
<td><font color="red">Drug Free:</font></td>
<td><input type="text" name="drug" 
value="<? echo $formVars["drug"]; ?>" size=50></td>
</tr>

</table>

<br>

<input type="submit" value="Submit">

</form>

</div>

</div> 

</div> 

<br>
<br>

<div align="center">

<div class="hovermenu">
<ul>
<li><a href="TrainingDataBaseSearch.php">Search Training</a></li>
<li><a href="TrainingDataBaseAdd.php">Add Training</a></li>
<li><a href="TrainingUpdate.html">Update Training</a></li>
<li><a href="../index.html">Home</a></li>
</ul>
</div>

</div>

<br>

</div>

<br>
<br>

</div>

<br>

</body>

</html>

postupdate1.php code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<title>Updated Employee Records</title>

<link rel="stylesheet" type="text/css" href="TrainingDataBase.css">
<link rel="shortcut icon" href="../images/favicon.ico" type="image/x-icon" />

</head>

<body>

<div id="container">

<div align="center">
<p>
<h1 align="center"><u><strong><font face="arial">Updated Employee Records</font></strong></u></h1>
</p>

</div> 

<br>

<div id="select">

<br>
<br>

<div align="center">

<?php
foreach($HTTP_POST_VARS as $varname => $value)
{
$formVars[$varname]=$value;
}

require_once("config.php");

$db1=mysql_connect($dbhost, $dbuname, $dbpass);
mysql_select_db("training_db");

echo "Congratulations the record has been successfully updated<br><br><a href=\"TrainingUpdate.html\">CLICK HERE</a><br><br>to update another record<br><br><br>";

$query = ("UPDATE `employee` SET FirstName=".$formVars["FirstName"].",
LastName=".$formVars["LastName"].",Dept=".$formVars["Dept"]."Shift=".$formVars["Shift"]." WHERE Emp_Id = '".$formVars["Emp_Id"]."'");

$query="UPDATE `employee` SET ".

"FirstName= \"".$formVars["FirstName"]."\",".
"LastName= \"".$formVars["LastName"]."\",".
"Dept= \"".$formVars["Dept"]."\"".
"Shift= \"".$formVars["Shift"]."\"".

"\" WHERE Emp_Id = \"".$formVars["Emp_Id"]."\"";

mysql_query($query);
mysql_close($db1);
?>

</div>

</div>

<br>
<br>

</div>

</body>

</html>

postupdate2.php code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<title>Updated Employee Annual Training Records</title>

<link rel="stylesheet" type="text/css" href="TrainingDataBase.css">
<link rel="shortcut icon" href="../images/favicon.ico" type="image/x-icon" />

</head>

<body>

<div id="container">

<br>
<br>

<div align="center">
<p>
<h1 align="center"><u><strong><font face="arial">Updated Employee Annual Training Records</font></strong></u></h1>
</p>

</div> 

<br>

<div id="select">

<br>
<br>

<div align="center">

<?php
foreach($HTTP_POST_VARS as $varname => $value)
$formVars[$varname]=$value;

require_once("config.php");

$db1=mysql_connect($dbhost, $dbuname, $dbpass);
mysql_select_db("training_db");

echo "Congratulations the record has been successfully updated<br><br><a href=\"TrainingUpdate.html\">CLICK HERE</a><br><br>to update another record<br><br><br>";

$query="UPDATE annual SET ".

"harassment= \"".$formVars["harassment"]."\",".
"bbp= \"".$formVars["bbp"]."\",".
"loto= \"".$formVars["loto"]."\",".
"hazcomm= \"".$formVars["hazcomm"]."\",".
"ppe= \"".$formVars["ppe"]."\",".
"evacuation= \"".$formVars["evacuation"]."\",".
"drug= \"".$formVars["drug"]."\",".

"\" WHERE Emp_Id = \"".$formVars["Emp_Id"]."\"";

mysql_query($query);
mysql_close($db1);
?>

</div>

</div>

<br>
<br>

</div>

</body>

</html>

The way it's set up so far is you have to update each table one at a time. I would like to be able to update all the tables at once. The code for the selection of the employee (TrainingUpdate.html) and the page that lists the current data in the database for each employee (TrainingUpdate.php) work perfect but when I try to update it seems to update (no errors) and I get the Congratulations the record has been successfully updated message but it DOES NOT actually update the database. Thanks for any help..

ixalmida

I don't quite understand what you are asking for help with but the first thing I would do is rethink your program flow.

Try this:

Data Entry Page ~~> Data Processing Page ~~> View Results Page
        ^                                 |
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

The problem with mixing data processing and entry/display is that you have to deal with the repost issue. In most cases, that means preventing a repost, but that can lead to confusion or annoyance for your users - especially if that page is no longer accessible without it's own POST data.

The benefits of processing data in a separate file are many. First, it encourages reusable code. For example, you may find the employee update module can process records from several different pages with only a slight alteration. A clever developer might even mix INSERT and UPDATE functions on the same page.

Secondly, it reduces the overall size of your pages - always a good thing in my book, especially when debugging code. Thirdly, it can help to obscure the workings of your application from the user, which is useful in preventing hacker attacks.

Bjom

sorry to disagree on a few points:

The benefits of processing data in a separate file are many.

Separate files and separate pages are not necessarily the same thing. Also separate files do not guarantee for reusable code at all. On the contrary they can make your whole app a maintenance nightmare. (A good example for a way that guarantees reusable code would be writing generic classes...)

obscure the workings of your application from the user, which is useful in preventing hacker attacks

security through obscurity doesn't work well.

ixalmida

Excuse me but we don't disagree. You just misinterpreted what I said.

I never said "guarantee" reusable code, I said "encourage" reusable code. I also never said "obscurity works great". I said it can help. I choose my words very carefully, so don't read sentiments into them that aren't there.

You might also note that there is no OOP in the code, so unless you want to start teaching OOP here, "generic classes" isn't a helpful suggestion.

igorek

Suggestions:

set connection variables to CONSTANTS in your config file
```
define("HOST", 99.1.1.11);
// etc
```
since you are doing the same thing you dont need this many pages just use includes for bits of your code or require_once();
```
require_once("config.inc.php");
require_once("updatequerys.inc.php");
// do this in your main file
```
state errors or success variables instead of just echo() when you access next page, you are not sure if query has been run and the row in your db has been updated

function updaterecordsinmydatabase($formdata){
// you do not need to make formdata into an array [b]since[/b] the method
// post and $_POST[] is a superglobal array
$result=mysql_query($query,$connection);
if(!$result){$error="Your query wasnt run";}

$success="You have updated record in database!";
return $success;
}

use a primary key to update a user record with inner join statement in your sql. if you are updating more than one table the subsequent table must have a foreign key that relates to the father table. for ex. tbl_subject id=1, tbl_item, id=1 subjectid=1, id=2 subjectid=1.
```
// query time
$query =  "UPDATE tbl_one "
$query .= "LEFT JOIN tbl_two USIGN(subjectid) "
$query .= "SET colname='value' "
$query .= "WHERE tbl_one.id='value'"

// your query will be dynamical, best to split it up like this easier to adjust
```
1. close connection at the footer of the page beyound the </html> tag since you are still using it during the process of the page

bradgrafelman

igorek;10946041 wrote:
5. close connection at the footer of the page beyound the </html> tag since you are still using it during the process of the page

Not sure what you mean by that... none of the three scripts posted above use the SQL connection after it was closed via [man]mysql_close/man, so I don't see why there'd be a reason to move the function call to a later point.

igorek

bradgrafelman;10946045 wrote:
Not sure what you mean by that... none of the three scripts posted above use the SQL connection after it was closed via [man]mysql_close/man, so I don't see why there'd be a reason to move the function call to a later point.

trainingupdate.php 2x same connection

in fact if he redoes his script with require_once(); and doesn't change the connections it will open 4x and close instead where he can open before <html> and close after </html>

then he doesnt even need to specify the connection in mysql_query($query);

bradgrafelman

Ah, that's what I get for scrolling too quickly.