PDA

View Full Version : SQL syntax error


Narth
07-02-2009, 03:29 PM
Keep submitting new items and receiving the error "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', 'test2', 'test3, 'test4', 'test5', 'test6', 'test7', 'test8')' at line 1"

The tests are just what i've entered into the text fields before clicking "submit"

I've desk checked the code for the last 2 hours and re-done the tutorials and still cannot find any error.. any help?

My code is deleting items, and all the items ive entered through SQL manager appear correctly, the connection to the database is fine im assuming its a syntax error

All field names in the database are identical so i cant see why it shouldn't update itself

Here's my code anyway...

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

/////////////////////////////////////////////////
$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'];

//////////////////////////////////////////////// This is the code for deleting a line
if($del){


$query = sprintf("DELETE FROM parts where ID='$del'");
mysql_query($query)or die (mysql_error());
}
//////////////////////////////// The following is the code for adding

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

$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){

echo "At least one of the fields are empty !";

}

//////////////////////////////////////
$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>&nbsp;</p>
<p>&nbsp;</p>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
<p>&nbsp;</p>
<p>&nbsp;</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" />
</label></td>
<td><label>
<input type="text" name="Make" id="Make" />
</label></td>
<td><label>
<input type="text" name="Model" id="Model" />
</label></td>
<td><label>
<input type="text" name="Year" id="Year" />
</label></td>
<td><label>
<input type="text" name="Fitment" id="Fitment" />
</label></td>
<td><label>
<input type="text" name="Part" id="Part" />
</label></td>
<td><label>
<input type="text" name="Location" id="Location" />
</label></td>
<td><label>
<input type="text" name="Price" id="Price" />
</label></td>
<td><label>
<input type="text" name="Order" id="Order" />
</label></td>
</tr>
<tr>
<td height="48" colspan="9"><label>
<div align="center">
<input type="submit" name="Submit" id="Submit" value="Submit" />
<input name="Submit" type="hidden" id="Submit" value="1" />
</div></td>
<td></label></td>
</tr>
</table>
<p>&nbsp;</p>
</form>
<p>
<label></label></p>
<table width="1017" height="76" border="1">
<tr>
<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">&nbsp;</td>
</tr>

<?php do { ?>
<tr>
<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>
Many thanks in advance

edbr
07-06-2009, 04:26 AM
instead of
file:///C:\wamp\www\Site\Local Root\Test\Connections\connection.php
try
"Connections\connection.php"

Narth
07-06-2009, 07:12 PM
Its okay all fixed!
I read up on it and turns out using "order" is a bad idea, as its a command for something else!

I've spent the last few days teaching myself to do the PHP login script, and i've also conjured up a search function, but im looking to put the results into a table, and for the life of me i can't work out the coding for it if anyone on the site could help?

My code at the moment gives out the data in a list, with a line break inbetween each of the 9 items, but i want the results to appear in a table in rows, with the correct column header, something like "while there is data in the array, create a new row, and fill it with data returned, until there is no more data" if you were to write it in psuedocode

This is my code, which also shows all the records below, i want a table to appear above it with the searched code in:

<?php
require_once("Connections/connection.php"); // database connection

error_reporting (E_ALL ^ E_NOTICE);

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


// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted.
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.

if (strlen($searchTerms) < 2) {
$error[] = "Search terms must be longer than 1 character.";
}else {
$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
}

// If there are no errors, lets get the search going.
if (count($error) < 1) {
$searchSQL = "SELECT id, Product, Make, Model, Year, Fitment, Part, OriginalPrice, Price, ToOrder FROM alfa WHERE ";

// grab the search types.
$types = array();
$types[] = isset($_GET['model'])?"`model` LIKE '%{$searchTermDB}%'":'';

$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

if (count($types) < 1)
$types[] = "`model` LIKE '%{$searchTermDB}%'"; // use the model as a default search if none are checked

$andOr = isset($_GET['matchall'])?'AND':'OR';
$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `id`"; // order by id

$searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

if (mysql_num_rows($searchResult) < 1) {
$error[] = "The search term provided {$searchTerms} gave no results. Try shortening the Model (e.g. Escort instead of Escort RS Turbo)";
}else {
$results = array(); // the result array
$i = 1;

while ($roww = mysql_fetch_assoc($searchResult)) {
$results[] = "{$i}: {$roww['Make']}<br />{$roww['Model']}<br />{$roww['Part']}<br />{$roww['Product']}<br />{$roww['Year']}<br />{$roww['Fitment']}<br />{$roww['Part']}<br />{$roww['OriginalPrice']}<br />{$roww['Price']}<br />{$roww['ToOrder']}";
$i++;
}
}
}
}

function removeEmpty($var) {
return (!empty($var));
}
?>


<!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>
<style type="text/css">
<!--
.style3 {font-size: 9}
-->
#error {
color: red;
}
</style>
</head>

<body>
<br />
<br />
<?php echo (count($error) > 0)?"The following occured:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
Search for your model of car (e.g. Escort): <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
<input type="submit" name="submit" value="Search!" />
</form>
<?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
<br />
<p>
<label></label></p>
<table width="950" height="76" border="1">
<tr>
<td width="113" height="42"><div align="center">Product</div></td>
<td width="111"><div align="center">Make</div></td>
<td width="116"><div align="center">Model</div></td>
<td width="89"><div align="center">Year</div></td>
<td width="101"><div align="center">Fitment</div></td>
<td width="82"><div align="center">Part</div></td>
<td width="87"><div align="center">OriginalPrice</div></td>
<td width="41"><div align="center">Price</div></td>
<td width="152"><div align="center">To Order</div>
<div align="center"></div></td>
</tr>

<?php do { ?>
<tr>
<td height="25"><?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['OriginalPrice']; ?></td>
<td><?php echo $row['Price']; ?></td>
<td><?php echo $row['ToOrder']; ?></td>
</tr>
<?php }while ($row = mysql_fetch_array($result)); ?>
</table>
</body>
</html>however a search for model type "156" gives the following format, but is followed by the complete table:

1: Alfa Romeo
156
BW13
Brake Pads
1998 - 2000
Front Pads
BW123
29.99
15
Call

2: Alfa Romeo
156
HH
Brake Hose
1998 - 2002
Front Hose
HH122
10
5
Call

Sure its blatently obvious what i've done wrong, but 4/5 hours of looking up tutorials and shuffling the code around is driving me round the bend!

Many thanks again