Custom MySQL & PHP Forum - List Topics Under Categories

1
votes

I'm working on a custom MySQL & PHP forum, and I'm trying to get my topics to be seated/listed under their respective categories. When I create a topic in my admin panel I can set the category id, which corresponds with the id of the category. I've already browsed around quite a bit on the web and on the site here.

I've been able to get the categories to show up and have been able to seat where the topics would go, but the topic(s) aren't showing up, the param I pass keeps resulting in null, and causes each category to show that there are no topics created.

This is the function I'm working on: 

function fetch_forum() {
global $connection;

$query = "SELECT * FROM forum_categories LEFT JOIN forum_topics ON forum_topics.forum_topic_category_id = forum_categories.forum_category_id";
$select_forum_data = mysqli_query($connection, $query);
$new_topic = "";
$pre_topic = "";

while($row = mysqli_fetch_assoc($select_forum_data)) {
    $forum_topic_id = $row['forum_topic_id'];
    $forum_topic_title = $row['forum_topic_title'];
    $forum_topic_description = $row['forum_topic_description'];
    $forum_topic_category_id = $row['forum_topic_category_id'];
    $forum_category_id = $row['forum_category_id'];
    $forum_category_title = $row['forum_category_title'];

    $new_topic = $forum_topic_title;

    if($forum_category_title != null) {
        echo "<thead>";
            echo "<tr>";
                echo "<th><span class='glyphicon glyphicon-file' aria-hidden='true'></span> {$forum_category_title}</th>";
                echo "<th>Topics</th>";
                echo "<th>Posts</th>";
                echo "<th>Last Post</th>";
            echo "</tr>";
        echo "</thead>";
    }

    if($new_topic != $pre_topic) {
        echo "<tbody>";
            echo "<tr>";
                echo "<td><a href='topic.php?t_id=<?php echo $forum_topic_id; ?>'><h5> {$forum_topic_title}</h5></a></td>";
                echo "<td>1</td>";
                echo "<td>1</td>";
                echo "<td>03-28-2017</td>";
            echo "</tr>";
        echo "</tbody>";
    } else {
        echo "<tbody>";
            echo "<tr>";
                echo "<td>No topics have been created</td>";
            echo "</tr>";
        echo "</tbody>";
    }

    $pre_topic = $forum_topic_title;
}
}

This is my index page: 

<?php include "includes/header.php"; ?>
<?php include "includes/db.php"; ?>

    <!-- Navigation -->
    <?php include "includes/navigation.php"; ?>

    <!-- Page Content -->
    <div class="container">
        <div class="row">

            <!-- Blog Entries Column -->
            <div class="col-md-8">

                <!-- Forum Navigation -->
                <?php include "includes/forum_navigation.php"; ?>

                <!-- First Blog forum -->
                <table class="table table-hover">
                        <?php fetch_forum(); ?>
                </table>

                <hr>

            </div>

            <!-- Blog Sidebar Widgets Column -->
            <?php include "includes/sidebar.php"; ?>

        </div>

    </div>
    <!-- /.row -->

    <hr>

    <?php include "includes/footer.php"; ?>

This is my header page:

<?php ob_start(); ?>
<?php session_start(); ?>
<?php include "functions.php"; ?>
<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>My CMS Framework</title>

    <!-- Bootstrap Core CSS -->
    <link href="../css/bootstrap.min.css" rel="stylesheet">

    <!-- Custom CSS -->
    <link href="../css/blog-home.css" rel="stylesheet">
    <link href="../css/style.css" rel="stylesheet">

    <!-- Custom Fonts -->
    <link href="../admin/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">

    <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
        <script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
        <script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
    <![endif]-->

    <script type="text/javascript" src="../../js/autoBreadcrumbs.js"></script>

</head>

<body>

And this is my forum: http://innovativestudios.net/forum/

2
phphtmlmysql
As you can see from my forum link the param is passing as null resulting in the "No Topics Have Been Created." I have one topic that I've created and the Topic's Category ID is equal to the "General" Category ID, but it's not being listed/nested under the "General" Category. - Jacob Schmidt
what data type is forum_topic_category_id and forum_category_id? - inarilo

2 Answers

0
votes

FWIW your code works for me and produces:

Program output

using this assumed DB structure and dataset.DB Join

Could it be just a problem with your test data set?

0
votes

Had to change the function a little bit, but this is the final result:

    function fetch_forum() {
    global $connection;

    $query = "SELECT * FROM forum_categories LEFT JOIN forum_topics ON forum_topic_category_id = forum_category_id";
    $select_forum_data = mysqli_query($connection, $query);
    $new_topic = "";
    $pre_topic = "";

    while($row = mysqli_fetch_assoc($select_forum_data)) {
        $forum_topic_id = $row['forum_topic_id'];
        $forum_topic_title = $row['forum_topic_title'];
        $forum_topic_description = $row['forum_topic_description'];
        $forum_topic_category_id = $row['forum_topic_category_id'];
        $forum_category_id = $row['forum_category_id'];
        $forum_category_title = $row['forum_category_title'];

        $new_category = $forum_category_title;

        if($new_category != $pre_category) {
            echo "<thead>";
                echo "<tr>";
                    echo "<th><span class='glyphicon glyphicon-file' aria-hidden='true'></span> {$forum_category_title}</th>";
                    echo "<th>Topics</th>";
                    echo "<th>Posts</th>";
                    echo "<th>Last Post</th>";
                echo "</tr>";
            echo "</thead>";
        }

        if($forum_topic_title != null or $forum_topic_description != null) {
            echo "<tbody>";
                echo "<tr>";
                    echo "<td><a href='topic.php?t_id=$forum_topic_id'><h4>{$forum_topic_title}</h4></a><div class='row-fluid'><h6>{$forum_topic_description}</h6></div></td>";
                    echo "<td>1</td>";
                    echo "<td>1</td>";
                    echo "<td>03-28-2017</td>";
                echo "</tr>";
            echo "</tbody>";
        } else {
            echo "<tbody>";
                echo "<tr>";
                    echo "<td>No topics have been created yet.</td>";
                echo "</tr>";
            echo "</tbody>";
        }

        $pre_category = $forum_category_title;
    }
}