CODEWITHSUNDEEP
phpmysql
stulk
stulk

Reputation: 105

How do I nest prepared statements?

I'm updating some old code to prepared statements but the previous nesting query no longer works. Should I create a separate array first and then loop through that? If so, how?

<?php

// GET BOOKS

$stmt=$mysqli->prepare('SELECT book_name, book_num, isbn FROM master_books WHERE id=?');

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

$stmt->execute();

$stmt->bind_result($book_name, $book_num, $isbn);

while ($stmt->fetch()) {

    echo "<p>$isbn $book_name ($book_num)<br />\n";

        // GET AUTHOR(S)

        $stmt2=$mysqli->prepare('SELECT last_name, first_name FROM master_authors WHERE book_num=? ORDER BY last_name ASC');
        
        $stmt2->bind_param('s', $book_num);
        
        $stmt2->execute();
        
        $stmt2->bind_result($last_name, $first_name);
        
        $authors=array();
        
        while ($stmt2=fetch()) {
        
            $name="$last_name, $first_name";
        
            $authors[]=$name;
        }
        
        echo implode(", ", $authors).'</p>';
}

$stmt->close();

$stmt2->close();

?>

Upvotes: 0

Views: 52

Answers (1)

ggordon
ggordon

Reputation: 10035

Using a JOIN and aggregating the authors using GROUP_CONCAT may help you to achieve this with one query and reduce the need for additional nesting loops.

See possible edits below.

<?php

// GET BOOKS

$stmt=$mysqli->prepare("
    SELECT 
        b.book_name, 
        b.book_num, 
        b.isbn,
        GROUP_CONCAT(
           CONCAT(a.last_name,', ' , a.first_name )
           SEPARATOR ' , '
        ) as authors
     FROM 
        master_books b 
     LEFT JOIN
        master_authors a ON a.book_num = b.book_num
     WHERE 
        b.id=?
     GROUP BY
        b.book_name, 
        b.book_num, 
        b.isbn 
     
");

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

$stmt->execute();

$stmt->bind_result($book_name, $book_num, $isbn, $authors);

while ($stmt->fetch()) {

    echo "<p>$isbn $book_name ($book_num)<br />\n$authors</p>";
}

$stmt->close();

?>

Upvotes: 1

Related Questions