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

      mysql_connect("localhost", "phpuser", "alm65z");

      mysql_select_db("phpdb");

      $result = mysql_query("SELECT ID, Name FROM conferences;");

      

      while ($row = mysql_fetch_assoc($result)) {

          extract($row, EXTR_PREFIX_ALL, "conf");

          print "<B>Speakers at $conf_Name:</B><br />";

          $subresult = mysql_query("SELECT ID, Name FROM confspeakers WHERE Conference = $conf_ID;");

  

          while ($subrow = mysql_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 something like this when viewed through your web browser:

Next chapter: Advanced formatting >>

Previous chapter: Mixing in PHP variables

Jump to:

Home: Table of Contents