12 replies to this topic

[makamo66]

I am trying to do an UPDATE with the SET clause:

UPDATE temp_cart SET quantity IN (7, 6) WHERE product_id IN ( 2, 4 ) AND user_id = '" . $_SESSION['UID'] . "'

I know that SET is used only with an equal sign like this:

UPDATE temp_cart SET quantity = 6 WHERE product_id IN ( 2, 4 ) AND user_id = '" . $_SESSION['UID'] . "'

However, I need a way to set the quantity column to more than one value so I want to use the IN clause. I can't just run the query twice because in reality the query is using variables with imploded values and not simply the integers shown here. The query actually looks like this:

$id_nums = implode(", ", $productId2);
$qty_nums = implode(", ", $qtyBox2);
UPDATE temp_cart SET quantity IN ($qty_nums) WHERE product_id IN ($id_nums) AND user_id = '" . $_SESSION['UID'] . "'

I am using MySQL version 5.0. The error message I get is fairly non-descript. It is merely:

Error occured while inserting values into table: 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 'IN (7, 6) WHERE product_id IN ( 2, 4 ) AND user_id = '20100604164333'' at line 1

[WingedPanther]

Wait, so you want to set quantity to two different values at the same time? You can't do that. quantity has to be set to a specific value in each record. You either have to specify your where clause better so you can set it with two update statements.

[makamo66]

Tried this:
INSERT INTO temp_cart (quantity, product_id) VALUES (8,6),(2,5) ON DUPLICATE KEY UPDATE product_id = (2,5) WHERE user_id = '" . $_SESSION['UID'] . "'

and this
INSERT INTO temp_cart (quantity, product_id) VALUES (($qty_nums),($id_nums)) ON DUPLICATE KEY UPDATE product_id = VALUES($id_nums)

but neither one worked. Any ideas?

[makamo66]

Tried the following but it didn't work either:
$i=0;
while ($i<=count($qty_nums)){
$i++;
($mysqli->query("UPDATE temp_cart SET quantity = '$qty_nums' AND product_id = '$id_nums' WHERE user_id = '" . $_SESSION['UID'] . "' "));
}

[WingedPanther]

Are you getting error messages, or are you just not getting the data set the way you expected? You are providing VERY little code to work with, and no information about the database structure or current contents.

[makamo66]

This one worked

$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN 2 THEN 7 "
. " WHEN 4 THEN 6 "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";

However when I tried to use variable names for the arrays it didn't work any more. I tried the following with and without a while loop and neither one worked:

$i=0;
while ($i<=count($qty_nums)){
$i++;
$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN ($id_nums) THEN ($qty_nums) "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";

$ret3 = $mysqli->query($sql);
}

The error message was this:

Error occured while inserting values into table: Operand should contain 1 column(s)

[WingedPanther]

Try this:

Also, you may want to actually display the query to the screen so you can verify that you're sending what you think you're sending.

$i=0; 
while ($i<=count($qty_nums)){
$i++;
$sql = "UPDATE temp_cart SET quantity = "
     .        " ( CASE product_id " 
     .               " WHEN (".$id_nums.") THEN (".$qty_nums.") "
     .          " END ) "
     . " WHERE user_id = '" . $_SESSION['UID'] . "' ";
	 
$ret3 = $mysqli->query($sql);	 
}

[makamo66]

I tried the following which didn't work:

$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN (".$id_nums.") THEN (".$qty_nums.") "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";


If you do echo $sql you can see how it is being rendered:

UPDATE temp_cart SET quantity =
( CASE product_id
WHEN (2, 3) THEN (1, 2)
END )
WHERE user_id = '20100605131139'

The error message is: Error occured while inserting values into table: Operand should contain 1 column(s)

This one works but I don't know how to make it do this with $id_nums and $qty_nums:

$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN 2 THEN 7 "
. " WHEN 4 THEN 6 "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";

[makamo66]

This one almosts works but not quite. It doesn't use imploded arrays. It updates one row in the table but no more than that.

$productId2 = $_SESSION['selectedProducts'];

$qtyBox2 = $_SESSION['qtyBox2'];

for ($i=0; $i<count($productId2); $i++) {
$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN (".$productId2[$i].") THEN (".$qtyBox2[$i].") "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";
}
$ret3 = $mysqli->query($sql);

It updates just one row and resets the other row to zero.

This is the output of echo $sql:

UPDATE temp_cart SET quantity = ( CASE product_id WHEN (3) THEN (5) END ) WHERE user_id = '20100605133406'

[makamo66]

The following code produces the right statement when I use echo $sql but it doesn't update the table the way that the statement would appear to.

$productId2 = $_SESSION['selectedProducts'];

$qtyBox2 = $_SESSION['qtyBox2'];

for ($i=0; $i<count($productId2); $i++) {
$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN (".$productId2[$i].") THEN (".$qtyBox2[$i].") "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";
$ret3 = $mysqli->query($sql);

echo $sql outputs the following:

UPDATE temp_cart SET quantity = ( CASE product_id WHEN (2) THEN (1) END ) WHERE user_id = '20100605161019' UPDATE temp_cart SET quantity = ( CASE product_id WHEN (3) THEN (6) END ) WHERE user_id = '20100605161019'

The echo $sql statement is completely correct but it doesn't update the table the way it says it will. It updates only with the last statement and doesn't apply the first statement to the table. I can feel I'm getting close but still so far away.

[WingedPanther]

You CANNOT do multiple values. EVER! In every instance, you have to work with explicit data types, explicit data, etc. Often, this means you will need to process the data before you hand it to the SQL, as you are discovering.

[makamo66]

I found the solution! Just need to add an else clause and now it works. This is the code:

$productId2 = $_SESSION['selectedProducts'];
$qtyBox2 = $_SESSION['qtyBox2'];

for ($i=0; $i<count($productId2); $i++) {
$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN (".$productId2[$i].") THEN (".$qtyBox2[$i].") "
. " ELSE quantity "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";
$ret3 = $mysqli->query($sql);
}
Thanks for all of the help; I wouldn't have been able to solve this without the forums.