homepage Welcome to WebmasterWorld Guest from 54.166.123.2
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mySQL syntax error but can't see how
galahad2




msg:3988713
 11:09 am on Sep 13, 2009 (gmt 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?

 

mack




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

$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




msg:3988879
 9:04 pm on Sep 13, 2009 (gmt 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` = ...

galahad2




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

That worked perfectly- many thanks for that.

sonjay




msg:3989141
 12:14 pm on Sep 14, 2009 (gmt 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.

nrobidoux




msg:3989593
 7:51 am on Sep 15, 2009 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved