problem grouping 2 sql tables in php
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I'm writing a Forum Software just as a practise. Sadly I ran into a small problem. The problem is with Categories and Threads being rendered. PHP has no problem calling all of the data from the SQL. But the problem is when it's ordered as it should.

This is how my current data is being returned. As you can see not quite a Forum style

Date returned Image

and this is how I want the data to be structured

How data should be, Wireframe

and here is the code

$stmt = $pdo->query('SELECT c.*, t.* FROM category c, threads t WHERE t.thread_cat = c.cid GROUP BY t.tid, c.cid ORDER BY t.thread_order ASC');
$row = $stmt->fetchAll(PDO::FETCH_OBJ);

if($row) {

foreach ($row as $data) {

echo '
    <table border="0">
        <tr>
            <th><h3>'.$data->cat_name.'</h3></th>
            <th><h3>Latest Post</h3></th>
        <tr>

        <tr>
            <td><a href="thread.php?t='.$data->tid.'">'.$data->thread_name.'</a></td>
            <td></td>
        </tr>

 </table>';

}

} else {

 echo 'The categories could not be displayed, please try again later.';

}

SQL for threads

 CREATE TABLE `threads` (
  `tid` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `thread_name` varchar(48) DEFAULT NULL,
  `thread_desc` varchar(160) DEFAULT NULL,
  `thread_cat` int(12) DEFAULT NULL,
  `thread_order` int(1) DEFAULT NULL,
  `thread_adult` int(1) DEFAULT '0',
  PRIMARY KEY (`tid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

SQL for Category

CREATE TABLE `category` (
`cid` int(12) unsigned NOT NULL AUTO_INCREMENT,
`cat_name` varchar(48) DEFAULT NULL,
`cat_order` int(1) DEFAULT NULL,
`cat_adult` int(1) DEFAULT '0',
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
What is your problem, what do you want done? Can you give us a screen shot or an example of how you would like this code to work?
mashtullah 14 days ago
@r10t or its the last "Latest Post" that is aligned to the extreme right?
mashtullah 14 days ago
@mashtullah please refer to the Image. As you can see there is 2 "General" categories being returned. Feedback and Front Page news should be in General category. Instead it's returning Category + 1 Thread. and if we have more than 1 Thread in a Category then it's just gonna return it as a new HTML Table
r1ot 14 days ago
Hey r10t, may I have the sql table.So that I can test it on my side.
Codeword 14 days ago
@r10t can you give me your two tables the category and threads i recreate the query localy please
mashtullah 14 days ago
@mashtullah updated question with SQL tables and Wireframe of what I'm trying to achive
r1ot 14 days ago
@ Codeword updated question with SQL tables and Wireframe of what I'm trying to achive
r1ot 14 days ago
@ r1ot check my solution, hope it works for you
Zhopon 14 days ago
Hey I just answered Group by c.cid ?
SilverHood Apps 14 days ago
awarded to kerncy

Crowdsource coding tasks.

3 Solutions


I'd recommend just a simple query to fetch all the rows, sorted by category id. Output the category only if its value changes from the previous row.

<?php

$stmt = $pdo-> query("SELECT * FROM `myTable` ORDER BY cat_name");

$current_cat = null;
while ($row = $stmt->fetch()) {
 if ($row["cat_name"] != $current_cat) {
$current_cat = $row["cat_name"];
echo "Category #{$current_cat}\n";
 }
echo $row["thread_name"] . "\n";
}

?>
Winning solution

The issue is not in sql but in php management. Here is your updated code that should work :

$stmt = $pdo->query('SELECT c.*, t.* FROM category c, threads t WHERE t.thread_cat = c.cid ORDER BY c.cid, t.thread_order ASC');
$row = $stmt->fetchAll(PDO::FETCH_OBJ);

if($row) {

    $previousCat = "";
    $init = true;
    foreach ($row as $data) {
        if ($previousCat != $data->cat_name) {
            if ($init == false) {
                echo '</table>';
            }
            echo '
                <table border="0">
                <tr>
                    <th><h3>'.$data->cat_name.'</h3></th>
                    <th><h3>Latest Post</h3></th>
                <tr>';
            $previousCat = $data->cat_name;
            $init = false;
        }
        echo '
            <tr>
                <td><a href="thread.php?t='.$data->tid.'">'.$data->thread_name.'</a></td>
                <td></td>
            </tr>';

    }
echo '</table>';
} else {

 echo 'The categories could not be displayed, please try again later.';

}

I am assuming you need all the general category Threads to be under general itself while in the image there are two general categories shown differently so I would suggest change $stmt this into..

SELECT c., t. FROM category c, threads t WHERE t.thread_cat = c.cid GROUP BY c.cid ORDER BY t.thread_order ASC'

Here what it will do is group by its category name instead of cascading IDs.

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' t. FROM category c, threads t WHERE t.threadcat = c.cid GROUP BY c.catname OR' at line 1' in /Applications/MAMP/htdocs/cloud/beta/index.php:6 Stack trace:
r1ot 14 days ago
Your rest code Seems perfect just change the group by clause sorry for not formatting..
SilverHood Apps 14 days ago
It's returning only 1 Forum (aka Thread) per Category now...
r1ot 14 days ago
Ok let me see..
SilverHood Apps 14 days ago
Try using c.cid alone in the GROUP BY function?
SilverHood Apps 14 days ago
View Timeline