只有一个MySQL查询的两个foreach语句? [英] Two foreach statements with only one MySQL query?

问题描述

这是使用两个foreach语句并将数组放入MySQL数据库的唯一方法吗?

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

第一个将更新ot_hours字段，第二个foreach将更新lieu_hours字段.我试图将两者结合起来做一个查询，但是它不断使用错误的值进行更新.

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 :)

提前感谢所有回复

推荐答案

适用于您的情况，这是Danny改编的答案:

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

<?php

//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

?>

这篇关于只有一个MySQL查询的两个foreach语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！