mysql UPDATE syntax

[Journeyman1900]

I am trying to put different variables into a database on the same line. when i use insert into without the where clause, the script puts the $quantity2 variable on a new line in the database. so i tried update in a few ways including using WHERE ID=’$id’. i keep getting the following error:

error uploading content: 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 ‘(Bolts_6in_zinc) VALUES (’-2’) WHERE product=‘2ply_Dom’’ at line 1

Here is the code, any help would be appreciated.

[php]<?php
require_once(‘functions.php’);
require_once(‘config.php’);
connect();
///Specs 1

$specs_product1= make_safe($_POST[‘product1’]);
$add=“INSERT INTO product_specs (product) VALUES (’$specs_product1’)”;
if (!mysql_query($add)) {
die('error uploading content: ’ . mysql_error());
}
//////////////SPEC 2
if(empty($_POST[‘quantity2’])) {}
else {
$specs_product2= make_safe($_POST[‘product2’]);
$specs_quantity2= make_safe($_POST[‘quantity2’]);
$quantity2= $specs_quantity2 * -1;

$id=mysql_insert_id();

$add=“UPDATE product_specs (” . $specs_product2 . “) VALUES (’” . $quantity2 . "’) WHERE product=’$specs_product1’ ";
if (!mysql_query($add)) {
die('error uploading content: ’ . mysql_error());
}
}

?>[/php]

[m11]

UPDATE is not the same as INSERT

[php]
“UPDATE product_specs SET " . $specs_product2 . " = '” . $quantity2 . “’ WHERE product = '” . $specs_product1 . “’”
[/php]

[Journeyman1900]

Your absolutely right. geez…the things i overlook sometimes. i really appreciate it.

i changed the code to
[php]"UPDATE product_specs SET " . $specs_product2 . "=’$quantity2’ WHERE product=’$specs_product1’ ";[/php]

and it works perfectly.

thanks a bunch!

[m11]

I just want to point out that this is a highly insecure practice you are using (setting column names from post values). If you insist on using this method I recommend you switch to mysqli or PDO and use prepared statements.

mysqli:

http://php.net/manual/en/class.mysqli.php
http://php.net/manual/en/mysqli.prepare.php

PDO:
http://php.net/manual/en/book.pdo.php
http://php.net/manual/en/pdo.prepared-statements.php

I personally like PDO

[Journeyman1900]

Yah, I havent gotten into prepared statements yet. I appreciate the link and the advice. I am just writing this code for internal (intranet) use so i have room to learn. i was not aware that there was a risk doing it this way.