naykidd Posted July 5, 2009 Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/164881-sql-syntax-error/ Share on other sites More sharing options...
Philip Posted July 6, 2009 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/164881-sql-syntax-error/#findComment-869473 Share on other sites More sharing options...
corbin Posted July 6, 2009 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/164881-sql-syntax-error/#findComment-869475 Share on other sites More sharing options...
Philip Posted July 6, 2009 Share Posted July 6, 2009 Very true Quote Link to comment https://forums.phpfreaks.com/topic/164881-sql-syntax-error/#findComment-869477 Share on other sites More sharing options...
naykidd Posted July 6, 2009 Author Share Posted 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! Quote Link to comment https://forums.phpfreaks.com/topic/164881-sql-syntax-error/#findComment-869484 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.