Welcome to WebmasterWorld Guest from 54.196.233.208

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mySQL syntax error but can't see how

     
11:09 am on Sep 13, 2009 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 14, 2008
posts: 151
votes: 0


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?

2:15 pm on Sept 13, 2009 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator mack is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 15, 2001
posts:7557
votes: 3


$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.

9:04 pm on Sept 13, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:May 4, 2002
posts:97
votes: 0


[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` = ...
8:45 am on Sept 14, 2009 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 14, 2008
posts:151
votes: 0


That worked perfectly- many thanks for that.
12:14 pm on Sept 14, 2009 (gmt 0)

Preferred Member

10+ Year Member

joined:Feb 13, 2003
posts: 590
votes: 0


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.
7:51 am on Sept 15, 2009 (gmt 0)

New User

5+ Year Member

joined:Jan 20, 2008
posts:29
votes: 0


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.