Categories: php, jquery, html, sql, loops

Another SQL query in a loop (row) query

1 answer

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>

All answers to this question, which has the identifier 61214718

The best answer:

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.

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data