title: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server - Limit 1
body: <p>I am trying to work with this simple DB. However I keep getting Error - "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"</p>
  
  <p>I am using MYSQL 5.0 and have tried different ways to display $query.</p>
  
  <p>Any ideas would be great.  Also want to change the quantity to a drop down menu but unsure how to do that and still store the info in Database.</p>
  
  <pre><code>&lt;?php
  
  $hostname     = 'localhost';
  $username     = 'username';
  $password     = 'password';
  $databasename = 'database';
  
  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 = '&lt;table border="1" width="700" cellpadding="5" style="border-collapse: collapse"&gt;
                  &lt;tr&gt;
                      &lt;th  width="50"&gt;ItemID&lt;/th&gt;
                      &lt;th&gt;Item Name&lt;/th&gt;
                      &lt;th&gt;Owner&lt;/th&gt;
                      &lt;th  width="70"&gt;Quantity&lt;/th&gt;
                      &lt;th  width="76"&gt;Reoccuring&lt;/th&gt;
                      &lt;th  width="76"&gt;Purchased&lt;/th&gt;
                      &lt;th width="76"&gt;Action&lt;/th&gt;
                  &lt;/tr&gt;';
  
      // 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 .= '&lt;tr&gt;';
          $output .= '&lt;td&gt;' . $row['itemid'] .'&lt;/td&gt;';
          $output .= '&lt;td&gt;' . $row['name'] . '&lt;/td&gt;';
          $output .= '&lt;td&gt;';
  
          // if this field is empty, its not for anyone specific
          if(empty($row['owner'])){
              $output .= "Everyone";
          }else{
              $output .= $row['owner'];
          }
  
          $output .= '&lt;/td&gt;';
          $output .= '&lt;td&gt;' . $row['quantity'] .'&lt;/td&gt;';
          $output .= '&lt;td&gt;';
  
          if($row['reoccuring'] == 1){
              $output .= "yes";
          }else{
              $output .= "no";
          }
          $output .= '&lt;/td&gt;';
          $output .= '&lt;td&gt;';
  
          if($row['purchased'] == 1){
              $output .= "yes";
          }else{
              $output .= "no";
          }
          $output .= '&lt;/td&gt;';
          $output .= '&lt;td width="76"&gt;&lt;a href="?action=edititem&amp;itemid=' . $row['itemid'] . '"&gt;Edit&lt;/a&gt; - ';
  
          // need to add slashes as we're dealing with javascript here
          $output .= '&lt;a href="javascript:checkDelete(\'' . addslashes($row['name']) . '\',' . $row['itemid'] . ');"&gt;Delete&lt;/a&gt;&lt;/td&gt;';
          $output .= '&lt;/tr&gt;';
      }
      $output .= '&lt;/table&gt;';
  
      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 = '&lt;b&gt;Item deleted successfully!&lt;/b&gt;&lt;br/&gt;&lt;br/&gt;';
      }else{
          $output = '&lt;b&gt;An Error Occurred: ' . mysql_error() . '&lt;/b&gt;&lt;br&gt;&lt;br&gt;';
      }
      // 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();
      ?&gt;
      &lt;form method="POST" action="&lt;?=$_SEVER['PHP_SELF']?&gt;?action=save"&gt;
      &lt;?php
  
      if(isset($_GET['itemid']) &amp;&amp; $itemid &gt; 0){
          echo '&lt;input type="hidden" name="itemid" value="' . $itemid . '"&gt;';
      }
  
      ?&gt;
      &lt;table border="0" width="450"&gt;
          &lt;tr&gt;
              &lt;td&gt;&lt;font size="2"&gt;Item Name:&lt;/font&gt;&lt;/td&gt;
              &lt;td&gt;&lt;input type="text" name="name" size="20" value="&lt;?php echo htmlspecialchars($row['name']); ?&gt;"&gt;&lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr&gt;
              &lt;td&gt;&lt;font size="2"&gt;For someone specific? &lt;/font&gt;&lt;font size="1"&gt;(leave blank if not)&lt;/font&gt;&lt;/td&gt;
              &lt;td&gt;&lt;input type="text" name="owner" size="20"  value="&lt;?php echo htmlspecialchars($row['owner']); ?&gt;"&gt;&lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr&gt;
              &lt;td&gt;&lt;font size="2"&gt;Quantity: &lt;/font&gt;&lt;/td&gt;
              &lt;td&gt;&lt;input type="text" name="quantity" size="4"   value="&lt;?php echo $row['quantity']; ?&gt;"&gt;&lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr&gt;
              &lt;td&gt;&lt;font size="2"&gt;Reoccuring?&lt;/font&gt;&lt;/td&gt;
              &lt;td&gt;&lt;input type="checkbox" name="reoccuring" value="yes"   &lt;?php if($row['reoccuring'] == 1){echo "checked"; } ?&gt;&gt;&lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr&gt;
              &lt;td height="23"&gt;&lt;font size="2"&gt;Purchased already?&lt;/font&gt;&lt;/td&gt;
              &lt;td height="23"&gt;
              &lt;input type="checkbox" name="purchased" value="yes" &lt;?php if($row['purchased']== 1){echo "checked"; } ?&gt;&gt;&lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr&gt;
              &lt;td colspan="2"&gt;
              &lt;p align="center"&gt;
              &lt;input type="submit" value="Add/Edit Item" name="submit"&gt;&lt;/td&gt;
          &lt;/tr&gt;
      &lt;/table&gt;
      &lt;/form&gt;
      &lt;?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 = '&lt;b&gt;Item updated successfully!&lt;/b&gt;&lt;br/&gt;&lt;br/&gt;';
          }else{
              $output = '&lt;b&gt;An Error Occurred: ' . mysql_error() . '&lt;/b&gt;&lt;br&gt;&lt;br&gt;';
          }
  
      }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 = '&lt;b&gt;Item added successfully!&lt;/b&gt;&lt;br/&gt;&lt;br/&gt;';
          }else{
              $output = '&lt;b&gt;An Error Occurred: ' . mysql_error() . '&lt;/b&gt;&lt;br&gt;&lt;br&gt;';
          }
      }
      // 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 "&lt;table border=0&gt;";
      while($row = mysql_fetch_assoc($q)){
          echo "&lt;tr&gt;";
          echo "&lt;td&gt;";
          echo $row['quantity'] . " x";
          echo "&lt;/td&gt;&lt;td&gt;";
          echo $row['name'];
          if(!empty($row['owner'])){
              echo " for ".$row['owner'];
          }
          echo "&lt;/td&gt;&lt;/tr&gt;";
      }
      echo "&lt;/table&gt;";
  
      die();
  }
  
  function db_escape($string){
      if(function_exists('mysql_real_escape_string')){
          return mysql_real_escape_string($string);
      }else{
          return mysql_escape_string($string);
      }
  }
  ?&gt;
  &lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"&gt;
  &lt;html&gt;
   &lt;head&gt;
    &lt;title&gt; The Interspire Shopping List &lt;/title&gt;
    &lt;link rel="shortcut icon" href="cart.png" type="image/png"&gt;
   &lt;/head&gt;
  &lt;style&gt;
  body,table, td, th, tr {
  font-family: georgia;
  font-size: 10pt;
  }
  &lt;/style&gt;
  &lt;script&gt;
  function checkDelete(name,itemid){
  if(confirm('Are you sure you want to delete "'+ name +'" ?')){
      window.location = '?action=delete&amp;itemid='+itemid;
  }else{
  
  }
  }
  &lt;/script&gt;
  &lt;body&gt;
  
  &lt;h1&gt;The Shopping List - &lt;?=$title?&gt;&lt;/h1&gt;
  
  &lt;a href="&lt;?=$_SERVER['PHP_SELF']?&gt;"&gt;List Home&lt;/a&gt;  |  &lt;a href="?action=new"&gt;Add Item&lt;/a&gt;  | &lt;a href="?action=print"&gt;Printer Version&lt;/a&gt;&lt;br&gt;&lt;br&gt;
  
  &lt;?=$content?&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>