Jump to content


Photo

SQL Syntax Error on edit submit


  • Please log in to reply
2 replies to this topic

#1 chimp

chimp
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 16 August 2006 - 03:37 PM

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.

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


my table structure is as follows:

  `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 ;




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:

<?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);
?>


#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 16 August 2006 - 03:40 PM

First thing that springs to mind is that '&' may not be a legal character for use in column names.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 16 August 2006 - 03:45 PM

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
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users