Page 1 of 1

[SOLVED] UPDATE query not working

Posted: Wed Aug 25, 2004 12:18 pm
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.

?>

Posted: Wed Aug 25, 2004 12:38 pm
by feyd
[php_man]mysql_info[/php_man] may help a bit..

what does the rendered queries look like on the "error" output?

Posted: Wed Aug 25, 2004 12:54 pm
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.

Posted: Wed Aug 25, 2004 1:07 pm
by feyd
just for kicks, var_dump $r1 and mysql_affected_rows() in that error.

Posted: Wed Aug 25, 2004 1:16 pm
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.

Posted: Wed Aug 25, 2004 1:24 pm
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.

Posted: Wed Aug 25, 2004 1:37 pm
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?

Posted: Wed Aug 25, 2004 2:03 pm
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..

Posted: Wed Aug 25, 2004 2:21 pm
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?

Posted: Wed Aug 25, 2004 2:29 pm
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... :?

Posted: Thu Aug 26, 2004 6:59 am
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

Posted: Thu Aug 26, 2004 3:48 pm
by cdickson
GOT IT! :D

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