0

I'm building a forum for learning purposes. I'm trying without success to retrieve forum categories from the database and displaying them in a table, but only the first category displays in the table, the rest display outside the table. I will post my code, and a screenshot of the image.

<?php
include 'connect.php';
include 'header.php';

$sql = "SELECT categories.cat_id,categories.cat_name,
                categories.cat_description, COUNT(topics.topic_id) AS topics
        FROM categories
            LEFT JOIN topics ON topics.topic_id = categories.cat_id
        GROUP BY categories.cat_name, categories.cat_description, 
                categories.cat_id";

$result = mysql_query($sql);

if(!$result) {
    echo 'The categories could not be displayed, please try again later.';
} else {
    if(mysql_num_rows($result) == 0) {
        echo 'No categories defined yet.';
    } else {
        //prepare the table
        echo '
            <div class="container">
            <table class="table forum tale-striped table-hover">
            <thead>
            <tr>
                <th class="cell-stat"></th>
                <th><h3>Category</h3></th>
                <th><h3>Last topic</h3></th>
            </tr>
            </thead>';  

        while($row = mysql_fetch_assoc($result)) {          
            echo '<tbody>';
            echo '<tr >';
            echo '<td class="text-center"><i class="fa fa-exclamation fa-2x text-danger">     </i></td>';
            echo '<td><h3><a href="category.php?id=' . $row['cat_id'] . '">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'].'</td>';

            echo '<td class="float-xs-right">';

            //fetch last topic for each cat
            $topicsql = "SELECT topic_id, topic_subject, topic_date, topic_cat
                        FROM topics
                        WHERE topic_cat = " . $row['cat_id'] . "
                        ORDER BY topic_date DESC
                        LIMIT 1";

            $topicsresult = mysql_query($topicsql);

            if(!$topicsresult) {
                echo 'Last topic could not be displayed.';
            } else {
                if(mysql_num_rows($topicsresult) == 0) {
                    echo 'no topics';
                } else {
                    while($topicrow = mysql_fetch_assoc($topicsresult))
                        echo '<a href="topic.php?id=' . $topicrow['topic_id'] . '">' .  $topicrow['topic_subject'] . '</a> at ' . date('d-m-Y',  strtotime($topicrow['topic_date']));
                    }
                }
                echo '</td>';
                echo '</tr>';
                echo '</tbody>';
                echo '</table>';
                echo '</div>'; //container 
            }
        }
    }

include 'footer.php';
?>

https://www.dropbox.com/s/c1dgijuafgv9jzu/Capture.PNG?dl=0

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) – RiggsFolly Nov 12 '16 at 15:38
  • Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. – RiggsFolly Nov 12 '16 at 15:39

2 Answers2

0

Becuse you're closing the <table> tag within the while-loop. Also, the <tbody> should be outside the loop as well, since there should only be one <tbody> in a table like this.

junkfoodjunkie
  • 3,168
  • 1
  • 19
  • 33
0

Quite simply you have the closing </table> tag inside the while loop so once the first row is output you close the table. Just move it outside the while loop like this, also the opening <tbody> need moving above the while loop as well

<?php
include 'connect.php';
include 'header.php';

$sql = "SELECT categories.cat_id,categories.cat_name,
                categories.cat_description, COUNT(topics.topic_id) AS topics
        FROM categories
            LEFT JOIN topics ON topics.topic_id = categories.cat_id
        GROUP BY categories.cat_name, categories.cat_description, 
                categories.cat_id";

$result = mysql_query($sql);

if(!$result) {
    echo 'The categories could not be displayed, please try again later.';
} else {
    if(mysql_num_rows($result) == 0) {
        echo 'No categories defined yet.';
    } else {
        //prepare the table
        echo '
            <div class="container">
            <table class="table forum tale-striped table-hover">
            <thead>
            <tr>
                <th class="cell-stat"></th>
                <th><h3>Category</h3></th>
                <th><h3>Last topic</h3></th>
            </tr>
            </thead>
            <tbody>';   //<-- moved

        while($row = mysql_fetch_assoc($result)) {          

            echo '<tr >';
            echo '<td class="text-center"><i class="fa fa-exclamation fa-2x text-danger">     </i></td>';
            echo '<td><h3><a href="category.php?id=' . $row['cat_id'] . '">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'].'</td>';

            echo '<td class="float-xs-right">';

            //fetch last topic for each cat
            $topicsql = "SELECT topic_id, topic_subject, topic_date, topic_cat
                        FROM topics
                        WHERE topic_cat = " . $row['cat_id'] . "
                        ORDER BY topic_date DESC
                        LIMIT 1";

            $topicsresult = mysql_query($topicsql);

            if(!$topicsresult) {
                echo 'Last topic could not be displayed.';
            } else {
                if(mysql_num_rows($topicsresult) == 0) {
                    echo 'no topics';
                } else {
                    while($topicrow = mysql_fetch_assoc($topicsresult))
                        echo '<a href="topic.php?id=' . $topicrow['topic_id'] . '">' .  $topicrow['topic_subject'] . '</a> at ' . date('d-m-Y',  strtotime($topicrow['topic_date']));
                    }
                }
                echo '</td>';
                echo '</tr>';
            }
        }
        echo '</tbody>';            //<-- moved
        echo '</table>';            //<-- moved
        echo '</div>'; //container  //<-- moved

    }

include 'footer.php';
?>
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149