Matthew Campbell Matthew Campbell - 4 months ago 8
MySQL Question

MySQL & PHP: querying 3 rows at a time

I'm working on a photo gallery using a database. On each line 3 images are displayed before everything resets. It would be in a

<img>/<img>/<img>/<div>
format.

So when looking at the source code you'd have:

<img src="1" alt="">
<img src="2" alt="">
<img src="3" alt="">
<div class="clear-both"></div>


And it would keep repeating this for every row in the data base. So 9 images would be:

<img src="1" alt="">
<img src="2" alt="">
<img src="3" alt="">
<div class="clear-both"></div>
<img src="4" alt="">
<img src="5" alt="">
<img src="6" alt="">
<div class="clear-both"></div>
<img src="7" alt="">
<img src="8" alt="">
<img src="9" alt="">
<div class="clear-both"></div>


I figured I could use
while
to handle this. But I don't know what SQL or PHP statement to add. I thought about using
WHERE
,
GROUP BY
and
COUNT
, but none of these work.

If possible, I also want it to be able to handle rows with just 1 and 2 images. Meaning if there are 4 rows in the table, then it would display:

<img src="1" alt="">
<img src="2" alt="">
<img src="3" alt="">
<div class="clear-both"></div>
<img src="4" alt="">
<div class="clear-both"></div>


And if there were 8, then it would display:

<img src="1" alt="">
<img src="2" alt="">
<img src="3" alt="">
<div class="clear-both"></div>
<img src="4" alt="">
<img src="5" alt="">
<img src="6" alt="">
<div class="clear-both"></div>
<img src="7" alt="">
<img src="8" alt="">
<div class="clear-both"></div>


Thanks for the help.

PHP



$Database4 = new mysqli("localhost", "user", "pass", "Photography");
$Photos = $Database4 -> query("SELECT *
FROM `Year2016`
-- ORDER BY DateTaken
;");

while ($DataRows = $Photos -> fetch_array()) {
echo '<img src="photography/small/' . $DataRows["file"] . '" alt="">' . "\n";
}

echo '<div class="clear-both"></div>' . "\n";


SQL



CREATE TABLE `Year2016`(
`file` text COLLATE utf8_bin NOT NULL,
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;

INSERT INTO `Year2016`(`file`)
VALUES
('red-1.png'),
('red-2.png'),
('red-3.png'),
('red-4.png'),
('red-5.png'),
('red-6.png');

Answer

This is easy as a pie:

$i = 1;    // special counter
while ($DataRows = $Photos -> fetch_array()) {
    echo '<img src="photography/small/' . $DataRows["file"] . '" alt="">' . "\n";
    if ($i % 3 == 0) {
        echo '<div class="clear-both"></div>';
    }
    $i++;
}
// the last one if last `$i` value is not divided by 3
if ($i % 3 != 1) {
    echo '<div class="clear-both"></div>';
}