SQL Syntax Error on edit submit

chimp · August 16, 2006

hi i am lost!!! really lost, i have some php script that i am trying to slightly change by adding a new text entry field.

I have made the change and get the following error when i click my submit button.

[code]
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 '&bname='450', price=NULL, special_price='Simply immaculate detac
[/code]

my table structure is as follows:

[code]
`b&bname` varchar(50) default NULL,
`id` int(11) NOT NULL auto_increment,
`cid` int(11) default NULL,
`scid` int(11) default NULL,
`mid` int(11) default NULL,
`title` varchar(50) default NULL,
`price` double default NULL,
`special_price` double default NULL,
`pdate` datetime default NULL,
`sold` varchar(4) default NULL,
`description` text,
`address` varchar(50) default NULL,
`city` varchar(45) default NULL,
`state` varchar(45) default NULL,
`zip` varchar(50) default NULL,
`exp` int(11) default NULL,
`featured` varchar(4) default NULL,
`active` varchar(5) default NULL,
`hits` int(11) default NULL,
`bed` varchar(10) default NULL,
`bath` varchar(10) default NULL,
`subdiv` varchar(60) default NULL,
`schoold` varchar(60) default NULL,
`year` int(11) default NULL,
`lot_size` double default NULL,
`sqft` double default NULL,
`lat` varchar(40) default NULL,
`long` varchar(40) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

[/code]

It is an edit.php file, so i am trying to edit a current record in my mysql table.

the new field i have added is called "b&bname"

I have added a new field in mymsql table called "b&bname"

i really don't get why i am getting this error.

please help...

my full code is here:

[code=php:0]
<?php require_once('Connections/myconn.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

//begin debug
//print $_REQUEST['city'];
//die();
//end debug

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

//fetch logn and lat for address
$request_address = $_REQUEST['address'] . ', '.$_REQUEST['city'].' '.$_REQUEST['state'].' '.$_REQUEST['zip'];
require('admin/xmlparse.ini.php');
require('admin/llfetch.php');

$updateSQL = sprintf("UPDATE items SET `long`=%s, `lat`=%s, cid=%s, title=%s, b&bname=%s, price=%s, special_price=%s, description=%s, address=%s, city=%s, `state`=%s, zip=%s, bed=".GetSQLValueString($_REQUEST['bed'],"text").", bath=".GetSQLValueString($_REQUEST['bath'],"text").", subdiv=".GetSQLValueString($_REQUEST['subdiv'],"text").", schoold = ".GetSQLValueString($_REQUEST['schoold'],"text").", year = ".GetSQLValueString($_REQUEST['year'],"int").", lot_size = ".GetSQLValueString($_REQUEST['lot_size'],"double").", sqft = ".GetSQLValueString($_REQUEST['sqft'],"double")." WHERE id=".GetSQLValueString($_REQUEST['id'],"int")."",
GetSQLValueString($address['long'], "text"),
GetSQLValueString($address['lat'], "text"),
GetSQLValueString($_POST['cat'], "int"),
GetSQLValueString($_POST['title'], "text"),
GetSQLValueString($_POST['price'], "double"),
GetSQLValueString($_POST['special_price'], "double"),
GetSQLValueString($_POST['description'], "text"),
GetSQLValueString($_POST['b&bname'], "text"),
GetSQLValueString($_POST['address'], "text"),
GetSQLValueString($_POST['city'], "text"),
GetSQLValueString($_POST['state'], "text"),
GetSQLValueString($_POST['zip'], "text"),
GetSQLValueString($_POST['id'], "int")
);
//echo $updateSQL;
//die();
mysql_select_db($database_myconn, $myconn);
$Result1 = mysql_query($updateSQL, $myconn) or die(mysql_error());

$updateGoTo = "a_index.php";
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}

?>
<? include('header.php'); ?>
<?php

//fetch all categories from database , and load into a assoc array
mysql_select_db($database_myconn, $myconn);
$query_cat = "SELECT * FROM categories ORDER BY title ASC";
$cat = mysql_query($query_cat, $myconn) or die(mysql_error());
$row_cat = mysql_fetch_assoc($cat);
$totalRows_cat = mysql_num_rows($cat);

//fetch member information from datebase where email matches session varable
$colname_minfo = "1";
if (isset($_SESSION['MM_Username'])) {
$colname_minfo = (get_magic_quotes_gpc()) ? $_SESSION['MM_Username'] : addslashes($_SESSION['MM_Username']);
}
mysql_select_db($database_myconn, $myconn);
$query_minfo = sprintf("SELECT * FROM members WHERE email = '%s'", $colname_minfo);
$minfo = mysql_query($query_minfo, $myconn) or die(mysql_error());
$row_minfo = mysql_fetch_assoc($minfo);
$totalRows_minfo = mysql_num_rows($minfo);

mysql_select_db($database_myconn, $myconn);
$query_city = "SELECT * FROM city ORDER BY title ASC";
$city = mysql_query($query_city, $myconn) or die(mysql_error());
$row_city = mysql_fetch_assoc($city);
$totalRows_city = mysql_num_rows($city);

mysql_select_db($database_myconn, $myconn);
$query_state = "SELECT * FROM `state` ORDER BY title ASC";
$state = mysql_query($query_state, $myconn) or die(mysql_error());
$row_state = mysql_fetch_assoc($state);
$totalRows_state = mysql_num_rows($state);

//fetch all active credits from accounting table in database
$colname_a = $row_minfo['id']; // store member id
mysql_select_db($database_myconn, $myconn);
$query_a = sprintf("SELECT * FROM accounting WHERE mid = %s", $colname_a) . " and active = 'Yes'";
//echo $query_a;
$a = mysql_query($query_a, $myconn) or die(mysql_error());
$row_a = mysql_fetch_assoc($a);
$totalRows_a = mysql_num_rows($a);

//fetch item from database and place in assoc array
if (isset($_GET['id'])) {
$colname_item = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_myconn, $myconn);
$query_item = sprintf("SELECT * FROM items WHERE id = %s", $colname_item) . " and mid =" . $row_minfo['id'];

$item = mysql_query($query_item, $myconn) or die(mysql_error());
$row_item = mysql_fetch_assoc($item);
$totalRows_item = mysql_num_rows($item);
?>
<link href="mystyle.css" rel="stylesheet" type="text/css">

<form method="POST" action="<?php echo $editFormAction; ?>" name="form1">
<table width="500" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#FFFFFF">
<caption align="left" class="pageTitle">
<h1>Edit Property Listing</h1>
</caption>
<tr>
<td class="colText">Category</td>
<td bgcolor="#FFFFFF"><select name="cat" id="cat">
<?php
do {
?>
<option value="<?php echo $row_cat['id']?>"<?php if (!(strcmp($row_cat['id'], $row_item['cid']))) {echo "SELECTED";} ?>><?php echo $row_cat['title']?></option>
<?php } while ($row_cat = mysql_fetch_assoc($cat));
$rows = mysql_num_rows($cat);
if($rows > 0) {
mysql_data_seek($cat, 0);
$row_cat = mysql_fetch_assoc($cat);
}
?>
</select></td>
</tr>
<tr>
<td class="colText">B&B Name:</td>
<td bgcolor="#FFFFFF"><input name="b&bname" type="text" id="b&bname" value="<?php echo $row_item['b&bname']; ?>" size="25"></td>
</tr>

<tr>
<td class="colText">Price:</td>
<td bgcolor="#FFFFFF"><input name="price" type="text" id="price" value="<?php echo $row_item['price']; ?>" size="25"></td>
</tr>
<tr>
<td class="colText">Address</td>
<td bgcolor="#FFFFFF"><input name="address" type="text" id="address" value="<?php echo $row_item['address']; ?>" size="40"></td>
</tr>
<tr>
<td class="colText">City/Town</td>
<td bgcolor="#FFFFFF"> <span class="rowText">
<? if(mysql_num_rows($city) <=0){?>
<input name="city" type="text" id="city" value="<?php echo $row_item['city'] ?>">
<? }else { ?>
<select name="city" id="city">
<?php
do {
?>
<option value="<?php echo $row_city['title']?>"<?php if ($row_city['title'] == $row_item['city']) {echo "SELECTED";} ?>><?php echo $row_city['title']?></option>
<?php
} while ($row_city = mysql_fetch_assoc($city));
$rows = mysql_num_rows($city);
if($rows > 0) {
mysql_data_seek($city, 0);
$row_city = mysql_fetch_assoc($city);
}
?>
</select>
<? } ?>
</span></td>
</tr>
<tr>
<td class="colText">County</td>
<td bgcolor="#FFFFFF"><span class="rowText"> <select name="state" id="state" style="width: 109px;">
<?php
do {
?>
<option value="<?php echo $row_state['stitle']?>"<?php if (!(strcmp($row_state['stitle'], $row_item['state']))) {echo "SELECTED";} ?>><?php echo $row_state['title']?></option>
<?php
} while ($row_state = mysql_fetch_assoc($state));
$rows = mysql_num_rows($state);
if($rows > 0) {
mysql_data_seek($state, 0);
$row_state = mysql_fetch_assoc($state);
}
?>
</select>
</span></td>
</tr>
<tr>
<td class="colText">Postcode</td>
<td bgcolor="#FFFFFF"><input name="zip" type="text" id="zip" value="<?php echo $row_item['zip']; ?>" size="15"></td>
</tr>
<tr>
<td class="colText">Bed(s)</td>
<td bgcolor="#FFFFFF" class="rowText"><input name="bed" type="text" id="bed" value="<?php echo $row_item['bed']; ?>" size="15"></td>
</tr>
<tr>
<td class="colText">Bathroom(s)</td>
<td bgcolor="#FFFFFF" class="rowText"><input name="bath" type="text" id="bath" value="<?php echo $row_item['bath']; ?>" size="15"></td>
</tr>
<tr>
<td class="colText">Year</td>
<td bgcolor="#FFFFFF" class="rowText"><input name="year" type="text" id="year" value="<?php echo $row_item['year']; ?>" size="15"></td>
</tr>
<tr>
<td class="colText">Local Authority</td>
<td bgcolor="#FFFFFF" class="rowText"><input name="subdiv" type="text" id="subdiv" value="<?php echo $row_item['subdiv']; ?>" size="40" maxlength="60"></td>
</tr>
<tr>
<td class="colText">Local Hospital</td>
<td bgcolor="#FFFFFF" class="rowText"><input name="schoold" type="text" id="schoold" value="<?php echo $row_item['schoold']; ?>" size="40" maxlength="60"></td>
</tr>
<tr>
<td class="colText">Grounds (Acres)</td>
<td bgcolor="#FFFFFF" class="rowText"><input name="lot_size" type="text" id="lot_size" value="<?php echo $row_item['lot_size']; ?>" size="15"></td>
</tr>
<tr>
<td class="colText">Square Feet </td>
<td bgcolor="#FFFFFF" class="rowText"><input name="sqft" type="text" id="sqft" value="<?php echo $row_item['sqft']; ?>" size="15"></td>
</tr>
<tr>
<td colspan="2" class="colText"><p>Description</p> </td>
</tr>
<tr>
<td colspan="2" class="colText"><div align="center">
<textarea name="description" cols="70" rows="15" id="description"><?php echo $row_item['description']; ?></textarea>
</div></td>
</tr>
<tr>
<td colspan="2"><div align="right">
<input name="id" type="hidden" id="id" value="<?php echo $row_item['id']; ?>">
<input type="submit" name="Submit4" value="Submit">
<input type="reset" name="Submit22" value="Reset">
<input name="Submit32" type="button" onClick="window.location='a_index.php'" value="Cancel">
</div></td>
</tr>
</table>
<input type="hidden" name="MM_update" value="form1">
</form>
<? include('footer.php'); ?>
<?php
mysql_free_result($city);

mysql_free_result($state);

mysql_free_result($item);
?>
[/code]

king arthur · August 16, 2006

First thing that springs to mind is that '&' may not be a legal character for use in column names.

king arthur · August 16, 2006

In fact depending on your version of MySQL you can use it but you must put backticks around the column name in the query, e.g. `b&bname`=%s

Sign In

SQL Syntax Error on edit submit

Recommended Posts

chimp

Link to comment

Share on other sites

king arthur

Link to comment

Share on other sites

king arthur

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information