Nested loops always confuse me

Question

So I have a loop to be nested inside another loop based on two queries. I have the first loop working fine-

$sql_categories = mysql_query("SELECT * FROM $categories_table");

$results = mysql_query("SELECT * FROM $events_table");
    while ($num_rows = mysql_fetch_assoc($sql_categories)) {
        extract($num_rows);
        echo "<h2>$category_name</h2>";
        // Begin second loop to output events
        while/for(not sure) {

    }
}

I want to output into the second loop all the $vars for the corresponding $category_id. In the second query, the matching value is $event_category_id.

I don't know if that makes sense, but what I'm trying to get is basically--

<h2>Category One</h2>
Event Name
Event Name
Event Name

<h2>Category Two</h2>
Event Name
Event Name
Event Name

etc. where the "Event Name" corresponds to the "Category Name"

The two tables I'm working with look like this-

CREATE TABLE `wp_wild_dbem_categories` (
  `category_id` int(11) NOT NULL auto_increment,
  `category_name` tinytext NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `wp_wild_dbem_events` (
  `event_id` mediumint(9) NOT NULL auto_increment,
  `event_author` mediumint(9) default NULL,
  `event_name` tinytext NOT NULL,
  `event_start_time` time NOT NULL default '00:00:00',
  `event_end_time` time NOT NULL default '00:00:00',
  `event_start_date` date NOT NULL default '0000-00-00',
  `event_end_date` date default NULL,
  `event_notes` text,
  `event_rsvp` tinyint(1) NOT NULL default '0',
  `event_seats` tinyint(4) default NULL,
  `event_contactperson_id` mediumint(9) default NULL,
  `location_id` mediumint(9) NOT NULL default '0',
  `recurrence_id` mediumint(9) default NULL,
  `event_category_id` int(11) default NULL,
  UNIQUE KEY `event_id` (`event_id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1

Thanks for your help!

Answer 1

You need to do the second query inside the while loop for it to have any meaningful effect:

$sql_categories = mysql_query("SELECT * FROM $categories_table");

while($category = mysql_fetch_assoc($sql_categories)) {    
    extract($category);    
    $events = mysql_query("SELECT * FROM $events_table WHERE event_category_id = '".mysql_real_escape_string($category_id)."'");

    echo "<h2>$category_name</h2>";    
    while($event = mysql_fetch_assoc($events) {
        extract($category);
        echo "<p>$event_name</p>";
    }
}

This should get you where you want, but note that this is not the optimal way to do things. You should first get all of the events, build an array of them indexed by event_category_id and loop that array inside your while loop. This is because now you are doing one extra query per each category whereas only two queries in total should suffice.

But perhaps this would get you started on that then.