Skip to content
Advertisement

Replace value in array with result of mysqli_query

I’m having some trouble with php coding. What I want to do is following:

  1. Create an array ($rows) and fil it with the results of a mysqli_query ($query1) –> OK
  2. for each element in that array, replace the value of a certain key (pilot_rule_id) with the result of another mysqli_query ($query2). (the second query will return one row, since the id of the pilot table is the primary key).

So far I have

$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

Advertisement

Answer

You can something like this:

$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;
}

However, you really should learn about SQL joins instead. Using SQL joins you can avoid N+1 queries to the database.

$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
}
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement