mysql GROUP BY to sort and display the output

Question

I'm trying to make a multi-user page where user posts something, and posts are grouped and sorted by the user id and display only several of them on main page.

I have a table where it stores user information (id, password etc) and another table that stores the posts (content, post id, poster id).

What I'm stuck at and wondering is that I know I should use GROUP BY or DISTINCT to do it, but when I use it, it would only display only one post on each user. and it will output them all in one row.

        $result = mysqli_query($con, "SELECT * FROM post GROUP BY author_id") or die(mysqli_error($con));

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

                $title =  $row['title'];
                $id =  $row['post_id'];
                $author = $row['author_id'];
                echo "\n<div class=\"thumb\">";
                echo "<p>$author</p>";
                echo "<div class=\"thumb-title\">$title</div>";
                echo "\n</div>";        
        }

Output

User1 User2
Post1 post2

I want this to be like

User1 
Post1 Post2 Post3 (see more)

User2
Post1 Post2 Post3 (see more)
...

You need to add another column in Group By such as post_id for example: `SELECT * FROM post GROUP BY author_id, post_id` try it and let me know. — Akam, Feb 02 '20 at 06:52
You shouldn't be using `GROUP BY` or `DISTINCT`. You probably just want to `ORDER BY author_id`; then in your loop only display the `thumb` div when the value of `$author` changes — Nick, Feb 02 '20 at 06:57
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Feb 13 '20 at 17:15

score -2 · Answer 1 · answered Feb 02 '20 at 07:07

Your table contain one to many relation between your authors and posts tables. I means your user1 can have multiple post like:

user1 -> post1
user1 -> post2
user1 -> post3

So now your fetching your posts by grouping author_id, so mysql will return single row related to each author in your database table.

you can't get output what expected by doing this.

try like this:

$users = mysqli_query($con, "SELECT * FROM authors") or die(mysqli_error($con));

while($user = mysqli_fetch_array($users)){
    $result = mysqli_query($con, "SELECT * FROM posts WHERE author_id = {$user['id']}") or 
 die(mysqli_error($con));

    $author = $user['id'];
    echo "\n<div class=\"thumb\">";
    echo "<p>$author</p>";

    while($row = mysqli_fetch_array($result)){
        $title =  $row['title'];
        $id =  $row['post_id'];

        echo "<div class=\"thumb-title\">$title</div>";           
    }

    echo "\n</div>";  

}

Don't use two queries where one will suffice , and see about sql injection and the importance of prepared and bound queries — Strawberry, Feb 02 '20 at 10:02
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Feb 13 '20 at 17:15

mysql GROUP BY to sort and display the output

1 Answers1