Hi Rob,
I've moved the code of this site I'm working on from MySQLi to PDO. I've placed the while loop, I believe it is called, in the cascading 3 query script and made certain to place the } after the 3rd query.
When I used $sql in
while ($result = $sql->fetch(PDO::FETCH_ASSOC)) {
$field = $result['field'];
I got this: Fatal error: Call to a member function fetch() on string in . . .
So I replaced $sql with $stmt which didn't throw the error, but probably was an incorrect move. Query 1 still displays the column data of the particular row through this code in the <body>:
<br>
<strong>Name:</strong>
<?php
echo $first_name . ' ' . $last_name;
?>
<br>
<strong>Company:</strong>
<?php
echo $company;
?>
<br>
<strong>MAS#:</strong>
<?php
echo $mas_number;
?>
<br>
<strong>Last Modified:</strong>
<?php
echo $last_modified;
?>
<br>
<strong>Notes:</strong>
<pre><?php echo $notes; ?></pre>
<br>
But after query 1 things fall apart. You can see in the code, at the very bottom of this response, that I've placed the while loop between query 1 and query 2. I can not get results of query 3 to display in the table in the <body> That table is:
<table class="table table-striped">
<tr>
<th>Position ID</th>
<th>Position Number</th>
<th>Title</th>
</tr>
<?php foreach ($stmt as $row) { ?>
<tr>
<td><?= $row['position_id']; ?></td>
<td><?= $row['position_number']; ?></td>
<td><?= $row['title']; ?></td>
</tr>
<?php } ?>
</table>
Any assistance or examples of proper PDO would be greatly appreciated. What we have is a many-to-many issue. That is, a candidate will be associated with multiple positions and a position will be associated with multiple candidates, thus the need for cross reference table pos2cands.
I'm clearly a beginner certainly dazed and confused. Yet I would assume anytime one has a many-to-many issue the cascading query approach is one of the options since interface with the cross reference table is essential. I know you discussed a single query with left joins, but I wanted to further investigate your while loop approach. Display of the information, or the R in CRUD, is obviously vital. In my research, so far, I haven't seen much discussion regarding display of data in a many-to-many scenario; but, I'm certain I haven't been correctly studying the matter.
Thanks again for any assistance. Below is the code:
<?php
require_once '../includes/session_timeout_db.php';
?>
<?php
require_once '../includes/connection.php';
// initialize flag
$OK = false;
// create database connection
$conn = dbConnect('read', 'pdo');
// get details of selected record
if (isset($_GET['candidate_id'])) {
// prepare SQL query
$sql = 'SELECT candidate_id, last_name, first_name, company, mas_number, last_modified, notes
FROM candidates WHERE candidate_id = ?';
$stmt = $conn->prepare($sql);
// pass the placeholder value to execute() as a single-element array
$OK = $stmt->execute([$_GET['candidate_id']]);
// bind the results
$stmt->bindColumn(1, $candidate_id);
$stmt->bindColumn(2, $last_name);
$stmt->bindColumn(3, $first_name);
$stmt->bindColumn(4, $company);
$stmt->bindColumn(5, $mas_number);
$stmt->bindColumn(6, $last_modified);
$stmt->bindColumn(7, $notes);
$stmt->fetch();
// use value of query 2 as parameter in query 3
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
$field = $result['field'];
// query 2 get positions associated with Candidate
$sql = 'SELECT position_id FROM pos2cands WHERE candidate_id = ?';
$stmt = $conn->prepare($sql);
// pass the placeholder value to execute() as a single-element array
$result = $stmt->execute([$_GET['candidate_id']]);
// bind the results
$stmt->bindColumn(1, $position_id);
// query 3 find position data
$sql = 'SELECT position_id, position_number, title
FROM positions WHERE position_id = ?';
$stmt = $conn->prepare($sql);
// pass the placeholder value to execute() as a single-element array
$result = $stmt->execute([$field]);
// bind the results
$stmt->bindColumn(1, $position_id);
$stmt->bindColumn(2, $position_number);
$stmt->bindColumn(3, $title);
}
}
// get error message if query fails
if (isset($stmt) && !$OK) {
$error = $stmt->error;
}
elseif (!$stmt) {
$error = $conn->error;
}
?>
1 Correct answer
24
Replies
AdChoices