SQL Syntax Error

naykidd · July 5, 2009

Hi, i've recently been doing a PHP tutorial online and was looking for some help, im trying to teach myself PHP and all was going well until i tried following the instructions to make a "submit" form..

I set up a local server using Wamp, and have followed all the instructions best i can, but i'm receiving the error as follows:

"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 'Order) values ('test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test')' at line 1"

I've searched this site, and have desk checked my code for hours and hours, revisiting the tutorials and im not sure whats wrong, if anyone could help i'd greatly appreciate it...

The following is my code at the moment. The response i get is always the same no matter what i put in the fields, but i just used test in this case. The table in SQL manager has identical field names, and the table shows, and deletes currently existing records, but i cant update them or add new ones or i get the error above, so im assuming the error lies around the "submit" button.

Thanks again for any help or advice

<?php
require_once("file:///C:\wamp\www\Site\Local Root\Test\Connections\connection.php"); // database connection

///////////////////////////////////////////////// variables
$Product = $_POST['Product'];
$Make = $_POST['Make'];
$Model = $_POST['Model'];
$Year = $_POST['Year'];
$Fitment = $_POST['Fitment'];
$Part = $_POST['Part'];
$Location = $_POST['Location'];
$Price = $_POST['Price'];
$Order = $_POST['Order'];
$Submit = $_POST['Submit'];
$del = $_GET['del'];
$upd = $_GET['upd'];
$update_id = $_POST['update_id'];

//////////////////////////////////////
$query = sprintf("SELECT * FROM parts where ID='$upd'"); 
$result = @mysql_query($query); //tells the database
$rowUpdate = mysql_fetch_array($result); //fetches the result
//////////////////////////////////////

/////////////////////////////// The following checks if there is stuff in every field and not in the hidden update field

if ($Submit && $Product && $Make && $Model && $Year && $Fitment && $Part && $Location && $Price && $Order && ! $update_id){

/////////////////////////////////////////////////////// this inserts it
$query 		= sprintf("INSERT INTO parts (Product, Make, Model, Year, Fitment, Part, Location, Price, Order) values ('$Product', '$Make', '$Model', '$Year', '$Fitment', '$Part', '$Location', '$Price', '$Order')");
		  mysql_query($query)or die (mysql_error());

}elseif($Submit && $update_id){ ////////////////Otherwise
////////////////////////////////////////// UPDATE
$query = sprintf("UPDATE parts set Product='$Product', Make='$Make', Model='$Model', Year='$Year', Fitment='$Fitment', Part='$Part', Location='$Location', Price='$Price', Order='$Order' where ID = '$update_id'");
$result = @mysql_query($query);
}

///delete a record if
if ($del){

//////////////////////////////////////////////// This is the code for deleting a line
$query 		= sprintf("DELETE FROM parts where ID='$del'");
		  mysql_query($query)or die (mysql_error());
}

////////////////////////////////////// Then show it
$query = sprintf("SELECT * FROM parts"); //selects all data from the database
$result = @mysql_query($query); //tells the database
$row = mysql_fetch_array($result); //fetches the result
/////////////////////////////////////

?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p> </p>
<p> </p>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
  <p> </p>
  <p> </p>
  <table width="982" border="1">
    <tr>
      <td width="128"><div align="center">Product</div></td>
      <td width="128"><div align="center">Make</div></td>
      <td width="127"><div align="center">Model</div></td>
      <td width="108"><div align="center">Year</div></td>
      <td width="123"><div align="center">Fitment</div></td>
      <td width="123"><div align="center">Part</div></td>
      <td width="96"><div align="center">Location</div></td>
      <td width="55"><div align="center">Price</div></td>
      <td width="170"><div align="center">Order</div></td>
    </tr>
    <tr>
      <td><label>
        <input type="text" name="Product" id="Product" value="<?php echo $rowUpdate['Product']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Make" id="Make" value="<?php echo $rowUpdate['Make']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Model" id="Model" value="<?php echo $rowUpdate['Model']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Year" id="Year" value="<?php echo $rowUpdate['Year']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Fitment" id="Fitment" value="<?php echo $rowUpdate['Fitment']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Part" id="Part" value="<?php echo $rowUpdate['Part']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Location" id="Location" value="<?php echo $rowUpdate['Location']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Price" id="Price" value="<?php echo $rowUpdate['Price']; ?>" />
      </label></td>
      <td><label>
        <input type="text" name="Order" id="Order" value="<?php echo $rowUpdate['Order']; ?>" />
      </label></td>
    </tr>
    <tr>
      <td height="48" colspan="9"><label>
        <div align="center">
          <label>
          <input type="reset" name="Reset" id="Reset" value="Reset" />
          </label>
          <input type="submit" name="Submit" id="Submit" value="Submit" />
          <input name="Submit" type="hidden" id="Submit" value="1" />
          <input name="update_id" type="hidden" id="update_id" value="<?php echo $rowUpdate['ID']; ?>" />
      </div></td>
      <td></label></td>
    </tr>
  </table>
  <p> </p>
</form>
<p>
  <label></label></p>
<table width="1247" height="76" border="1">
  <tr>
    <td width="43">Edit</td>
    <td width="43" height="42"><div align="center">ID</div></td>
    <td width="109"><div align="center">Product</div></td>
    <td width="114"><div align="center">Make</div></td>
    <td width="114"><div align="center">Model</div></td>
    <td width="96"><div align="center">Year</div></td>
    <td width="97"><div align="center">Fitment</div></td>
    <td width="89"><div align="center">Part</div></td>
    <td width="69"><div align="center">Location</div></td>
    <td width="47"><div align="center">Price</div></td>
    <td width="175"><div align="center">Order</div>
    <div align="center"></div></td>
    <td width="175"> </td>
  </tr>
  
<?php do {  ?>
  <tr>
    <td><label>
      <div align="center">
        <input type="button" name="Update" id="Update" value="Update" onclick="document.location.href='cpd.php?upd=<?php echo $row['ID'] ?>'" />
      </div>
    </label></td>
    <td height="25"><?php echo $row['ID']; ?></td>
    <td><?php echo $row['Product']; ?></td>
    <td><?php echo $row['Make']; ?></td>
    <td><?php echo $row['Model']; ?></td>
    <td><?php echo $row['Year']; ?></td>
    <td><?php echo $row['Fitment']; ?></td>
    <td><?php echo $row['Part']; ?></td>
    <td><?php echo $row['Location']; ?></td>
    <td><?php echo $row['Price']; ?></td>
    <td><?php echo $row['Order']; ?></td>
    <td>
      <input type="button" name="Button" id="Button" value="Delete" onClick="document.location.href='cpd.php?del=<?php echo $row['ID'] ?>'" />      </td>
  </tr>
<?php }while ($row = mysql_fetch_array($result));   ?>
</table>
</body>
</html>

Philip · July 6, 2009

At a quick glance it looks like your column names (some, not all) are reserved words. You should encase them in backticks (`)

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

corbin · July 6, 2009

Better than encasing them in backticks, you should just not use reserved words.

Backticks break compatibility with other SQL servers.

But if you have other code dependent on certain column names, renaming them might not be possible.

Philip · July 6, 2009

Very true

naykidd · July 6, 2009

I tried with the backticks and was unsuccessful, however changed the column name "order" and this appears to have fixed the problem, after my many hours of trawling through code before...

The tutorial never mentioned a problem with naming the column headings, when it said syntax i simply assumed id missed a comma or a bracket somewhere, not the words i was using.

Good to know!

Next is creating a PHP search box, i've seen a tutorial on the forums here so i think ill go away and have a read of that and get some more done...

Many thanks!

Sign In

SQL Syntax Error

Recommended Posts

naykidd

Link to comment

Share on other sites

Philip

Link to comment

Share on other sites

corbin

Link to comment

Share on other sites

Philip

Link to comment

Share on other sites

naykidd

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information