Update multiple rows with 1 query

[perky416]

Hi Guys,

 

Sorry this is similar to the last threat I started, however I think I made it a little confusing. I use the code below to update rows in my database depending on which check-box is ticked. Basically id like to carry out the following but with the query outside of the while loop so that only 1 query is ran rather than a query for each check-box.

 

$i = 0;
while($row = mysql_fetch_assoc($query)){
	$check = $_POST['checkbox_value'][$i];
	$value1 = $_POST['value1'][$i];

	mysql_query("UPDATE table1 SET field1='$value1' WHERE field2='$check'");

$i++; 
}

 

Iv tried using mysql_query("UPDATE table1 SET field1='$value1' WHERE field2 IN ('$check')"); outside of the while loop however I don't know how to get it working with the arrays.

 

Any help is greatly appreciated.

[AyKay47]

if you are trying to update multiple rows with one query, then you will need a while loop...perhaps im not fully understanding what you are trying to accomplish here

[perky416]

Hi AyKay47

 

Iv threw together a quick example of the form im using: www.directbullion.co.uk

 

When a user changes the value in the text box, ticks the checkbox and clicks save changes, im trying to update the database using as few a queries as possible.

 

If I use a while loop wouldn't multiple queries be submitted?

 

Thanks

[djlee]

the best way to do this is to find a unique key. For example the id of the record of the database. Pass that unique id through with the form, then you can use an insert statement and use "on duplicate key". Basically you'll never want to insert, but the unique id will always cause the on dupe key action to be called for every insert

 

for example lets say you have a database

product_id | name             | price
----------------------------------------
1              | some name     | 23
2              | another name | 32
3              | abcdefg          | 12

 

the form

<form>
<input name=product[1] />
<input name=product[2] />
<input name=product[3] />
</form>

 

the php

$insert = array();

foreach($_POST['product'] as $id => $val)
{
$insert[] = "($id, $val)";
}

mysql_query(" INSER INTO products(id,price) VALUES " . implode(',', $insert) . " ON DUPLICATE KEY UPDATE price=VALUES(price)");

[AyKay47]

If I use a while loop wouldn't multiple queries be submitted?

 

Yes, a query will be executed for each iteration of the while loop that is performed

[perky416]

Hi djlee,

 

I was using the checkbox value as the unique key.

 

Iv tried using your example however for the life of my I cant get it to work. Im only working with 2 products atm, it is inserting 2 new rows each time i submit the form, and it is updating the value in all of the previous rows apart from the original 2 that i am trying to update.

 

Do you have any idea as to what i could be doing wrong?

 

Thanks

[perky416]

Hi everyone,

 

In case anybody else comes across this post looking for the same thing, here is my solution. It only uses 1 query to update the database and after tests updating 2 columns with 100 rows each, it was found to be about 4 times faster than using a query within a while loop.

 

$i = 0;
while($row = mysql_fetch_assoc($query)){
$product_string .= " WHEN domain='" . $_POST['checkbox_value'][$i] . "' THEN '" . $_POST['product'][$i] . "'";
$price_string .= " WHEN domain='" . $_POST['checkbox_value'][$i] . "' THEN '" . $_POST['price'][$i] . "'";
$i++;
}

mysql_query("UPDATE table1 SET column1 = CASE" . $product_string . " ELSE column1 END, column2 = CASE" . $price_string . " ELSE column2 END");

 

I hope it helps.