Using a PHP loop to fetch MySQL data and repeat until get certain amount

Question

I have a database table that stores image details. From this I want to get a exactly 6 images (as strings only) and display the images as background images on figure tags.

Where I am struggling is to get 6 image paths even if there are only 4 in the DB. In this instance I would like to go back around at fetch the first two rows again. Additionally if there are 10 rows from the query, I would like to randomly select only 6.

So far I have...

Functions file

// Slideshow images (Homepage)
function get_slideshow() {
    global $conn;
    $sql =  "SELECT id, caption, image, page_id 
                FROM slideshow
                ORDER BY id DESC";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $result = $stmt->get_result();
    $stmt->free_result();
    $stmt->close();
    return $result;
}

Index.php

$slideshow = get_slideshow();

<?php
    if ($slideshow->num_rows > 0) {
        while($row = $slideshow->fetch_assoc()) {
?>
        <figure style="background-image:url(<?php echo '/photos/'.$row['image']; ?>)"></figure>
<?php   
        }
    }
?>

Any help would be great. Thanks in advance :)

How to select random 6 rows from a table: https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast. This combined with the LIMIT should get you close to what you want. If you get less than 6, reuse some of the results you get. — Nic3500, May 01 '18 at 16:39

score 1 · Answer 1 · answered May 01 '18 at 19:14

Drop the ORDER BY clause from the query. Once you've got the result set in an array, use PHP's shuffle function. Once they've been shuffled, use a loop to generate the image from the 1st 6 images from the shuffled array.

Depending on how many images are in the db table, the use of ORDER BY RAND can potentially have quite a bad performance penalty

Using a PHP loop to fetch MySQL data and repeat until get certain amount

1 Answers1