CODEWITHSUNDEEP
phpmysql
Shadid
Shadid

Reputation: 4272

how to iterate whole mysql table using php

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

enter image description here

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. enter image description here

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.

Upvotes: 0

Views: 66

Answers (2)

Steve
Steve

Reputation: 20469

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']);
}

Upvotes: 1

Craig Barben
Craig Barben

Reputation: 148

I can't see where the "$db" variable is coming from, but you seem to be using it in the space where you need to define the columns you want to retrieve.

Can I suggest that you perform a join on the post table instead of performing a MySQL query on every iteration.

SELECT
    `post`.`user_id` AS `post_user_id`,
    `post`.`subject` AS `post_subject`,
    `users`.*
FROM
    `post`
LEFT JOIN
    `users` ON `post`.`user_id`=`users`.`id`

This will get you all posts, and join on the user data associated with the post if the id exists in the user table. I have just guessed the column name for the user id in the users table, but this should work.

Upvotes: 0

Related Questions