Solved

Updating Mysql Database

Posted on 2011-03-11
5
317 Views
Last Modified: 2012-06-27
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......
<?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.

?>

Open in new window

0
Comment
Question by:wantabe2
  • 3
5 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 334 total points
ID: 35110979
See line 13:
$result = mysql_query("SELECT * FROM psrinfo ");

You need to test queries for success.  Please see the man page here about the return values.
http://us3.php.net/manual/en/function.mysql-query.php

There may be other things wrong, but this jumped off the page at me.
0
 
LVL 13

Assisted Solution

by:dsmile
dsmile earned 166 total points
ID: 35110997
Your code will never work because of these reasons:
(unless what you postes is not complete)
1. dont know where data from line 42~52 come from
2. line 13: dont know why it is created since it's never used
3. line 57: wont work because $id doesn't exist
4. line 83 & 84 will definitely return first record as you said since you specified no ID. In fact, your query select all records but return only the first one since it was called with no loop.

A quick fix I may suggest is that:

1. change line 84 to this
$query = "SELECT id, pacts, fname, lname, status, employee, location, assgn_date, interv_date, sent_date, due_rev, due_suspo, due_clerk, due_super, due_owner FROM psrinfo WHERE id=".(int)$_REQUEST['id'];
Change line 87 to this
list($id, $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);

Where id is derived from query string. Eg: edit_psrflow.php?id=3 --> you'll see the form show data of user with id = 3

2. Add this hidden field to your form so that you can get $_POST['id']
<input type="hidden" name="id" value="<?php echo $id;?>">

3. Since "status" might be mistaken with MySQL keyword, it should be call with backstick in you query. Eg: `status`

For a long term benefit, I insist you read more about PHP & MySQL.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35111072
I'd like to recommend this book to you.  It is very readable and has great examples.  If you follow its guidance, it may help you write more dependable code.
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

0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 334 total points
ID: 35111103
Going forward, here is the general design I use when I have a table of data that needs to be updated.

1. Produce a tabular list of the rows, with a link to the update script.  This link has the key in the GET string, something like this:
<a href="update.php?k=123">Update 123</a>

2. The update script takes the key and SELECTs the row from the table.

3. With the selected data, the update script creates a POST-method form.  The form is prepopulated with the existing contents of the row.  The key of the row is stored in the form in a hidden control and in the session array

4. When the form is displayed, the client can change any field in the form

5. When the form is submitted we test to see if the key in the POST array matches the key in the SESSION array.  If not, we discard the input.  If so, we update the row and go back to step 2 or to step 1, depending on the design you choose.
0
 
LVL 15

Author Closing Comment

by:wantabe2
ID: 35111141
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Wordpress Body Class 5 13
Help cleaning out CSS 2 31
Create html table using xsl 8 11
php ssh2_scp_send 1 0
This article discusses how to create an extensible mechanism for linked drop downs.
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will learn how to count occurrences of each item in an array.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now