Page 1 of 1
MySQL Query Using SET and IN Clauses with an Array
Posted: Fri Jun 04, 2010 7:08 pm
by makamo66
I am trying to do an UPDATE with the SET clause:
Code: Select all
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:
Code: Select all
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:
Code: Select all
$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
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Fri Jun 04, 2010 7:14 pm
by Eran
You can only use IN in the WHERE clause, so MySQL tells you that your usage of in the SET section is invalid. You can't run UPDATE on multiple rows with different values. The only way to achieve a similar results is to use INSERT ... ON DUPLICATE KEY UPDATE
http://dev.mysql.com/doc/refman/5.0/en/ ... icate.html
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Fri Jun 04, 2010 8:18 pm
by Benjamin
How do you plan on storing more than one value in a single field?
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Fri Jun 04, 2010 8:21 pm
by Eran
He wants to update separate rows with different values in one query, I believe
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Fri Jun 04, 2010 8:25 pm
by Benjamin
Well I suppose:
Code: Select all
UPDATE
temp_cart
SET
quantity = IF(product_id = 2, 7, 6)
WHERE
product_id IN(2, 4)
AND user_id = '" . mysql_real_escape_string($_SESSION['UID']) . "'
Certainly doesn't smell right though.
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 10:23 am
by makamo66
Tried this:
Code: Select all
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
Code: Select all
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?
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 11:27 am
by Eran
There is no WHERE clause in the INSERT .. ON DUPLICATE
you put the unique key (id) with the appropriate value in the values brackets
So, something of the form -
Code: Select all
INSERT INTO temp_cart (user_id,quantity, product_id) VALUES (2,8,6),(7,2,5) ON DUPLICATE KEY UPDATE product_id = VALUES(product_id)
You'll have to prepare your values separately than the implode you did previously
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 12:34 pm
by makamo66
Tried the following but it didn't work either:
Code: Select all
$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'] . "' "));
}
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 1:04 pm
by makamo66
If I do this:
Code: Select all
$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.
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 1:22 pm
by makamo66
This one worked
Code: Select all
$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:
Code: Select all
$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)
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 2:22 pm
by makamo66
I tried the following which didn't work:
Code: Select all
$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:
Code: Select all
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:
Code: Select all
$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN 2 THEN 7 "
. " WHEN 4 THEN 6 "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 2:38 pm
by 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.
Code: Select all
$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:
Code: Select all
UPDATE temp_cart SET quantity = ( CASE product_id WHEN (3) THEN (5) END ) WHERE user_id = '20100605133406'
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 5:27 pm
by 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.
Code: Select all
$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:
Code: Select all
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.
Re: MySQL Query Using SET and IN Clauses with an Array
Posted: Sat Jun 05, 2010 5:50 pm
by makamo66
I found the solution! Just need to add an else clause and now it works. This is the code:
Code: Select all
$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.