[SOLVED] 2 mysql tables query with php 1 inside another :S

[marksie1988]

i am trying to create a forum system on my website, i have created the forum but i want to create categories that multiple boards can go in a bit like on here, but i dont know how i can basically say the following:

 

query category database display category if category = 1 then display all boards with category 1.

 

  here is the code i tried i will put comments into it to show you whats what.

 

$cat="SELECT * from forum_category";//select the info from the category table
$cat2=mysql_query($cat) or die("Could not get threads");

  while($cat3=mysql_fetch_array($cat2))
   {
        $cat1=$cat3['category'];
        print "<h3>$cat1</h3>";//display the category as a header
        print "<table class='maintable'>";

       $getthreads="SELECT * from forum_boards WHERE category='$cat1'";//select the boards from the table
       $getthreads2=mysql_query($getthreads) or die("Could not get threads");
            while($getthreads3=mysql_fetch_array($getthreads2))
            {
         $getthreads3[title]=strip_tags($getthreads3[title]);
         print "<tr class='mainrow'><td><A href='boards.blc?bid=$getthreads3[boardid]'>$getthreads3[title]</a></td></tr>";//display the boards
           }
   }

 

basically the board that has the same category should be displayed under that category area

[phpSensei]

<?php
$cat="SELECT * from forum_category";//select the info from the category table
$cat2=mysql_query($cat) or die("Could not get threads");

  while($cat3=mysql_fetch_array($cat2))
   {
        $cat1=$cat3['category'];
        print "<h3>$cat1</h3>";//display the category as a header
        print "<table class='maintable'>";

       $getthreads="SELECT * from forum_boards WHERE category='$cat1'";//select the boards from the table
       $getthreads2=mysql_query($getthreads) or die("Could not get threads");
       $getthreads3=mysql_fetch_array($getthreads2);
           
         $getthreads3[title]=strip_tags($getthreads3[title]);
         print "<tr class='mainrow'><td><A href='boards.blc?bid=$getthreads3[boardid]'>$getthreads3[title]</a></td></tr>";//display the boards

   }
?>

 

The While goes for all loops you are trying to make, therefor a while inside a while is not necessary

[revraz]

Just curious, wouldn't be easier to JOIN the tables?

[marksie1988]

Just curious, wouldn't be easier to JOIN the tables?

 

if i join the tables thought wouldnt i need an if statement for each category ?

[revraz]

Doesn't each post have a corresponding category id?

[marksie1988]

each board has a category here are the tables

 

CREATE TABLE `forum_boards` (
  `boardid` bigint(20) NOT NULL auto_increment,
  `category` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL default '',
  `description` mediumtext NOT NULL,
  PRIMARY KEY  (`boardid`)
) 

CREATE TABLE `forum_category` (
  `catid` bigint(20) NOT NULL auto_increment,
  `category` varchar(50) NOT NULL,
  PRIMARY KEY  (`catid`)
)

CREATE TABLE `forum_posts` (
  `postid` bigint(20) NOT NULL auto_increment,
  `author` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `post` mediumtext NOT NULL,
  `showtime` varchar(255) NOT NULL default '',
  `realtime` bigint(20) NOT NULL default '0',
  `lastposter` varchar(255) NOT NULL default '',
  `numreplies` bigint(20) NOT NULL default '0',
  `parentid` bigint(20) NOT NULL default '0',
  `boardid` bigint(20) NOT NULL default '0',
  `lastrepliedto` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`postid`)
)

[revraz]

No link from the forum_category to the forum_posts?

[marksie1988]

no there isnt

[revraz]

I would think if you did track that, then you could JOIN them.