使用数组和 IN 语句的 PHP MySQL UPDATE 查询

Question

我的这个查询在我的 PHP 脚本之一中成功运行:

$sql = "UPDATE Units SET move_end = $currentTime, map_ID = $mapID, attacking = $attackStartTime, unit_ID_affected = $enemy, updated = now() WHERE unit_ID IN ($attackingUnits);";

$attackingUnits是 1 - 100 之间任意位置的内爆数组整数。

我想做的是为 $currentTime 添加具有不同值的数组和$mapID与 $attackingUnits 的值相对应。像这样的事情:

$sql = "UPDATE Units SET move_end = " . $attackingUnits['move_end'] . ", map_ID = " . $attackingUnits['map_ID'] . ", attacking = $attackStartTime, unit_ID_affected = $enemy, updated = now() WHERE unit_ID IN ($attackingUnits);";

显然这不会按照我想要的方式工作，因为 $attackingUnits['move_end']和$attackingUnits['map_ID']只是单个值，而不是数组，但我对如何编写此查询感到困惑。我知道我可以查询 $attackingUnits 的每个元素，但这正是我想要避免的，因为我希望能够对所需数量的元素使用一次更新。

我该如何编写这个查询？

PHP 脚本的关键部分是:

$attackStartTime = time(); // the time the units started attacking the enemy (i.e. the current time)

// create a proper multi-dimensional array as the client only sends a string of comma-delimited unitID values
$data = array();

// add the enemy unit ID to the start of the selectedUnits CSV and call it allUnits. we then run the same query for all units in the selectedUnits array. this avoids two separate queries for pretty much the same columns
$allUnits = $enemy . "," . $selectedUnits;

// get the current enemy and unit data from the database
$sql = "SELECT user_ID, unit_ID, type, map_ID, moving, move_end, destination, attacking, unit_ID_affected, current_health FROM Units WHERE unit_ID IN ($allUnits);";
$result = mysqli_query($conn, $sql);

// convert the CSV strings to arrays for processing in this script
$selectedUnits = explode(',', $selectedUnits);
$allUnits = explode(',', $allUnits);

while ($row = mysqli_fetch_assoc($result)) {
    $data[] = $row;
}

$result -> close();

$increment = 0; // set an increment value outside of the foreach loop so that we can use the pointer value at each loop

// check each selected unit to see if it can validly attack the enemy unit, otherwise remove them from selected units and send an error back for that specific unit
foreach ($data as &$unit) {
// do a whole bunch of checking stuff here
}

// convert the attacking units (i.e. the unit ids from selected units which have passed the attacking tests) to a CSV string for processing on the database
$attackingUnits = implode(',', $selectedUnits);

// update each attacking unit with the start time of the attack and the unit id we are attacking, as well as any change in movement data
// HERE IS MY PROBLEMATIC QUERY
$sql = "UPDATE Units SET moving = " . $unit['moving'] . ", move_end = " . $unit['move_end'] . ", map_ID = " . $unit['map_ID'] . ", attacking = $attackStartTime, unit_ID_affected = $enemy, updated = now() WHERE unit_ID IN ($attackingUnits);";
$result = mysqli_query($conn, $sql);

// send back the full data array - should only be used for testing and not in production!
echo json_encode($data);

mysqli_close($conn);

Answer 1

好的，经过更多的网络研究，我发现了一个可以帮助我的链接:

https://stuporglue.org/update-multiple-rows-at-once-with-different-values-in-mysql/

我把他的代码更新到了mysqli，经过大量测试，它可以工作了!我现在可以通过一个查询成功更新数百行，而不是通过 PHP 发送数百个小更新。以下是我的代码的相关部分，供感兴趣的人引用:

$updateValues = array(); // the array we are going to build out of all the unit values that need to be updated

// build up the query string
$updateValues[$unit["unit_ID"]] = array(
    "moving" => $unit["new_start_time"],
    "move_end" => $unit["new_end_time"],
    "map_ID" => "`destination`",
    "destination" => $unit["new_destination"],
    "attacking" => 0,
    "unit_ID_affected" => 0,
    "updated" => "now()"
    );

// start of the query
$updateQuery = "UPDATE Units SET ";

// columns we will be updating
$columns = array("moving" => "`moving` = CASE ",
    "move_end" => "`move_end` = CASE ",
    "map_ID" => "`map_ID` = CASE ",
    "destination" => "`destination` = CASE ",
    "attacking" => "`attacking` = CASE ",
    "unit_ID_affected" => "`unit_ID_affected` = CASE ",
    "updated" => "`updated` = CASE ");

// build up each column's CASE statement
foreach ($updateValues as $id => $values) {
    $columns['moving'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['moving']) . " ";
    $columns['move_end'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['move_end']) . " ";
    $columns['map_ID'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['map_ID']) . " ";
    $columns['destination'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['destination']) . " ";
    $columns['attacking'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['attacking']) . " ";
    $columns['unit_ID_affected'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['unit_ID_affected']) . " ";
    $columns['updated'] .= "WHEN `unit_ID` = " . mysqli_real_escape_string($conn, $id) . " THEN " . mysqli_real_escape_string($conn, $values['updated']) . " ";
}

// add a default case, here we are going to use whatever value was already in the field
foreach ($columns as $columnName => $queryPart) {
    $columns[$columnName] .= " ELSE `$columnName` END ";
}

// build the WHERE part. since we keyed our updateValues off the database keys, this is pretty easy
// $where = " WHERE `unit_ID` = '" . implode("' OR `unit_ID` = '", array_keys($updateValues)) . "'";
$where = " WHERE unit_ID IN ($unitIDs);";

// join the statements with commas, then run the query
$updateQuery .= implode(', ', $columns) . $where;
$result = mysqli_query($conn, $updateQuery);

这将显着减少我数据库的负载，因为这些事件每秒都会发生(想象一下数百名玩家同时使用数百个自己的单位攻击数百个敌方单位)。我希望这可以帮助别人。