wantabe2
asked on
Updating Mysql Database
I'm having an aweful time getting this to work.....I'm trying to update a table in a mysql database via a browser using php. The attached code only pulls the first record in the database & not the recors I click on. Each record is identified by an auto incrementing ID in the mysql database.
Basically, when I view the data in a browser on another page, & they click the "edit" button for that record, that record pulls up "using the attached code" so I can edit it then submit it....... the attached code will only display the first record inthe database no matter what record I want to edit......
Basically, when I view the data in a browser on another page, & they click the "edit" button for that record, that record pulls up "using the attached code" so I can edit it then submit it....... the attached code will only display the first record inthe database no matter what record I want to edit......
<?php # edit_psrflow.php
$page_title = 'Edit a Record';
$con = mysql_connect("localhost","uname","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("psrflow", $con);
$result = mysql_query("SELECT * FROM psrinfo ");
// Check if the form has been submitted.
if (isset($_POST['submitted'])) {
$errors = array(); // Initialize error array.
// Check for a beginning time.
if (empty($_POST['pacts'])) {
$errors[] = 'You forgot to enter a PACTS number';
} else {
$bt = escape_data($_POST['pacts']);
}
if (empty($_POST['fname'])) {
$errors[] = 'You forgot to enter a last name';
} else {
$bt = escape_data($_POST['pacts']);
}
if (empty($_POST['lname'])) {
$errors[] = 'You forgot to enter a last name';
} else {
$bt = escape_data($_POST['pacts']);
}
$pacts = $_POST['pacts'];
$fname = $_POST['lname'];
$lname = $_POST['lname'];
$id = $_POST['id'];
$status = $_status['status'];
$location = $_location['location'];
$employee = $_employee['employee'];
$assgn_date = $_assgn_date['assgn_date'];
$interv_date = $_interv_date['interv_date'];
$sent_date = $_sent_date['sent_date'];
$due_rev = $_due_rev['due_rev'];
$due_suspo = $_due_suspo['due_suspo'];
$due_clerk = $_due_clerk['due_clerk'];
$due_super = $_due_super['due_super'];
$due_owner = $_due_owner['due_owner'];
if (empty($errors)) { // If everything's OK.
// Make the query.
$query = "UPDATE psrinfo SET pacts='$pacts', fname='$fname', lname='$lname', status='$status', employee='$employee', location='$location', assgn_date='$assgn_date', interv_date='$interv_date' sent_date='$sent_date', due_rev='$due_rev', due_suspo='$due_suspo', due_clerk='$due_clerk', due_super='$due_super', due_owner='$due_owner' WHERE id=$id";
$result = @mysql_query ($query); // Run the query.
$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
if ((substr($url, -1) == '/') OR (substr($url, -1) == '//')) {
$url = substr ($url, 0, -1);
}
$url .='/view_ts.php';
header("Location: $url");
exit();
} else { // Report the errors.
echo '<h1 id="mainhead">Error!</h1>
<p class="error">The following error(s) occurred:<br />';
foreach ($errors as $msg) { // Print each error.
echo " - $msg<br />\n";
}
echo '</p><p>Please try again.</p><p><br /></p>';
} // End of if (empty($errors)) IF.
} // End of submit conditional.
// Retrieve the user's information.
$query = "SELECT pacts, fname, lname, status, employee, location, assgn_date, interv_date, sent_date, due_rev, due_suspo, due_clerk, due_super, due_owner FROM psrinfo ";
$result = @mysql_query ($query); // Run the query.
list($pacts, $fname, $lname, $status, $employee, $location, $assgn_date, $interv_date, $sent_date, $due_rev, $due_suspo, $due_clerk, $due_super, $due_owner) = mysql_fetch_array($result, MYSQL_NUM);
?>
<script type="text/javascript">
var valid;
function d2(v) { return (v<10)?("0"+v):v; }
function dcheck(form) {
var a = form.assgn_date.value;
var s = form.sent_date.value;
var i = form.interv_date.value;
var dr = form.due_rev.value
var su = form.due_suspo
var clk = form.due_clerk
var att = form.due_super
var jdg = form.due_owner
var assn = new Date(a);
var sent = new Date(s);
var intv = new Date(i);
var due_rev = new Date(dr);
var due_suspo = new Date(su);
var due_clerk = new Date(clk);
var due_super = new Date(att);
var due_owner = new Date(jdg);
if (isNaN(intv)) {
intv = new Date(assn.getFullYear(),assn.getMonth(),assn.getDate()+0);
}
if (isNaN(assn)) {
assn = new Date(assn.getFullYear(),assn.getMonth(),assn.getDate()+0);
}
if (isNaN(due_rev)) {
due_rev = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-42);
}
if (isNaN(due_suspo)) {
due_suspo = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-40);
}
if (isNaN(due_clerk)) {
due_clerk = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-38);
}
if (isNaN(due_super)) {
due_super = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-36);
}
if (isNaN(due_owner)) {
due_owner = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-7);
}
switch(due_rev.getDay()){
case 0: due_rev.setDate(due_rev.getDate() - 1); // take one for Sunday
case 6: due_rev.setDate(due_rev.getDate() - 1); // take two for Sunday or one for Saturday
}
switch(due_suspo.getDay()){
case 0: due_suspo.setDate(due_suspo.getDate() - 1); // take one for Sunday
case 6: due_suspo.setDate(due_suspo.getDate() - 1); // take two for Sunday or one for Saturday
}
switch(due_clerk.getDay()){
case 0: due_clerk.setDate(due_clerk.getDate() - 1); // take one for Sunday
case 6: due_clerk.setDate(due_clerk.getDate() - 1); // take two for Sunday or one for Saturday
}
switch(due_super.getDay()){
case 0: due_super.setDate(due_super.getDate() - 1); // take one for Sunday
case 6: due_super.setDate(due_super.getDate() - 1); // take two for Sunday or one for Saturday
}
switch(due_owner.getDay()){
case 0: due_owner.setDate(due_owner.getDate() - 1); // take one for Sunday
case 6: due_owner.setDate(due_owner.getDate() - 1); // take two for Sunday or one for Saturday
}
switch(due_rev.getDay()){
case 0: due_rev.setDate(due_rev.getDate() - 1); // take one for Sunday
case 6: due_rev.setDate(due_rev.getDate() - 1); // take two for Sunday or one for Saturday
}
form.assgn_date.value = (assn.getFullYear()+0) + "-" + d2(assn.getMonth()+1) + "-" + d2(assn.getDate());
form.interv_date.value = (intv.getFullYear()+0) + "-" + d2(intv.getMonth()+1) + "-" + d2(intv.getDate());
form.sent_date.value = (sent.getFullYear()+0) + "-" + d2(sent.getMonth()+1) + "-" + d2(sent.getDate());
form.due_rev.value = (due_rev.getFullYear()+0) + "-" + d2(due_rev.getMonth()+1) + "-" + d2(due_rev.getDate());
form.due_suspo.value = (due_suspo.getFullYear()+0) + "-" + d2(due_suspo.getMonth()+1) + "-" + d2(due_suspo.getDate());
form.due_clerk.value = (due_clerk.getFullYear()+0) + "-" + d2(due_clerk.getMonth()+1) + "-" + d2(due_clerk.getDate());
form.due_super.value = (due_super.getFullYear()+0) + "-" + d2(due_super.getMonth()+1) + "-" + d2(due_super.getDate());
form.due_owner.value = (due_owner.getFullYear()+0) + "-" + d2(due_owner.getMonth()+1) + "-" + d2(due_owner.getDate());
return true;
}
</script>
<form action="edit_psrflow.php" method="post">
<fieldset><legend><h1> You are editing a record!</h1></legend>
<b>Customer No:</b> <br><input type="text" name="pacts" size="15" maxlength="30" value="<?php echo $pacts; ?>" /><br>
<b>First Name:</b> <br><input type="text" name="fname" size="15" maxlength="30" value="<?php echo $fname; ?>" /><br />
<b>Last Name:</b> <br><input type="text" name="lname" size="15" maxlength="30" value="<?php echo $lname; ?>" /><br />
<b>Status: </b><br><input type="text" name="status" size="15" maxlength="30" value="<?php echo $status; ?>" /> <br>
<b>Location: </b><br><input type="text" name="location" size="15" maxlength="30" value="<?php echo $location; ?>" /><br>
<b>employee: </b><br><input type="text" name="status" size="15" maxlength="30" value="<?php echo $employee; ?>" /> <br>
<b>Assign Date: MM/DD/YYYY </b><br><input type="text" name="assgn_date" size="15" maxlength="30" value="<?php echo $assgn_date; ?>" /> <br>
<b>Interview Date: MM/DD/YYYY </b><br><input type="text" name="interv_date" size="15" maxlength="30" value="<?php echo $interv_date; ?>" /> <br>
<b>Sent Date: MM/DD/YYYY </b><br><input type="text" name="sent_date" size="15" maxlength="30" value="<?php echo $sent_date; ?>" /> <br>
<p><input type="button" value="Calculate" onclick="return dcheck(this.form);"> <b> DO NOT enter anything below this line. Click the "Calculate" Button.</b></p>
<b>-----------------------------------------------------------</b> <br />
<b>Due to Reviewer:</b><br><input type="text" name="due_rev" size="15" maxlength="30" value="<?php echo $due_rev; ?>" /> <br>
<b>Due to SUSPO:</b><br><input type="text" name="due_suspo" size="15" maxlength="30" value="<?php echo $due_suspo; ?>" /> <br>
<b>Due to Clerk:</b><br><input type="text" name="due_clerk" size="15" maxlength="30" value="<?php echo $due_clerk; ?>" /> <br>
<b>Due to supervisor:</b><br><input type="text" name="due_super" size="15" maxlength="30" value="<?php echo $due_super; ?>" /> <br>
<b>Due to owner:</b><br><input type="text" name="due_owner" size="15" maxlength="30" value="<?php echo $due_owner; ?>" /> <br>
<br>
</fieldset>
<input type="hidden" name="submitted" value="TRUE" />
<div align="left"><input type="submit" name="submit" value="Submit" /></div>
</form>
<?php
mysql_close(); // Close the database connection.
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. I'm just going to scrap this code/idea & instead of me editing a record, I'm going to try to just create a way for my users to delete the record then enter the new information. That would be easier for me "I think" since I'm new to php & programming. I'll probably be posting some help deleteing a record in the near future but at least I'm going to give it a try.
http://www.sitepoint.com/books/phpmysql4/
A hacker could destroy your data base very easily. Here is why and how.
Line 41: $id = $_POST['id'];
Line 57: $query = "UPDATE psrinfo ... WHERE id=$id";
So all I need to do is POST "id" = "1 OR 1=1" and your script will update every row of the table with the same data. Almost certainly not what you want.
The programming mantra, when dealing with external input, must always be "Filter Input, Escape Output" -- and -- "Accept Only Known Good Values." This script does neither. It takes an unknown external value and uses it directly in a query. That is the kind of programming construct that will ensure that catastrophe is not left to chance. You probably want $id to be a positive integer, right? Then test for it, maybe using filter_var() or some similar expression.
You might also want to add LIMIT 1 to this query, too? Without that, MySQL will try to update every row of the data base table, even though only one row is needed. There's no extra credit for poor performance.
HTH, ~Ray