[SOLVED] UPDATE query not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

[SOLVED] UPDATE query not working

Post by cdickson »

I am trying to create a form that will update a specific record in my database. I can select the record, and the form with the fields to be input comes up OK.

The problem is after I enter information into the various fields:
- Sometimes it says the record has been updated, but when I check in PHPMyAdmin, it hasn't updated.
- Other times, as per my code, I get a message that the record wasn't updated, but I don't get any MySQL or PHP errors at all, so I can't seem to hone in on where I've gone wrong.

Code:

Code: Select all

<?php
if ($dbc = @mysql_connect ('localhost', 'username', 'password')) {
	
	if (!@mysql_select_db ('mdb')) {
		die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>');
	}

} else {
	die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');
}

if (isset ($_POST['submit'])) { // Handle the form.
	
	// Define the query.
	$query1 = "UPDATE orders SET 
	  ins_amount='{$_POST['ins_amount']}', community_name='{$_POST['community_name']}', 
	  community_no='{$_POST['community_no']}', map_no='{$_POST['map_no']}', 
	  map_eff_date='{$_POST['map_eff_date']}', loma_lomr='{$_POST['loma_lomr']}', 
	  loma_lomr_date='{$_POST['loma_lomr_date']}', flood_zone='{$_POST['flood_zone']}', 
	  nfip_map='{$_POST['nfip_map']}', ins_avail='{$_POST['ins_avail']}', bldg_CBRA='{$_POST['bldg_CBRA']}', 
	  CBRA_date='{$_POST['CBRA_date']}', flood_hazard='{$_POST['flood_hazard']}', comments='{$_POST['comments']}'
	  WHERE order_no={$_POST['order_no']} LIMIT 1";
	$r1 = mysql_query ($query1); // Execute the query.
	
	// Report on the result.
	if (mysql_affected_rows() == 1) {
		print '<p>The order has been updated.</p>';
	} else {
		echo "<p>Could not update the order because: <b>" . mysql_error() . "</b>. The query was $query1.</p>";
	}

} else { // Display the entry in a form.

	// Check for a valid entry ID in the URL.
	if (is_numeric ($_GET['order_no']) ) {
	
		// Define the query.
		$query2 = "SELECT * FROM orders WHERE order_no={$_GET['order_no']}";
		if ($r2 = mysql_query ($query2)) { // Run the query.
		
			$row = mysql_fetch_array ($r2); // Retrieve the information.
			
			// Make the form.
			echo '<form action="updateorder2.php" method="post">
			<table width="500" columns="2" rows="40">
		<tr>
		<td width="40%"><b>Order Number:</b></td>
		<td>' . $row['order_no'] . '</p></td>
		</tr>
		<tr>
		<td width="40%"><b>Order Date:</b></td>
		<td>' . $row['order_date'] . '</p></td>
		</tr>
		<tr>
		<td><b>Lender Information:</b></td>
		<td>' . $row['lender_name'] . '</td>
		</tr>
		<tr>
		<td></td>
		<td>' . $row['lender_address'] . '</td>
		<tr>
		<td></td>
		<td>' . $row['lender_city'] . ', ' . $row['lender_state'] . '  ' . $row['lender_zip'] . '</td>
		</tr>
		<tr>
		<td></td>
		<td><b>Lender ID:</b> ' . $row['lender_id'] . '</td>
		</tr>
		<br /><br />
		<tr>
		<td><b>Property Information:</b></td>
		</tr>
		<tr>
		<td>Owner Name: </td>
		<td>' . $row['collateral_owner'] . '</td>
		</tr>
		<tr>
		<td>Property Address: </td>
		<td>' . $row['collateral_address'] . '</td>
		</tr>
		<tr>
		<td></td>
		<td>' . $row['collateral_city'] . ', ' . $row['collateral_state'] . '  ' . $row['collateral_zip'] . '</td>
		</tr>
		<tr>
		<td>Loan Number:</td>
		<td>' . $row['loan_no'] . '</td>
		</tr>
		<tr>
		<td>Legal Description:</td><br />
		<td>' . $row['legal_descr'] . '<br /><br /></td>
		</tr>
		<tr>
		<td>
		<hr /><br />
		<b>Determination Information:</b></td>
		<td></td>
		</tr>
		<tr>
		<td>Insurance Amount:</td>
		<td>$<input type="text" name="title" size="10" maxsize="10" value="' . $row['ins_amt'] . '" /></td>
		</tr>
		<tr>
		<td>Community Name: </td>
		<td><input type="text" name="title" size="20" maxsize="60" value="' . $row['community_name'] . '" /></td>
		</tr>
		<tr>
		<td>NFIP Map Number: </td>
		<td><input type="text" name="title" size="20" maxsize="20" value="' . $row['map_no'] . '" /></td>
		</tr>
		<tr>
		<td>NFIP Map Panel Effective/Revised Date: </td>
		<td><input type="text" name="title" size="10" maxsize="10" value="' . $row['map_eff_date'] . '" /></td>
		</tr>
		<tr>
		<td>LOMA/LOMR: </td>
		<td><input type="checkbox" name="Yes" value="loma_lomr" />Yes  <input type="checkbox" name="No" value="loma_lomr" /> No</td>
		</tr>
		<tr>
		<td>LOMA/LOMR Date: </td>
		<td><input type="text" name="title" size="10" maxsize="10" value="' . $row['loma_lomr_date'] . '" /> yyyy-mm-dd</td>
		</tr>
		<tr>
		<td>Flood Zone: </td>
		<td><input type="text" name="title" size="10" maxsize="10" value="' . $row['flood_zone'] . '" /></td>
		</tr>
		<tr>
		<td>NFIP Map? </td>
		<td><input type="checkbox" name="Yes" value="nfip_map" />Yes  <input type="checkbox" name="No" value="nfip_map" /> No</td>
		</tr>
		<tr>
		<td>Federal Flood Insurance is Available: </td>
		<td><input type="checkbox" name="Yes" value="ins_avail" />Yes  <input type="checkbox" name="No" value="ins_avail" /> No</td>
		</tr>
		<tr>
		<td>Regular Program? </td>
		<td><input type="checkbox" name="Yes" value="reg_program" />Yes  <input type="checkbox" name="No" value="reg_program" /> No</td>
		</tr>
		<tr>
		<td>Emergency Program? </td>
		<td><input type="checkbox" name="Yes" value="emergency" />Yes  <input type="checkbox" name="No" value="emergency" /> No</td>
		</tr>
		<tr>
		<td>Federal Flood Insurance not Available:</td>
		<td><input type="checkbox" name="Yes" value="ins_notavail" />Yes  <input type="checkbox" name="No" value="ins_notavail" /> No</td>
		</tr>
		<tr>
		<td>Building/Mobile Home is in a Coastal Barrier Resources Area (CBRA) or Otherwise Protected Area (OPA): </td>
		<td><input type="checkbox" name="Yes" value="bldg_CBRA" />Yes  <input type="checkbox" name="No" value="bldg_CBRA" /> No</td>
		</tr>
		<tr>
		<td>CBRA/OPA designation date: </td>
		<td><input type="text" name="title" size="10" maxsize="10" value="' . $row['CBRA_date'] . '" /> yyyy-mm-dd</td>
		</tr>
		<tr>
		<td>Life of Loan Determination: </td>
		<td></td>
		</tr>
		<tr>
		<td>Is building/mobile home in special flood hazard area (zones containing
		the letters "A" or "V"?</td>
		<td><input type="checkbox" name="Yes" value="flood_hazard" />Yes  <input type="checkbox" name="No" value="flood_hazard" /> No</td
		</tr>
		</table>
		<input type="hidden" name="order_no" value="' . $_GET['order_no'] . '" />
		<input type="submit" name="submit" value="Update Order" />
		</form>';
	
		} else { // Couldn't get the information.
			echo "<p>Could not retrieve the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
		}

	} else { // No order no set
		echo '<p><b>You must have made a mistake in using this page.</b></p>';
	}

} // End of main IF.

mysql_close(); // Close the database connection.

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

[php_man]mysql_info[/php_man] may help a bit..

what does the rendered queries look like on the "error" output?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Still messing around with correct mysql_info() syntax - thanks for the hint.

My error message reads:
Could not update the order because: .
The query was UPDATE orders SET ins_amount='', community_name='', community_no='', map_no='', map_eff_date='', loma_lomr='', loma_lomr_date='', flood_zone='', nfip_map='', ins_avail='No', bldg_CBRA='No', CBRA_date='', flood_hazard='', comments='' WHERE order_no=67 LIMIT 1.
As you can see in the first line, there is no MySQL error returned.

I also have

Code: Select all

<?php
ini_set('display_errors', 1);		//Handle errors
error_reporting (E_ALL & ~E_NOTICE);
?>
at the top of my page to return any PHP errors, but none have been returned.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

just for kicks, var_dump $r1 and mysql_affected_rows() in that error.
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Now I have to admit that I've never done this before, so...
I added var_dump as follows (just need to make sure the syntax at both locations is correct):

Code: Select all

$r1 = mysql_query ($query1); // Execute the query.
var_dump($r1);
and

Code: Select all

// Report on the result.
if (mysql_affected_rows() == 1) {
var_dump(mysql_affected_rows());
print '<p>The order has been updated.</p>';
and my new error messages says
bool(true)

Could not update the order because: . The query was UPDATE orders SET ins_amount='', community_name='', community_no='', map_no='', map_eff_date='', loma_lomr='', loma_lomr_date='', flood_zone='', nfip_map='', ins_avail='', bldg_CBRA='', CBRA_date='', flood_hazard='', comments='' WHERE order_no=67 LIMIT 1.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

add a var_dump of affected_rows so it'll kick out with the error.

I'm thinking no records matched the update.
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

That's what it seems like, but it does match.

Perhaps I need to explain a little further.

Initially an order is placed online by a customer, and the order form creates the initial record in the database.

Once that record is created, the vendor then opens this "Update Order" page that I am working on. The information that is input by the customer is displayed at the top of the page, and the remainder of the fields that the vendor needs to input are displayed underneath.

This means that this Update Order form is only updating half of the fields in the record.

Would it help you to view the pages online?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the number of fields being updated has nothing to do with the problem that I've ever seen.. the syntax looks fine, obviously mysql doesn't have a problem with it there..

The only thing I see that can be "wrong" is the order_no restriction you have doesn't match a record.. mysql_info should tell you if it actually updated anything.. and the matching numbers as well..
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

That seems to make sense...

When I select the record I choose by order number, which is an auto-incremented field.

Do I need to use sessions (which I've never used) to carry that information forward?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it shouldn't require sessions.. basically, as long as the id number you use in the restriction remains the same across the 2 pages, it should be fine.. so I'm not sure what's wrong unless you are accidently changing the number used between them... :?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

I'm absolutely not changing the order number - it isn't an option on the Update Order page since the selection takes place on the previous page.

Yesterday after I posted this I did get one field to update, but can't seem to get the update to work when all of the fields are filled in.

I'll keep working on it. :evil: Thanks for your help, feyd. :D
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

GOT IT! :D

Hard to believe, I know, but a few syntactial errors... :oops:
Sheesh!
Post Reply