I’m having some trouble with php coding. What I want to do is following:
- Create an array ($rows) and fil it with the results of a mysqli_query ($query1) –> OK
- 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 }