Another SQL query in a loop (row) query

Question

I am struggling with my code. I try to make a News Feed.

The news feed is working well but now I want to add a avatar picture on each news row to have a picture from the author. The avatar picture path is in a different table (users).

That means I need to get in the loop the specific user ID ($row['uid']) and with that uid I need to get the related path from the user's avatar in the user table.

If I try to use a query in my loop it shows me only one result instead of 6 as specified in my query.

Do you have any suggestion or advise how I can solve that issue?

Many thanks for your support!

This is my attempt for the moment:

            <div class="timeline p-4 block mb-4">

                <?php

                // Getting the user id and the feedmessage from Table "activity_feed"
                $statement = $pdo->prepare("SELECT feed_message, uid FROM activity_feed WHERE cid = :cid ORDER BY id DESC LIMIT 6");
                $result = $statement->execute(array('cid' => $cid));
                $count = 1;
                while($row = $statement->fetch()) 
                
                { 
                // Starting the News feed Loop
                ?>
                
                <?php

                // This will repeat now X times as defined in the query above by = LIMIT ?

                    // Getting the avatar path from the user table 
                    $statement = $pdo->prepare("SELECT avatar FROM users WHERE id = :id");
                    $result = $statement->execute(array('id' => $row['uid']));
                    $user_avatar = $statement->fetch(); 
                    ?>

                        <style>
                            #circle_feed {
                            height:50px;
                            width:50px;
                            border-radius:50%;
                            /* Including the avatar path from query above*/ 
                            background-image: url("<?php echo $user_avatar['avatar']; ?>");
                            background-position:center;
                            background-size:cover;
                                        }
                        </style>
                                

                    <div class="tl-item ">
                        <div class="tl-dot"><a class="tl-author" href="#" data-abc="true">
                        <!-- Including the Avatar circle here-->
                            <span class="w-40 avatar circle gd-warning border" id="circle_feed"></span></a>
                        </div>
                        <div class="tl-content">
                            <div class=""><?php echo $row['feed_message']; ?></div>
                            <div class="tl-date text-muted mt-1">DATE</div>
                        </div>
                    </div>                    

                  <?php 
                  // News Feed Loop is ending here
                    }       
                  ?>                   
                    
                </div>
            </div>

Answer 1

There is no need to loop. SQL is a set-based language that can give your the results that you want in a single query, using the join syntax:

SELECT 
    af.feed_message, 
    af.uid,
    u.avatar
FROM activity_feed af
INNER JOIN users u ON u.id = af.uid
WHERE af.cid = :cid 
ORDER BY af.id DESC 
LIMIT 6

This is much more efficient than running 7 queries (one for the activity feed, then one for each row returned by the first query, in a PHP loop).

You can then fetch the rows of the resultset and use this directly in your application.