[SOLVED] sql query problem

[anfo]

Hi Guys,

I am having a problem with my sql code. I get an error message "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 'Smith St , suburb = Sydney , pcode = 2000 , ' at line 3.

 

I have tried the code below with the keyword AND in placde of the comma's, as well as AND before  mfirstname, they both produce the same error. Yes I am trying to integrate this code with php.

 

php version : 5.2.6

sql version: 5.0.51b

on windows xp.

 

I am not sure if I am trying to update too many columns at a time or what.

 

any help would be appreciated.

 

Thanks and Regards

Anfo

 

here is my sql code.

 

$query = "UPDATE members SET msurname = " . mysql_real_escape_string(stripslashes($_POST['msurname'])) . " ,

    mfirstname = " . mysql_real_escape_string(stripslashes($_POST['mfirstname'])) . " ,

  address = " . mysql_real_escape_string(stripslashes($_POST['address'])) . " ,

  suburb = " . mysql_real_escape_string(stripslashes($_POST['suburb'])) . " ,

  pcode = " . mysql_real_escape_string(stripslashes($_POST['pcode'])) . " ,

  home = " . mysql_real_escape_string(stripslashes($_POST['home'])) . " ,

    work = " . mysql_real_escape_string(stripslashes($_POST['work'])) . " ,

  mobile = " . mysql_real_escape_string(stripslashes($_POST['mobile'])) . " ,

  email = " . mysql_real_escape_string(stripslashes($_POST['email'])) . "

  WHERE member_id = $row[0] LIMIT 1";

 

 

[trq]

Depending on your data types most fields values need to be surrouned by quotes.

[anfo]

I thought they were?

 

It's my understanding that the part after the equals sign is the value but maybe i'm mistaken.

 

mfirstname = " . mysql_real_escape_string(stripslashes($_POST["mfirstname"])) . "

[PFMaBiSmAd]

The double-quotes you have in your string are around the literal text parts of the string so that the dot . concatenate operator can incorporate the values from the php function calls. Echo $query to see what it contains. Mysql string values need single-quotes around them each of them to make them into sql string data.

[aschk]

Here's a tidier way of writing what you have:

 

<?php

// Copy $_POST into $data so we still have a raw copy.
$data = $_POST;
// Stripslashes and escape for SQL.
array_walk($data, 'stripslashes');
array_walk($data, 'mysql_real_escape_string');

// Extract array values into local symbol table.
// n.b. i don't ordinarily advocate the use of this function 
// because I personally believe it faces insecurities.
extract($data); 

// Set up our SQL query string.

$query = "UPDATE members SET msurname = '{$msurname}',
		mfirstname = '{$mfirstname}',
		address = '{$address}',
		suburb = '{$suburb}',
		pcode = '{$pcode}',
		home = '{$home}',
		work = '{$work}',
		mobile = '{$mobile}',
		email = '{$email}',
		WHERE member_id = $row[0] LIMIT 1";
?>

 

Notice that the variables must be enclosed in single quotes (') because you're inserting string values into a varchar column in your database.

[PFMaBiSmAd]

There is an extra comma after the email value in that code that will generate an error. Another reason to not post "fixed" (untested) code.