Can you run an SQL Update with an array?

[Jaxel]

Okay... I have an array with the following keys and values:

// [$key] => $value
[SIZE=4][FONT=Times New Roman]Array ([/FONT][/SIZE]
	[1] => 1
	[2] => 10 
	[3] => 1 
	[4] => 20 
	[5] => 1
)

I would like to run an update on SQL, and set each key (category_id) to the value (category_order). Basically an arrayed version of the following:

UPDATE EWRmedio_category
SET category_order = $value
WHERE category_id = $key

Yes, I know I could run a foreach loop, and update each individually, but that would add 5 queries, and possibly more as I add more categories. I like to do things as efficiently as possible. Now if the keys and values were in their own arrays ($keys and $values, respectively), would it be possible to run this update code using the arrays?

 

[Indigo]

I don't think Zend_Db has this kind of functionality built-in, but you could generate some SQL like...

UPDATE EWRmedio_category
SET category_order = CASE category_id WHEN $id1 THEN $order1 WHEN $id2 THEN $order2 (etc.) ELSE category_order END

http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html