将数组中的值替换为 mysqli_query 的结果

Question

我在 php 编码方面遇到了一些问题。我想做的是：创建一个数组 ($rows) 并用 mysqli_query ($query1) 的结果填充它 --> OK对于该数组中的每个元素，将某个键 (pilot_rule_id) 的值替换为另一个 mysqli_query ($query2) 的结果。（第二个查询将返回一行，因为 Pilot 表的 id 是主键）。到目前为止我已经$id = "96707ac6-ecae-11ea-878d-005056bbb446";$rows = array();$query1 = mysqli_query($con, "SELECT * FROM pilot_time_schedule WHERE pilot_id='$id'");while($r = mysqli_fetch_assoc($query1)) {    $rows[] = $r;}foreach($rows as $pilotRuleId) {    $pilotRuleId->$pilot_rule_id;    $query2 = mysqli_query($con, "SELECT name FROM pilot_rule WHERE id='$piloteRuleId'");    while($r = mysqli_fetch_assoc($query2)) {        $result[] = $r;    }// Don't know how to continue from here

Answer 1

你可以这样：

$id = "96707ac6-ecae-11ea-878d-005056bbb446";

$stmt = $con->prepare('SELECT * FROM pilot_time_schedule WHERE pilot_id=?');

$stmt->bind_param('s', $id);

$stmt->execute();

$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

foreach ($rows as $row) {

    $stmt = $con->prepare('SELECT name FROM pilot_rule WHERE id=?');

    $stmt->bind_param('s', $row['pilot_rule_id']);

    $stmt->execute();

    // replace with the `name` returned from the above statement.

    $row['pilot_rule_id'] = $stmt->get_result()->fetch_row()[0] ?? null;

}

然而，您确实应该了解 SQL 连接。使用 SQL 连接，您可以避免对数据库进行 N+1 查询。

$id = "96707ac6-ecae-11ea-878d-005056bbb446";

$stmt = $con->prepare('SELECT pilot_time_schedule.*,  pilot_rule.name

    FROM pilot_time_schedule 

    JOIN pilot_rule ON pilot_rule.id=pilot_time_schedule.pilot_rule_id

    WHERE pilot_id=?');

$stmt->bind_param('s', $id);

$stmt->execute();

$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

foreach ($rows as $row) {

    echo $row['name']; // contains the name from pilot_rule

}