Only the result result from php query displays in the html table, the rest display outside

2016-11-12 php mysql html-table

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

Answers

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.

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';
?>

Related