mySQL syntax error but can't see how

[galahad2]

Hi, getting an annoying mySQL syntax error when I run a query to a db, but the syntax looks fine as far as I can tell:

Error is: 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 'index = '2'' at line 1

The PHP code is:

<?php

include ('inc/dbconnect.php');

if($_POST['specialoffers_updatetable'])
{
// Get the search variable from URL
$title1 =@$_POST['_Index'];

$title2 =@$_POST['_Category'];
$title3 =@$_POST['_CompanyHotel'];
$title4 =@$_POST['_Location'];
$title5 =@$_POST['_Offer'];
$title6 =@$_POST['_Price'];
$title7 =@$_POST['_OfferEnds'];
$title8 =@$_POST['_Mobile'];

$trimmed2 = trim($title2);
$trimmed3 = trim($title3);
$trimmed4 = trim($title4);
$trimmed5 = trim($title5);
$trimmed6 = trim($title6);
$trimmed7 = trim($title7);
$trimmed8 = trim($title8);

// Build SQL Query
$query = "UPDATE specialofferstable SET category = '$trimmed2', company_hotel = '$trimmed3', location = '$trimmed4', offer = '$trimmed5', price = '$trimmed6', offerends = '$trimmed7', mobile = '$trimmed8' WHERE index = '$title1'"; // specify the table and field names for the SQL query
}
if($result = mysql_query($query))
{
//go to the new member confirmation page
header('location: admin_confirmed.php');
exit;
}
else
{
echo "ERROR: ".mysql_error();
}

?>

So the code runs but it seems to have a problem with that query. It's getting the data okay from the form on the previous page but just doesn't seem to want to make the edit of the db.

Any ideas?

[mack]

$query = "UPDATE specialofferstable SET category = '$trimmed2', company_hotel = '$trimmed3', location = '$trimmed4', offer = '$trimmed5', price = '$trimmed6', offerends = '$trimmed7', mobile = '$trimmed8' WHERE index = '$title1'";

You could try...

$query = mysql_query("UPDATE specialofferstable SET category = '$trimmed2', company_hotel = '$trimmed3', location = '$trimmed4', offer = '$trimmed5', price = '$trimmed6', offerends = '$trimmed7', mobile = '$trimmed8' WHERE index = '$title1')";

Mack.

[dublinmike]

[index] is a reserved word so it's probably not an ideal column name. Try enclosing the column name in backquotes, e.g. ... WHERE `index` = ...

[galahad2]

That worked perfectly- many thanks for that.

[sonjay]

Or better yet, change that column name 'index' to something else. You're going to keep forgetting the backticks in other queries. I avoid using reserved words as column names, period.

[nrobidoux]

If I may offer some off topic advice which may help your coding.... you can make your code a bit more readable and shorter if you did something like the following:

if(!empty($_POST['specialoffers_updatetable']))
  foreach($_POST as $k => $v)
   $_POST[$k] = mysql_real_escape_string(trim($v));

If you only wanted to loop through a group of POST variables you could group them into an array by altering their name in the input html tag. I forgot your names but it could be something like <input name="grp['title']". Instead of $_POST in foreach it would be $_POST['grp'].

That way in your query you could use readable names instead of trimmed0 - 99.

There's probably a faster way to walk through arrays but I like foreach.