Best approach to split mysqli result into smaller parts

PHP
1

Hi all

please can someone point me in the right direction…

I have a DB of images that I am making into a gallery, using bootstrap for layout.

For each image, roughly 60 per gallery, I am selecting title, description, filename and alt text from the database.

I then need to group them into sets of 4 to populate the bootstrap rows.

my approach (in English not phpcode) is:

  • query database using mysqli and return as a result object
  • create new array of all rows in the result
  • use arraychunk to split the new array into sections of 4
  • loop through those and echo variables to the html as needed

I’m not asking for the code, just if this is a sensible approach or if I am missing a way easier way of doing it, as I am relatively new to this.

Many Thanks,
Matt

2

My only thought is on the third step and whether it’s required or not. When you loop through in the fourth step, will you be looping through all ~60 images, or just four of them based on the “page” number? If you’re looping through all of them, why bother splitting the array rather than just increment a counter every fourth record to denote “page”?

I should add I’m not that familiar with bootstrap, or arraychunk() come to that, so either of those might make my comment irrelevant. As well as that, for relatively low numbers of records, it probably will make such a small difference in execution time that the only reason (to me) to pick a method is based on what makes the code clearer.

3

I’m not into Bootstrap, so it would probably help to see the resulting html that you require, then the best way to create that from an array of objects can be determined.
I think the challenge here is splitting the into rows of 4, I guess like a table but not. Doable, but not sure the best approach yet.
As a non-bootstrap user I would go for the simpler approach of creating a single continuous list and use css to determine the image width and number per row, probably with flex or inline-block.

4

Hi all

Thanks for the replies. I’m far from a php expert so all feedback is useful. In brief to keep the bootstrap grid working properly the html needs to be:

<div class="row>
<div class="imagecontainer">image title desc here</div>
<div class="imagecontainer">...</div>
<div class="imagecontainer">...</div>
<div class="imagecontainer">...</div>
</div> <!-- end row -->

So each row contains 4 images and related title etc.

So my thought was to split the array into chunks of 4 images, then for each of those 4 items generate one row.

I can handle all the styling etc, I just need to get my head around how I can take a set of 60 images, html blocks of sets of 4.

Hope that makes sense
Thanks
Matt

5

So it’s not going to be responsive?

6

It’s Bootstrap, so I guess it will work like a 4 column bootstrap layout.

1 Like
7

Hi

Yes as above it’s responsive depending on screen res. it goes from 1 row of 4 columns, then down to 2 rows of 2 colums, then down to 4 rows of 1 column. This bit it all fine, I am just trying to understand the best way to generate that html in sets of 4 items.

Each set of 4 items must start with <div class="row"> and end with the closing div for that row.

Thank you

Matt

1 Like
8

Maybe something along these lines.
(Not tested)

<div class="row>
<?php
$i = 1;
while($row = $sql->fetch()) : ?>
	<div class="imagecontainer">image title desc here</div>
	<?php if($i === 4) : ?>
		</div>
		<div class="row">
		<?php $i = 0 ;
	endif ;
	$i++ ;
endwhile ?>
</div>
9

I think that may give you some extra mark-up on the last one. :thinking:
Close.

10

That was actually really bad come to think of it. :grinning:
Another attempt, again not tested:-

$i = 1 ;   // Initialise the counter
while($row = $sql->fetch()) {   // Iterate throught DB results
	if($i === 1){ echo '<div class="row">';} // first item in the row, start new row
	echo '<div class="imagecontainer">image title desc here</div>' ; // Add this item
	if($i === 4){       // The 4th item in a row
		echo '</div>';   // Close the row
		$i = 0 ;      // Reset the counter for a new row
	}
	$i++ ;   // Counter up one
}  // end while
11

Again it seems a bit fragile, as the number of results must be a multiple of 4 to generate valid html.

12

Thanks for all the info, much appreciated. I will take a look and give it a try. It’s similar to one thought I had (create a counter and close the row when the counter hits 4), but I don’t know enough php to do that elegantly yet - I’ll look at your suggestions.

Thanks again

Matt

13

The idea this time is to open a new row on the 1st, then on the 4th, close the row and reset the counter.

14

That makes sense. I’ll need to think about how the final block works which may not include 4 items but still needs to close the row. Much appreciated. thank you.

16

I’d just have a Boolean “$divopen” and set it to true or false as the div is opened or closed. After the loop, if it’s still true, then output the closing div tag. Seems a bit wrong to have another variable just for that one function though. Maybe by initialising the count to zero and incrementing it right at the start of the while() loop, then you could perhaps just check for the count not being zero after the loop.

1 Like
17

Just let Bootstrap do the work and play around with the grid system to make it suit your needs (the col-* classes). I don’t think there is need for counter. Quick example:

<?php
// Image data from db
$images = array(
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image'),
        array('path' => 'test.png', 'title' => 'Test image')
);
?>
<html>
    <head>
        <title>BS test</title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
        
        <!-- jQuery required by Bootstrap -->
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        
        <!-- Bootstrap -->
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
        
    </head>
    <body>
        <div class="container">
            <div class="row">
                <?php 
                foreach ($images as $k => $img) {
                    echo '<div class="col-xs-6 col-sm-4 col-md-3">'.$img['title'].' '.$k.'<br/><img src="'.$img['path'].'"></div>';
                }
                ?>
            </div>
        </div>
    </body>
</html>
18

Yes, it’s the best you could do.
This solution will let you to separate concerns, leaving the Data Manipulation layer separated from the Presentation layer.
After getting your chunked array you can simply output it using 2 nested foreach loops in your template.

Whereas solution from @SamA74 is a horrible spaghetti that is mixing database API with markup, that looks as though it’s 1997, not 2017 now.

19

Thank you I will take a look.

I think (but could be wrong) that if I put all 60 odd items in one row I may end up with items not wrapping to the next row properly… but I appreciate your response.

I’ve also found this which I may try too.

<?php

    $numberOfColumns = 3;
    $bootstrapColWidth = 12 / $numberOfColumns ;

    $chunkArray = array_chunk($items, $numberOfColumns);
    foreach($chunkArray as $items) {
        echo '<div class="row">';
        foreach($items as $item) {
            echo '<div class="col-md-'.$bootstrapColWidth.'">';
            // your item
            echo '</div>';
        }
        echo '</div>';
    }  
?>

Thanks everyone, much appreciated.

1 Like
20

Did you try that code I gave? It’s wrapping the images on medium and larger screens in a rows of 4 images. On small screens its wrapping the images in “rows” of 3 images. And in extra small screens it will wrap 2 images on row. Well, they are not bootstrap “rows” but try it out and you see how it works. You can just copy paste the code and put in some image named “test.png” in the same directory with the script.

Bootstrap grid is 12 units wide. So when you tell the image divs to be for example col-md-3 it will put four images per “row” on medium and larger screens (34=12). If you wan’t to have different behaviour on large screens you could add class “col-lg-2” to the image divs. Then on large screens it will wrap 6 images on row (26=12).

Someone correct me if I am bullshitting. It’s been a while I dived into bootstrap and things might have also changed from that.

21

Thanks - I’ll try it this evening, I’m on another project for the next couple of hours. As soon as I get anywhere I will feedback to this thread.

in my head if I have all the items in one row, rather than many rows of 4 items, I’ll get issues with items clearing each other but if that’s not the case then your suggestion looks great :smile:

Thanks for the input it’s appreciated.

Cheers

Matt