MySQL Query Using SET and IN Clauses with an Array

[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

 

 

 

[dabaR]

implode() makes a string.

 

It could be that what you're looking for is:

UPDATE table SET column = '3, 2' WHERE ...

 

You certainly can't set one column to 2 values, database columns are scalars, meaning they take only one value, not a set of values.

 

If this does not answer you question, I suggest giving more context, and surrounding code.

[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?

[ignace]

You can't use a WHERE clause with the INSERT statement.

 

UPDATE temp_cart SET quantity = 7 WHERE product_id = 2 OR product_id = 4 AND user_id = ..

UPDATE temp_cart SET quantity = 6 WHERE product_id = 2 OR product_id = 4 AND user_id = ..

[makamo66]

I wrote in the original post:

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'] . "'

 

So the example you showed with two SQL statements won't work for me.

 

I'm thinking about using a while statement and iterating through a SQL statement but I tried this before and it didn't work.

[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'] . "' "));

}

[Norman262]

Try to run the following queries and let me know if it is close to what you are looking for...

 

CREATE TABLE `temp_cart` (`user_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `quantity` set('6','7','8','9') NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

INSERT INTO `temp_cart` (`user_id`, `product_id`, `quantity`) VALUES (123, 2, '8'),(456, 4, '');

 

UPDATE temp_cart SET quantity =  '7,6' WHERE product_id IN (2,4) AND user_id ='456';

[makamo66]

 

If I do this:

 

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

 

then the quantity is updated to 7 for both of the product ids.

[ignace]

 

If I do this:

 

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

 

then the quantity is updated to 7 for both of the product ids.

 

Yeah as you gave it both id's. Computers do as they are told, remember.

[Norman262]

Unless you have the data type of the quantity column as SET (*note SET is a mysql data type - don't mistake this with the UPDATE ... SET command)

 

What is the data type of your quantity column?

[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)

 

[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.

 

[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.