Using Array Result from Prepared Statement as Parameter in Subsequent Prepared Statement
Hi All,
Very new to PHP. Have a series of 3 Prepared Statements (see code below) I'm attempting to run through. This page fires from a link on a page that lists the individual candidates and all that works OK. Prepared Statement 1 works and displays the data in the particular row of the wanted columns, Access background thus I'd call it record and fields, but I think it's called row and columns here. Prepared Statement 2 which quires on a cross-reference table (we have a many-to-many relationship between candidates and positions, thus need for the cross-reference table) works and I can tell the array $selected_positions loads since I can see the position_id data in the <body> of the file using this:
<?php
foreach ($selected_positions as $item) {
echo $item.'<br>';
}
?>
Can't take this array $selected_positions and use it as the parameter in Prepared Statement 3, at least not how I'm attempting to do it. So, obviously Prepared Statement 3 is failing thus no way is an array I called $the_positions which is supposed to contain position ids, position titles and position numbers of the position_id that are in array $selected_positions. I can tell Prepared Statement 3 is failing because nothing shows in this table which is in the <body> of the file:
<table class="table table-striped">
<tr>
<th>Position ID</th>
<th>Position Number</th>
<th>Title</th>
</tr>
<?php while ($stmt->fetch()) { ?>
<tr>
<td><?= $position_id; ?></td>
<td><?= $position_number; ?></td>
<td><?= $title; ?></td>
</tr>
<?php } ?>
</table>
Here's the PHP script:
<?php
require_once '../includes/session_timeout_db.php';
?>
<?php
require_once '../includes/connection.php';
// initialize flag
$OK = false;
$conn = dbConnect('read');
// initialize statement
$stmt = $conn->stmt_init();
if (isset($_GET['candidate_id'])) {
$sql = 'SELECT candidate_id, last_name, first_name, company, mas_number, last_modified, notes
FROM candidates WHERE candidate_id = ?'; }
if ($stmt->prepare($sql)) {
// bind the query parameter
$stmt->bind_param('i', $_GET['candidate_id']);
// execute the query, and fetch the result
$OK = $stmt->execute();
//bind the results to variables
$stmt->bind_result($candidate_id, $last_name, $first_name, $company, $mas_number, $last_modified, $notes);
$stmt->fetch();
// free the database resources for the second query
$stmt->free_result();
}
// get positions associated with Candidate
$sql = 'SELECT position_id FROM pos2cands WHERE candidate_id = ?';
if ($stmt->prepare($sql)) {
// bind the query parameter
$stmt->bind_param('i', $_GET['candidate_id']);
// execute the query, and fetch the result
$OK = $stmt->execute();
$stmt->bind_result($position_id);
// loop through the results to store them in an array
$selected_positions = [];
while ($stmt->fetch()) {
$selected_positions[] = $position_id;
}
}
// find position data from array
$sql = 'SELECT position_id, position_number, title
FROM positions WHERE position_id = ?';
if ($stmt->prepare($sql)) {
// bind the query parameter
$stmt->bind_param('i', $_GET[$position_id]);
// execute the query, and fetch the result
$OK = $stmt->execute();
//bind the results to variables
$stmt->bind_result($position_id, $position_number, $title);
// loop through the results to store them in an array
$the_positions = [];
while ($stmt->fetch()) {
$the_positions[] = $position_id;
}
}
// get error message if query fails
if (isset($stmt) && !$OK) {
$error = $stmt->error;
}
if (!$stmt) {
$error = $conn->error;
} else {
$numRows = $stmt->num_rows;
}
?>
Thanks in advancel
AdChoices