ashland3000 ashland3000 - 7 months ago 7
SQL Question

How to group different forums into categories PHP

I would like to group my forums into categories, as seen here:

enter image description here

I currently have a database table called

forum_categories
that takes a title and creates an ID for all the categories created. I also have a column in a database table named
forum_forums
(all the different forums that I want to categorize) which takes a value named
category_apart_of
.

How would I go about listing the forums in their correct category ID?

Thank you so much!

If you would like to see any of my code, or want me to explain anything more in-depth please tell me.

My current code for listing the forums (note: an SQL query to get all the forums is above):

<thead>
<tr>
<th style="width: 50%;">Forum</th>
<th style="width: 10%;">Threads</th>
<th style="width: 10%;">Posts</th>
<th style="width: 30%;">Latest Posts</th>
</tr>
</thead>
<tbody>
<?php
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$forumID = $row['forumID'];
$forum_title[] = $row['forum_title'];
$forum_description[] = $row['forum_description'];
$forum_total_threads[] = $row['forum_total_threads'];
$forum_total_posts[] = $row['forum_total_posts'];
$forum_latest_thread[] = $row['forum_latest_thread'];

$stmt2 = $db->prepare("SELECT * FROM forum_threads WHERE forum_thread_belongs_to = '$forumID'");
$stmt2->execute();
$count = $stmt2->rowCount();

echo '
<tr><td><h4 style="margin-bottom: 0px;"><a style="margin-bottom: 0px;" href="forum.php?id='
. $row['forumID'] . ' ">'.$row['forum_title']
. '</a></h4><br /><h6 style="margin-bottom: 0px; margin-top: 0px;">'
.$row['forum_description'].'</h6></td><td style="text-align: center;><span">'.$count
.'</span></td><td style="text-align: center;><span">'.$row['forum_total_posts']
.'</span></td><td>'.$row['forum_latest_thread'].'</td></tr>
';
}
?>
</tbody>


DDL for both tables:

CREATE TABLE IF NOT EXISTS `forum_forums` (
`forumID` int(11) NOT NULL AUTO_INCREMENT,
`forum_title` varchar(255) NOT NULL,
`forum_description` varchar(255) NOT NULL DEFAULT 'This forum does not have a description',
`forum_total_threads` int(255) NOT NULL DEFAULT '0',
`forum_total_posts` int(255) NOT NULL DEFAULT '0',
`forum_latest_thread` varchar(255) NOT NULL DEFAULT 'There are no new threads',
`forum_apart_of` int(11) NOT NULL,
`category_apart_of` int(11) NOT NULL,
PRIMARY KEY (`forumID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `forum_categories` (
`catID` int(11) NOT NULL AUTO_INCREMENT,
`cat_title` varchar(255) NOT NULL,
PRIMARY KEY (`catID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer

Your Question : How would I go about listing the forums in their correct category ID?

Solution : As you already have your Database Structure and as you already and should probably know in order to link your categories table with your forums table you need to have at least one column in both similar which is a category_id from your categories table auto incremented column namely as id so in order to categorize your forum into the specific category you will need to add the category id to an extra column as category_id in your forums table so each forum will have it's category mentioned in there in id value..!

And then you can list your forums by categories like this way as :

Note : This code will check for every forum category and it will sort of list all those forums under each category..!

<?php
//Assuming you have fetched whole data from forums table in $forums
//And whole data from categories in $categories
//So moving forward with the code

foreach ($categories  as $category) {
echo "<h1>".$category['category_title']."</h1>";
$category_id = $category['category_id'];
$query = mysqli_query($mysqli,"SELECT * FROM forums WHERE category_id='$category_id'");

$forums = array();
while ($rows = mysqli_fetch_array($query)) {
  $forums[] = $rows;
}

foreach ($forums as $forum) {
echo "Title :".$forum['forum_title']."</br>";
echo "Descripton :".$forum['forum_description']."</br></br></br>";
}
echo "</br></br></br></br>";  
} 
?>

WORKING CODE EXAMPLE:

<?php
$categories = array(
    array('id' => "04",'category_title' => "News & Support"),
    array('id' => "23",'category_title' => "Current Affairs"),
    array('id' => "12",'category_title' => "Politics"));

    $forums = array(
    array('forum_title' => "News 1",'category_id' => "04"),
    array('forum_title' => "News 2",'category_id' => "04"),
    array('forum_title' => "Current Afairs 1",'category_id' => "23"),
    array('forum_title' => "Current Afairs 2",'category_id' => "23"),
    array('forum_title' => "Politics 1",'category_id' => "12"),
    array('forum_title' => "Politics 2",'category_id' => "12")); 

foreach ($categories  as $category) {
echo "<h1>".$category['category_title']."</h1>";
$category_id = $category['id'];
$output = array();
for ($i=0;$i<=count($forums);$i++) {
if  ($category_id == $forums[$i]['category_id']) {
$add_forum = array('forum_title' => $forums[$i]['forum_title'],'category_id' => $forums[$i]['category_id']);
array_push($output, $add_forum);
}
}
for ($i=0;$i<=count($output);$i++) {
echo "Title :".$output[$i]['forum_title']."</br>";
}
echo "</br></br></br></br>";  
} 
?>

OUTPUT :

News & Support

Title :News 1
Title :News 2


Current Affairs

Title :Current Afairs 1
Title :Current Afairs 2


Politics

Title :Politics 1
Title :Politics 2