Showing six latest PhpBB posts instead of six copies of latest post

Question

Trying to display six of the latest posts from my PhpBB installation. I'm happy with how it's all working, however it's showing six copies of the same (most recent) post, and not size unique latest posts.

Just to confirm, I have seven total posts on the forums.

<?php

$con = mysqli_connect("localhost", "dbuser", "dbpass", "dbname");

$users = mysqli_query($con, "SELECT * FROM phpbb_user_group WHERE group_id='8'");
while($row = mysqli_fetch_array($users)) {
        $developers[] = $row["user_id"];
}

$post = mysqli_query($con, "SELECT * FROM phpbb_posts");
while($row = mysqli_fetch_array($post)) {
        $topic_id = $row["topic_id"];
        $forum_id = $row["forum_id"];
        $post_id = $row["post_id"];
        $post_text = $row["post_text"];
        $post_time = $row["post_time"];
}

$username = mysqli_query($con, "SELECT * FROM phpbb_users WHERE user_id='2'");
while($row = mysqli_fetch_array($username)) {
        $postauthor = $row["username"];


if (strlen($post_text) > 10)   
    $post_text = wordwrap($post_text, 120);
    $post_text = explode("\n", $post_text);
    $post_text = $post_text[0] . '...';

$result = mysqli_query($con, "SELECT * FROM phpbb_posts WHERE poster_id='2' LIMIT 6");
while($row = mysqli_fetch_array($result)) {
        $content = '<div onclick="location.href=\'http://test.mythros.net/forum/viewtopic.php?f=' . $forum_id .     '&amp;p=' . $topic_id . '#p' . $post_id . '\';" class="forum-latest-box">';
        $content .=     '<div class="forum-latest-userbar">';
        $content .=                     '<div class="forum-latest-avatar">';
        $content .=                              '<img src="https://minotar.net/helm/' . $postauthor . '/40.png">';
        $content .=                      '</div>';
        $content .=                      '<h1>' . $postauthor . '</h1>';
        $content .=              '</div>';
        $content .=              '<div class="forum-latest-content">';
        $content .=                      '<div class="forum-latest-text">';
        $content .=                              '"' . $post_text . '"';
        $content .=                      '</div>';
        $content .=                      '<div class="forum-latest-meta">';
        $content .=                              gmdate("F j, Y, g:i a", $post_time);
        $content .=                      '</div>';
        $content .=              '</div>';
        $content .=     '</div>';
        echo $content;
}

?>

Answer 1

You can solve this problem by using a single loop and getting your post data and user information at the same time using combined query to the phpbb_posts table and phpbb_users table:

## Line break added to the query for legibility
$result = mysqli_query($con,
"SELECT
    p.post_id AS post_id,
    p.topic_id AS topic_id,
    p.forum_id AS forum_id,
    p.post_time AS post_time, 
    p.post_subject AS subject,
    p.post_text AS post_text
    IFNULL(m.username, 'Guest') AS username,
    FROM phpbb_posts AS p
    LEFT JOIN phpbb_users AS m ON (m.user_id = p.poster_id)
    ORDER BY phpbb_posts.post_time DESC LIMIT 6");

while($row = mysqli_fetch_array($result)) {
# $row now contains the post information and the user info, so you can grab all your data,
# process the post text, and print it out at the same time.
    $post_text = $row["post_text"];
    # do your text transformation
    if (strlen($post_text) > 10)
    ... (etc.)
    # now set up your content
    $content = '<div onclick="location.href=\'http://test.mythros.net/forum/viewtopic.php?f=' . $row["forum_id"] .'&amp;p=' . $row["topic_id"] . '#p' . $row["post_id"] . '\';" class="forum-latest-box">';
    ... (etc.)

}