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.

 

Next chapter: Advanced formatting >>

Previous chapter: Mixing in PHP variables

Jump to:

 

Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.