如何使用php迭代整个mysql表

Question

I want to iterate through all the records of mysql table. Here's how my table looks like

So I want to print out the name of the user and the subject of the post they posted. I have another user table. Here's my php code.

require_once('config.php');
    // build SQL query
            $sql ="SELECT user_id, subject FROM post"; 
            $result = mysqli_query($conn, $sql);
            $row = mysqli_fetch_assoc($result);

            foreach ($row as $k => $v) {
                if($k == 'user_id'){

                    $uid = $v;
                    $sql2 ="SELECT '$db', username FROM users WHERE user_id='$uid'"; 
                    $result2 = mysqli_query($conn, $sql2);
                    $row2 = mysqli_fetch_row($result2);
                    echo $row2[1]. " posted ";

                }
                if ($k == 'subject') {

                    echo "<a href='#'> $v </a>";
                }
            }

I am using the user_id to find the user in my other table. Pretty much a noob here. However I only get one output.

However there should have been 2 outputs. because my post table contains 2 posts made by 2 different users. I only get the first post as an output. can someone please point me to right direction. Also how do I iterate a SQL table for all the records.

Answer 1

If you expect your query to return more than 1 result, you should call mysqli_fetch_assoc in a while loop.

Also, you can use a JOIN query to return all the data in a single query:

$sql = "SELECT post.subject, users.username 
        FROM post JOIN users 
        ON post.user_id = users.user_id"; 
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)){
    echo sprintf("<p>%s posted <a href='#'>%s</a></p>", $row['username'], $row['subject']);
}