MySQL Error Message.

[zenlok]

Hi Guys,

I have tried to use a script that creates a simple list and has a form that adds data to the list and everytime I try and add some data, I get the following error message;

An Error Occurred: 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 'limit 1' at line 1

Does anyone know whart this means or how to resolve it?

Many thanks,

ZeNLok..

[sarvar]

I could check it, but please include the sql query.

[zenlok]

Hi Sarvar,

Thank you for your reply

The code is;

<?php
$hostname     = '';
$username     = '';
$password     = '';
$databasename = '';
if (!$connection_result = mysql_connect($hostname, $username, $password)) {
 die('Error Connecting to MySQL Database: ' . mysql_error());
}
if (!$db_result = mysql_select_db($databasename, $connection_result)) {
 die('Error Selecting the MySQL Database: ' . mysql_error());
}
// if we made it here, we are one with the database
switch($_GET['action']){
 case 'delete':
  $title = 'Delete an Item';
  $content = DeleteItem();
  break;
 case 'edit':
  $title = 'Edit an Item';
  $content = ManageItem();
  break;
 case 'new':
  $title = 'Create an Item';
  $content = ManageItem();
  break;
 case 'save':
  $title = 'Save an Item';
  $content = SaveItem();
  break;
 case 'print':
  PrintList();
  break;
 default:
  $title = 'The List';
  $content = ShowList();
  break;
}

function ShowList(){
 // we are showing the table, no initial actions are needed
 // so we'll jump straight into the table
 $output = '<table border="1" width="700" cellpadding="5" style="border-collapse: collapse">
    <tr>
     <th  width="50">ItemID</th>
     <th>Item Name</th>
     <th>Owner</th>
     <th  width="70">Quantity</th>
     <th  width="76">Reoccuring</th>
     <th  width="76">Purchased</th>
     <th width="76">Action</th>
    </tr>';
 // run the query, we're putting the things that aren't purchased at the top
 $query = mysql_query("select * from thelist order by purchased asc");
 // loop all the records
 while($row = mysql_fetch_assoc($query)){
  $output .= '<tr>';
  $output .= '<td>' . $row['itemid'] .'</td>';
  $output .= '<td>' . $row['name'] . '</td>';
  $output .= '<td>';
  // if this field is empty, its not for anyone specific
  if(empty($row['owner'])){
   $output .= "Everyone";
  }else{
   $output .= $row['owner'];
  }
  $output .= '</td>';
  $output .= '<td>' . $row['quantity'] .'</td>';
  $output .= '<td>';
  if($row['reoccuring'] == 1){
   $output .= "yes";
  }else{
   $output .= "no";
  }
  $output .= '</td>';
  $output .= '<td>';
  if($row['purchased'] == 1){
   $output .= "yes";
  }else{
   $output .= "no";
  }
  $output .= '</td>';
  $output .= '<td width="76"><a href="?action=edititem&itemid=' . $row['itemid'] . '">Edit</a> - ';
  // need to add slashes as we're dealing with javascript here
  $output .= '<a href="javascript:checkDelete(\'' . addslashes($row['name']) . '\',' . $row['itemid'] . 
');">Delete</a></td>';
  $output .= '</tr>';
 }
 $output .= '</table>';
 return $output;
}
function DeleteItem(){
 // in the query we convert it to an integer to prevent any injection
 if(mysql_query("delete from thelist where itemid='".(int)$_GET['itemid']."'")){
  $output = '<b>Item deleted successfully!</b><br/><br/>';
 }else{
  $output = '<b>An Error Occurred: ' . mysql_error() . '</b><br><br>';
 }
 // show the list
 $output .= ShowList();
 return $output;
}
function ManageItem(){
 if(isset($_GET['itemid'])){
  // if we're editing we need to grab the stuff from the database
  // convert to integer (if its not a number it'll become zero
  $itemid= (int)$_GET['itemid'];
  $query = mysql_query("select * from thelist where itemid='" . $itemid . "' limit 1");
  $row = mysql_fetch_assoc($query);
 }else{
  // set up blank array
  $row['itemid'] = '';
  $row['name'] = '';
  $row['purchased'] = '';
  $row['owner'] = '';
  $row['reoccuring'] = '';
  $row['quantity'] = '';
 }
 // we have lots of HTML here, so we're breaking out of PHP, but we need to stop it outputting
 // so we'll use output buffering and capture the result
 ob_start();
 ?>
 <form method="POST" action="<?=$_SEVER['PHP_SELF']?>?action=save">
 <?php
 if(isset($_GET['itemid']) && $itemid > 0){
  echo '<input type="hidden" name="itemid" value="' . $itemid . '">';
 }
 ?>
 <table border="0" width="450">
  <tr>
   <td><font size="2">Item Name:</font></td>
   <td><input type="text" name="name" size="20" value="<?php echo htmlspecialchars($row['name']); 
?>"></td>
  </tr>
  <tr>
   <td><font size="2">For someone specific? </font><font size="1">(leave blank if not)</font></td>
   <td><input type="text" name="owner" size="20"  value="<?php echo htmlspecialchars($row['owner']); 
?>"></td>
  </tr>
  <tr>
   <td><font size="2">Quantity: </font></td>
   <td><input type="text" name="quantity" size="4"   value="<?php echo $row['quantity']; ?>"></td>
  </tr>
  <tr>
   <td><font size="2">Reoccuring?</font></td>
   <td><input type="checkbox" name="reoccuring" value="yes"   <?php if($row['reoccuring'] == 1){echo 
"checked"; } ?>></td>
  </tr>
  <tr>
   <td height="23"><font size="2">Purchased already?</font></td>
   <td height="23">
   <input type="checkbox" name="purchased" value="yes" <?php if($row['purchased']== 1){echo "checked"; } 
?>></td>
  </tr>
  <tr>
   <td colspan="2">
   <p align="center">
   <input type="submit" value="Add/Edit Item" name="submit"></td>
  </tr>
 </table>
 </form>
 <?php
 // get output buffer and then clean it up
 $output = ob_get_contents();
 ob_end_clean();
 return $output;
}
function SaveItem(){
 if(isset($_POST['itemid'])){
  // we are updating
  // using our custom db escape function
  $query = 'update `thelist` set ';
  $query .= " `name`='".db_escape($_POST['name'])."', ";
  $query .= " `owner`='".db_escape($_POST['owner'])."', ";
  $query .= " `quantity`='".(int)$_POST['quantity']."', ";
  if($_POST['reoccuring'] == 'yes'){
   $query .= " `reoccuring`='1', ";
  }else{
   $query .= " `reoccuring`='0', ";
  }
  if($_POST['purchased'] == 'yes'){
   $query .= " `purchased`='1' ";
  }else{
   $query .= " `purchased`='0' ";
  }
  $query .= " where itemid='".(int)$_POST['itemid']."' limit 1";
  if(mysql_query($query)){
   $output = '<b>Item updated successfully!</b><br/><br/>';
  }else{
   $output = '<b>An Error Occurred: ' . mysql_error() . '</b><br><br>';
  }
 }else{
  // we are adding
  // we are updating
  // using our custom db escape function
  $query = "insert into `thelist` (`name`,`owner`,`quantity`,`reoccuring`,`purchased`) values ";
  $query .= "('".db_escape($_POST['name'])."','".db_escape($_POST['owner'])."','".(int)$_POST['quantity']."'";
  if($_POST['reoccuring'] == 'yes'){
   $query .= "'1', ";
  }else{
   $query .= "'0', ";
  }
  if($_POST['purchased'] == 'yes'){
   $query .= "'1' ";
  }else{
   $query .= "'0' ";
  }
  $query .= " limit 1";
  if(mysql_query($query)){
   $output = '<b>Item added successfully!</b><br/><br/>';
  }else{
   $output = '<b>An Error Occurred: ' . mysql_error() . '</b><br><br>';
  }
 }
 // show the list
 $output .= ShowList();
 return $output;
}
function PrintList(){
 // this is a printer version, so we cna just echo it straight out
 $q = mysql_query("select * from thelist where purchased=0 order by owner asc");
 echo "<table border=0>";
 while($row = mysql_fetch_assoc($q)){
  echo "<tr>";
  echo "<td>";
  echo $row['quantity'] . " x";
  echo "</td><td>";
  echo $row['name'];
  if(!empty($row['owner'])){
   echo " for ".$row['owner'];
  }
  echo "</td></tr>";
 }
 echo "</table>";
 die();
}
function db_escape($string){
 if(function_exists('mysql_real_escape_string')){
  return mysql_real_escape_string($string);
 }else{
  return mysql_escape_string($string);
 }
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
 <head>
  <title> The Interspire Shopping List </title>
  <link rel="shortcut icon" href="cart.png" type="image/png">
 </head>
<style>
body,table, td, th, tr {
font-family: georgia;
font-size: 10pt;
}
</style>
<script>
function checkDelete(name,itemid){
if(confirm('Are you sure you want to delete "'+ name +'" ?')){
 window.location = '?action=delete&itemid='+itemid;
}else{
}
}
</script>
<body>
<h1>The Shopping List - <?=$title?></h1>
<a href="<?=$_SERVER['PHP_SELF']?>">List Home</a>  |  <a href="?action=new">Add Item</a>  | <a href="?action=print">Printer 
Version</a><br><br>
<?=$content?>
</body>
</html>

The schema for the SQL table is;

CREATE TABLE `thelist` (
`itemid` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`owner` varchar(255) default '',
`purchased` int(11) NOT NULL default '0',
`quantity` int(11) NOT NULL default '0',
`reoccuring` int(11) NOT NULL default '0',
PRIMARY KEY(`itemid`)
) TYPE=MyISAM;

Any help would be much appriciated.

Cheers,

ZeNLoK

[gptsven]

atleast put it in a codebox omg

[Jarryd]

Edited, and put in codebox's.

[VPmase]

Anything like "(int)$_POST['quantity']" should be "intval($_POST['quantity'])"
That was one of them in the sql query that I believe goes haywire.
Here is another one: (int)$_POST['itemid'] should be changed to intval($_POST['itemid'])

[xPlozion]

i was wondering what the (int) was all about last night...

[woiwky]

You can type cast in PHP, so (int)$var and intval($var) are the same. I think using a type cast is slightly faster, though, since there's no overhead from a function call.

Anyway, I'm fairly certain the problem is this part:

$query = "insert into `thelist` (`name`,`owner`,`quantity`,`reoccuring`,`purchased`) values ";
  $query .= "('".db_escape($_POST['name'])."','".db_escape($_POST['owner'])."','".(int)$_POST['quantity']."'";
  if($_POST['reoccuring'] == 'yes'){
   $query .= "'1', ";
  }else{
   $query .= "'0', ";
  }
  if($_POST['purchased'] == 'yes'){
   $query .= "'1' ";
  }else{
   $query .= "'0' ";
  }
  $query .= " limit 1"; 

LIMIT can't be used in an INSERT query, so you need to remove that line.

[xPlozion]

You can type cast in PHP, so (int)$var and intval($var) are the same. I think using a type cast is slightly faster, though, since there's no overhead from a function call.

Anyway, I'm fairly certain the problem is this part:

$query = "insert into `thelist` (`name`,`owner`,`quantity`,`reoccuring`,`purchased`) values ";
  $query .= "('".db_escape($_POST['name'])."','".db_escape($_POST['owner'])."','".(int)$_POST['quantity']."'";
  if($_POST['reoccuring'] == 'yes'){
   $query .= "'1', ";
  }else{
   $query .= "'0', ";
  }
  if($_POST['purchased'] == 'yes'){
   $query .= "'1' ";
  }else{
   $query .= "'0' ";
  }
  $query .= " limit 1"; 

LIMIT can't be used in an INSERT query, so you need to remove that line.

lol, nice find... i always overlook the minor things, and that kills me sometimes when debugging my own work...