[SOLVED] PHP + MySQL Update

[s.eardley]

Should this work because I get this 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 '(ExistingUser, ExistingSoftwareDetails, ExistingSoftwareEdition, SoftwareRequire' at line 1".

 

$OrderID = $_POST['OrderID'];
echo $OrderID;
echo "<br/><br/>";

$db = mysql_connect("localhost", "root", "");
mysql_select_db("mysql",$db);

$sql = "UPDATE tblorders (";

foreach($_POST as $field=>$value)
{
$sql .= "".$field.", ";
}

$sql = substr_replace($sql,"",-2);

$sql .= ") VALUES (";

foreach($_POST as $field=>$value)
{
$sql.= "'".$value."', ";
}

$sql = substr_replace($sql,"",-2);

$sql .= ") WHERE OrderID='".mysql_real_escape_string( $OrderID )."'";

$result = mysql_query($sql,$db);

[aschk]

Do echo $sql, at the bottom of your script to see what the full SQL query is. Basically I expect it to be malformed.

Post it here for us to see.

[Mchl]

I would recommend familiarising with syntax of UPDATE

 

http://dev.mysql.com/doc/refman/5.0/en/update.html

(hint: it's not the same as INSERT)

[s.eardley]

UPDATE tblorders (ExistingUser, ExistingSoftwareDetails, ExistingSoftwareEdition) VALUES ('on', 'V1', 'Single user') WHERE OrderID='7'

 

I've shortened it a little just to make it easier to look at but this is the basic format of the sql statement which is output.

[s.eardley]

Yeah I must admit my sql knowledge is fairly limited at the mo.  Working on that though! 

[aschk]

Indeed, as Mchl suggested, I suspect your syntax is wrong.

i.e. UPDATE table1 (col1) VALUES(val1)

is invalid.

 

It should be

UPDATE table1 SET col1 = val1

 

What you're doing is using the INSERT syntax for UPDATE, they're not the same

[s.eardley]

I'll give it a go right now.  Thanks a lot for the very quick replies.  I'll get back to you shortly with a result.. Hopefully a positive one 

[aschk]

For those interested the link to the UPDATE syntax is here:

http://dev.mysql.com/doc/refman/5.1/en/update.html

[s.eardley]

That's great guys.  It's working 100% now.  Thanks a lot for the quick responses.  Very much appreciated.

 

Steve