Jump to content


Check out our Community Blogs

makamo66

Member Since 12 May 2010
Offline Last Active Jun 22 2010 05:15 PM
-----

Posts I've Made

In Topic: MySQL Query Using SET and IN Clauses with an Array

05 June 2010 - 02:48 PM

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.

In Topic: MySQL Query Using SET and IN Clauses with an Array

05 June 2010 - 02:25 PM

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.

In Topic: MySQL Query Using SET and IN Clauses with an Array

05 June 2010 - 11:36 AM

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'

In Topic: MySQL Query Using SET and IN Clauses with an Array

05 June 2010 - 11:21 AM

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

In Topic: MySQL Query Using SET and IN Clauses with an Array

05 June 2010 - 10:41 AM

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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download