CODEWITHSUNDEEP
phpsql
TheWelshManc
TheWelshManc

Reputation: 535

How Can I Get The Username of The Last Post?

I am in the process of making my own forum and I wanted to be able to display the username of the last person to post in a topic. Below is the code I have got right now but I've tried changing the SQL functions and can't seem to get it right. It will only display the same username in each row of the table.

Below is all the relevant code for it to display what I want.

$host = "host"; // Host name 
$username = "username"; // Mysql username 
$password = "password"; // Mysql password 
$db_name = "db"; // Database name 
$tbl_name = "fquestions"; // Table name 
$tbl_name2 = "fanswer"; // Table name 

// Connect to server and select databse.
$conn = mysqli_connect($host, $username, $password)or die("cannot connect"); 
mysqli_select_db($conn, $db_name)or die("cannot select DB");

<table width="100%" style="font-size: 20px;">
    <tr>
    <td align="center" width="20%"><h4>Topic</h4></td>
    <td align="center" width="20%"><h4>Posted By</h4></td>
    <td align="center" width="20%"><h4>Replies</h4></td>
    <td align="center" width="20%"><h4>Last Post</h4></td>
    <td align="center" width="20%"><h4>Date / Time</h4></td>
    </tr>

<?php

// Start looping table row
$sql2 = "SELECT * FROM $tbl_name";
$result2 = mysqli_query($conn, $sql2);

if (mysqli_num_rows($result2) > 0) {
    while($rows = mysqli_fetch_array($result2)) {
        $topic = strtolower(htmlentities($rows['topic']));
        $topic = str_replace(get_html_translation_table(), "-", $topic);
        $topic = str_replace(" ", "-", $topic);
        $topic = preg_replace("/[-]+/i", "-", $topic);
        ?>
        <tr>
            <td align="center"><a href="view-topic?id=<?php echo $rows['id']; ?>/<?echo $topic ?>"><?php echo $rows['topic']; ?></a><br></td>
            <td align="center"><?php echo $rows['username']; ?></td>
            <td align="center"><?php echo $rows['reply']; ?></td>
            <?php
            $sql3 = "SELECT * FROM $tbl_name2 ORDER BY a_id DESC, LIMIT 1";
            $result3 = mysqli_query($conn, $sql3);

            while($rows2 = mysqli_fetch_array($result3)) {
                ?>
                <td align="center"><?php echo $rows2['a_username']; ?></td>
                <?php
            }
            ?>
            <td align="center"><?php echo $rows['datetime']; ?></td>
        </tr>

        <?php
    // Exit looping and close connection 
    }
} else {
    ?>
    <tr>
        <td align="center"><?php echo "0 records"; ?></td>
        <td align="center"><?php echo "0 records"; ?></td>
        <td align="center"><?php echo "0 records"; ?></td>
        <td align="center"><?php echo "0 records"; ?></td>
        <td align="center"><?php echo "0 records"; ?></td>
    </tr>
    <?php
}
?>
</table>

I have also added images of the table structures if that helps!

fquestions table structure

fanswer table structure

The fquestions table is where all topic titles and details are kept along with things like how many times they were viewed, who posted it etc and the fanswer one is where all answers are stored, the question_id is the same id as the one on the first table. The a_id is reply number and then the detail and username of who posted and also the date and time.

Upvotes: 2

Views: 58

Answers (1)

jeroen
jeroen

Reputation: 91742

In the loop, you are selecting the last row of fanswer, regardless whether it is related to your question / answer or not:

$sql3 = "SELECT * FROM $tbl_name2 ORDER BY a_id DESC, LIMIT 1";

You want something like (depending on your table structures and relations):

$sql3 = "SELECT * FROM $tbl_name2 WHERE question_id = ? ORDER BY a_id DESC LIMIT 1";

or:

$sql3 = "SELECT * FROM $tbl_name2 WHERE a_id = ? ORDER BY a_id DESC LIMIT 1";

where you bind the placeholder ? to the specific item you are in.

Upvotes: 1

Related Questions