Page 1 of 1

Update problem- syntax error

Posted: Sat Aug 16, 2008 10:36 am
by macfi
Hi,

I am trying to create a editing page, but I keep getting an error:
Error updating member details: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE business_id=''' at line 7

The version on the server for MYSQL is 5.0.45
I have been through and through the coding and the manual and I cannot see the syntax error. :banghead: :banghead:
Any help would be very gratefully received.






<!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" xml:lang="en" lang="en">

<head>
<title>edit member page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
</style>
</head>
<body>
<?php require 'secure.inc.php' ?>
<?php
// connect to the database server
$dbcnx = @mysql_connect('localhost', 'xxx', 'xxxxxx');
if (!$dbcnx) {
exit('<p>Unable to connect to the database server at this time.</p>');
}
// select the members database
if (!@mysql_select_db('qba_org_uk_membership')) {
exit('<p>Unable to locate the members database at this time.</p>');
}

if (isset($_POST['business_name'])):
// The business details have been updated.

$business_name = $_POST['business_name'];
$contact_name = $_POST['contact_name'];
$business_email = $_POST['business_email'];
$business_telephone = $_POST['business_telephone'];
$business_mebership_type = $_POST['business_mebership_type'];
$sql = "UPDATE members_list SET
business_name='$business_name',
contact_name='$contact_name',
business_email='$business_email',
business_telephone='$business_telephone',
business_mebership_type='$business_mebership_type',
WHERE business_id='$business_id'";
if (@mysql_query($sql)) {
echo '<p>Member details updated.</p>';
} else {
echo '<p>Error updating member details: ' .
mysql_error() . '</p>';
}
?>
<p><a href="database_home_page.php">Home Page</a></p>
<?php
else: //allow the user to edit the member

$business_id = $GET['business_id'];
$result = @mysql_query(
"SELECT business_name, contact_name, business_email, business_telephone, business_mebership_type FROM members_list WHERE business_id='$business_id'");
if (!result) {
exit('<p>Error fetching business deatils: ' .
mysql_error() . '</p>');
}

$result = mysql_fetch_array($result);

$business_name = $result['business_name'];
$contact_name = $result['contact_name'];
$business_email = $result['business_email'];
$business_telephone = $result['business_telephone'];
$business_mebership_type = $result['business_mebership_type'];
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<p>Edit the member:</p>
<label>Business Name: <input type="text" name="business_name"
value="<?php echo $business_name; ?>" /></label><br />
<label>Contact Name: <input type="text" name="contact_name"
value="<?php echo $contact_name; ?>" /></label><br />
<label>Business Email: <input type="text" name="business_email"
value="<?php echo $business_email; ?>" /></label><br />
<label>Business Telephone: <input type="text" name="business_telephone"
value="<?php echo $business_telephone; ?>" /></label><br />
<label>Business Membership Type: <input type="text" name="business__mebership_type"
value="<?php echo $business__mebership_type; ?>" /></label><br />
<input type="hidden" name="busness_id" value="<?php echo $busness_id; ?>" />
<input type="submit" value="SUBMIT" /></p>

</form>
<?php endif; ?>



</body>
</html>

Re: Update problem- syntax error

Posted: Sat Aug 16, 2008 5:29 pm
by The_Anomaly
macfi wrote:Hi,

I am trying to create a editing page, but I keep getting an error:
Error updating member details: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE business_id=''' at line 7
You didn't use the tags, so this was rather hard to read. However, I think I see your problem. You don't assign a value to $business_id until AFTER your first query. That's why it says the error is found there. Move the assigning of that value before the query, and I'd think it would work.

Maybe you're doing this but I didn't see...but be sure to escape those variables in your query. Not doing so is just asking for SQL Injection.

Re: Update problem- syntax error

Posted: Sat Aug 16, 2008 11:52 pm
by califdon
You have an extra comma just before the WHERE clause.

When you get an error like that, read what it says! It points you precisely to where the error is. You could save yourself a lot of time by taking advantage of the MySQL error reporting. We don't mind helping you, but you really could have solved it yourself in a matter of minutes by just looking at the error message. Commas are used to separate field names, not after the last field name.

And please, please, please, the next time you post code, use the BBCode Tags to make your code easier for us to read. For PHP code, you can use either [ code=PHP ] ... [ /code ] or just [ php ] ... [ /php ] (I inserted spaces here so it wouldn't interpret them--naturally, don't use spaces).