mysql查询foreach,只有一个MySQL查询的两个foreach语句？

Is this the only way to use two foreach statements with arrays going into a MySQL database?

The first one will update the ot_hours field, and the second foreach will update the lieu_hours field. I tried to combine both to do one query but it kept updating with wrong values.

This is what I have right now that works but is ugly.

foreach($_POST['overtimehours'] as $key => $value) {

dbQuery("UPDATE $TABLE SET ot_hours='$value', ot_status=1, ot_submitdate='$ot_submitdate' WHERE trans_num=$key AND uid='$contextUser' AND (ot_status=0 OR ot_status=1 OR ot_status=3)");

}

foreach($_POST['lieutimehours'] as $key2 => $value2) {

dbQuery("UPDATE $TABLE SET lieu_hours='$value2', ot_status=1, ot_submitdate='$ot_submitdate' WHERE trans_num=$key2 AND uid='$contextUser' AND (ot_status=0 OR ot_status=1 OR ot_status=3)");

}

I'm sure there's much better ways to do this. This is why I'm hoping someone can help me :)

Thanks in advance for all responses

解决方案

Applied to your case, here is the adapted answer of Danny:

//first query:

$arrk = array_keys($_POST['overtimehours']);

$arrv = array_values($_POST['overtimehours']);

$id_list = implode(',', $arrk);

$whens = implode(

"\n ",

array_map(

function ($id, $value) {

return "WHEN {$id} THEN {$value}";

},

$arrk,

$arrv

)

);

$sql1 = "

UPDATE $TABLE

SET ot_hours = CASE trans_num

{$whens}

END,

ot_status=1,

ot_submitdate='$ot_submitdate'

WHERE id IN ({$id_list})

AND uid='$contextUser'

AND (ot_status=0 OR ot_status=1 OR ot_status=3)

";

//second query:

$arrk = array_keys($_POST['lieutimehours']);

$arrv = array_values($_POST['lieutimehours']);

$id_list = implode(',', $arrk);

$whens = implode(

"\n ",

array_map(

function ($id, $value) {

return "WHEN {$id} THEN {$value}";

},

$arrk,

$arrv

)

);

$sql2 = "

UPDATE $TABLE

SET lieu_hours = CASE trans_num

{$whens}

END,

ot_status=1,

ot_submitdate='$ot_submitdate'

WHERE id IN ({$id_list})

AND uid='$contextUser'

AND (ot_status=0 OR ot_status=1 OR ot_status=3)

";

//now use pdo to run sql1 and sql2

?>