Welcome to WebmasterWorld Guest from 54.242.63.214

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mySQL syntax error but can't see how

     

galahad2

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

5+ Year Member



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

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

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



$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

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

10+ Year Member



[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

8:45 am on Sep 14, 2009 (gmt 0)

5+ Year Member



That worked perfectly- many thanks for that.

sonjay

12:14 pm on Sep 14, 2009 (gmt 0)

10+ Year Member



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

7:51 am on Sep 15, 2009 (gmt 0)

5+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month