Results within results

As you have seen it is remarkably easy to alter your MySQL queries using PHP variables, and it is also easy to read values from MySQL into PHP variables. The two can be combined together so that you can run a query, then use values from its result in new queries - take a look at this following code:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    $result = mysqli_query($db, "SELECT ID, Name FROM conferences;");
    
    while ($row = mysqli_fetch_assoc($result)) {
        extract($row, EXTR_PREFIX_ALL, "conf");
        print "<strong>Speakers at $conf_Name:</strong><br />";
        $subresult = mysqli_query($db, "SELECT ID, Name FROM confspeakers WHERE Conference = $conf_ID;");

        while ($subrow = mysqli_fetch_assoc($subresult)) {
            extract($subrow, EXTR_PREFIX_ALL, "speaker");
            print "&nbsp;&nbsp;&nbsp;&nbsp;$speaker_Name<br />";
        }

        print "<br />";
    }
?>

That code pulls out all conferences from a table called "conferences", extracts each conference ID, then looks up a list of speakers who were at that conference. To try the code out you will need to create the tables using the following schema, then enter your own data:

CREATE TABLE conferences (ID INT AUTO_INCREMENT PRIMARY KEY, Name CHAR(100));
CREATE TABLE confspeakers (ID INT AUTO_INCREMENT PRIMARY KEY, Name CHAR(100), Conference INT);

Once you create the tables and add some conference and speakers, you should see it come to life in your browser.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Advanced formatting >>

Previous chapter: Mixing in PHP variables

Jump to:

Home: Table of Contents