PHP/MySQL: Limit foreach loop when more than one occurance is found

Question

I'm new to PHP and MySQL and I'm currently making a blog portal. On the index page, all recent posts are shown with the title, author, date, the first 150 characters of the post plus an image from the post.

I have two tables in MySQL: "post" and "image" and I'm looping through both of these with foreach to check if a post has an image connected to it with the same postId. My problem is if a post has more than one image, I only want one image to show and not all of them like it does now. I tried making a query selecting all posts from a user where post.id = image.postId, but then the posts with no images were not shown.

I appreciate any input or suggestion on how to make this work, or maybe an entirely different approach.

In PHP/HTML file (removed the part of the code in the loop where title/content and so on is added):

<?php foreach($allPosts as $key => $onePost): ?>

    <?php foreach($allImages as $one => $oneImage): ?>

        <?php if ($oneImage['postId'] === $onePost['id']): ?>
         <?php echo '<img src="../Images/' . $oneImage['filename'] . $secondString; ?>
        <?php endif; ?>

    <?php endforeach;?>
<?php endforeach;?>

In PHP/database file:

function selectAllPosts($conn)
{
    $sql = '
    SELECT p.*
         , u.username 
      FROM post p 
      JOIN users u 
        ON p.userId = u.id 
     ORDER 
        BY created DESC
    ';
    return $allPosts = db_select($conn, $sql);
}


function selectAllImages($conn)
{
    $sql = 'SELECT * FROM image';
    return $postImages = db_select($conn, $sql);
}

If it was me, I'd forget about all the non-sql stuff for now, and instead focus on the query — Strawberry, May 13 '20 at 09:01
Tip: You don't need to open `` tag for every statement. Just open a tag data the beginning of your php script and close it when its over. — Avishka Dambawinna, May 13 '20 at 09:12

score 0 · Answer 1 · answered May 13 '20 at 09:08

You can limit your image query by adding a 'where' and a 'limit'.

something like this could work:

'SELECT * FROM image WHERE image.postId=post.id LIMIT 1'

This approach assumes that every image has the attribute 'postId', which is the id of the post, which contains the image

Note: With this query, it is undefined which iamge you get after the limit. In practice it's mostly the image which is saved first in the db, but it can vary. If you want to get a specific image, you can either add some boolean column to the imagetable e.g. 'hero-image' and use this query

'SELECT * FROM image WHERE image.postId=post.id AND image.hero=true LIMIT 1'

Tog Porter · Answer 2 · 2020-05-13T09:43:32.167

0

OK I firstly suggest for the sake of security that you use PDO in your connections to your dbase (see https://www.udemy.com/course/php7-beginners-guide-to-database-pdo/.

A simple way to get only one image from the dbase is:

$sql = 'SELECT * FROM image LIMIT 1';

How is your image table structured? Are you fussy which image is displayed? If so, when loading a record you could have one field for preferred image location and another or others for the rest, you can then select your image from the preferred image field if one exists.

Alternatively you can store the image locations in an array and only choose the first one in the array (see: https://www.php.net/manual/en/function.mysql-fetch-array.php)

As some records do not have image locations in them you will also need to include a filter in the display code like:

if(empty($row['image'])){

}
else{
echo 'Your image display code Here';
}

edited May 13 '20 at 09:43

answered May 13 '20 at 09:13

Tog Porter

421
1
7
23

The image table has an unique id, filename and postId so multiple images can have the same postId if they belong to the same post. No, it doesn't matter which one of the images in the post are shown. – Inori May 13 '20 at 10:07
Maybe the way forward is to add another field to the post record e.g. "firststimage" and when you add a new record just add one image location to that field and then you can use it when you pull the post record without going to the images table :-) – Tog Porter May 14 '20 at 18:03

PHP/MySQL: Limit foreach loop when more than one occurance is found

2 Answers2