Working with Master / Detail SQL Data in PHP

Question

I'm a newbie so bear with me. I'm making a little forum for fun. Check it:

Categories:

  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` int(11) NOT NULL,
   PRIMARY KEY (`id`)

Forums:

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `position` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
   PRIMARY KEY (`id`)

I wonder how I should structure it so it loops out the categories and the assigned forums below it :/

Am I on the right path?

  <?php
  $query = mysql_query("SELECT * FROM forums JOIN...");
  while ($row = mysql_fetch_array($query)) {..
  ?>

Maybe some sql guru can help me out.

Cheers!

Answer 1

I think you'd be best off reading a join tutorial to help you understand the basics.

Answer 2

I don't know PHP, but it looks like you want to use a single query to loop over the categories, then, for each category, to loop over the forums.

Relational Databases don't work that way. A query returns a set of rows, in the form of a table. There is no parent/child structure to it.

If PHP is capable of this, you should have a parent query to return all the categories, and loop over that. For each category, call a child query to return the forums with that category id. Then loop over that.

Answer 3

If you want to display the Categories with Forums under them in your page, I suspect you'll need to get a resultset for the categories first, and then iterate over this list with another loop for your forum list.

A join will give you a single recordset with the category in one column, and the forum in another, giving you many rows for a single category. As another answer has said, you're not really looking for a join :)

Try something like this:

$cat_rs = mysql_query("select id, name from categories");
while($cat_row = mysql_fetch_array($cat_rs)){
     // print category name from $cat_row[1]
     $forum_rs = mysql_query("select name... from forums where cat_id = '" . $cat_row[0] ."'");
     while($forum_row = mysql_fetch_array($forum_rs)){
        //print forum stuff
     }
}