How to retrieve not null data from one fetch_object

Question

I am creating a tourism site and I have a carousel on my page which slides up to 10 images.

I have 10 columns in my tours table called img_1, img_2 ... img_10.

In some cases, a row may have only 6 images, so only 6 images should be displayed in the carousel.

I want to retrieve them using fetch_object() or something else.

Here is my code:

<?php
$resImg = $db->query("select img_1, img_2, img_3, img_4, img_5, img_6, 
     img_7, img_8, img_9, img_10 from tours where up_id = 1 and img_1 is not null");
// I will leave other conditions of not null values blank this time
while($rowImg = $resImg->fetch_object()) { ?>
    <div>
        <img data-u="image" src="img/<?=$rowImg->img_1;?>" />
        <img data-u="thumb" src="img/<?=$rowImg->img_1;?>" />
    </div>
<?php } ?>

Please ignore any syntax errors or typos in the code, I just want to find a way to solve this problem.

Here is an example of carousel: https://www.gitours.ge/index.php?action=tours_georgia&full_id=22&lang=

score 0 · Answer 1 · answered May 02 '17 at 13:44

OK, this is how I did it:

  <?php
    $resFullTours = $db->query("$query");
    $rowFullTours = $resFullTours->fetch_object();
?>

<?php 
            $image1 = $rowFullTours->img_1;
            if (!empty($image1)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image1;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image1;?>" />
        </div>
        <?php } ?>
        <?php 
            $image2 = $rowFullTours->img_2;
            if (!empty($image2)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image2;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image2;?>" />
        </div>
        <?php } ?>
        <?php 
            $image3 = $rowFullTours->img_3;
            if (!empty($image3)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image3;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image3;?>" />
        </div>
        <?php } ?>
        <?php 
            $image4 = $rowFullTours->img_4;
            if (!empty($image4)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image4;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image4;?>" />
        </div>
        <?php } ?>
        <?php 
            $image5 = $rowFullTours->img_5;
            if (!empty($image5)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image5;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image5;?>" />
        </div>
        <?php } ?>
        <?php 
            $image6 = $rowFullTours->img_6;
            if (!empty($image6)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image6;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image6;?>" />
        </div>
        <?php } ?>
        <?php 
            $image7 = $rowFullTours->img_7;
            if (!empty($image7)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image7;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image7;?>" />
        </div>
        <?php } ?>
        <?php 
            $image8 = $rowFullTours->img_8;
            if (!empty($image8)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image8;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image8;?>" />
        </div>
        <?php } ?>
        <?php 
            $image9 = $rowFullTours->img_9;
            if (!empty($image9)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image9;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image9;?>" />
        </div>
        <?php } ?>
        <?php 
            $image10 = $rowFullTours->img_10;
            if (!empty($image10)) {
        ?>
        <div>
            <img data-u="image" src="img/gallery/<?=$image10;?>" />
            <img data-u="thumb" src="img/gallery/<?=$image10;?>" />
        </div>
        <?php } ?>

score 0 · Answer 2 · edited May 23 '17 at 12:26

Your posted answer is not DRY and fetch_object() will force you to nominate each column by name. Accessing the columns by their name will force you to write long-winded code blocks or shorter code blocks with variable variable naming (which most people try to avoid).

I have written a simple, DRY block of code using array_filter() on fetch_row() to generate image tags from non-null column values.

If your $up_id is user input, be sure to do some form of sanitizing before it is used in the query.

$up_id=2;
if(!$db=new mysqli("host","user","pass","db")){
    echo "Connection Error: ",$db->connect_error;  // do not echo when live
}elseif(!$result=$db->query("SELECT img_1,img_2,img_3,img_4,img_5,img_6,img_7,img_8,img_9,img_10 FROM `tours` WHERE `up_id`=$up_id")){
    echo "Query Syntax Error: ",$db->error;  // do not echo when live
}elseif(!$result->num_rows){
    echo "Query Logic Error: No Rows For $up_id";  // write your preferred message
}else{
    foreach(array_filter($result->fetch_row()) as $column){
        echo "<div>";
            echo "<img data-u=\"image\" src=\"img/$column\" />";
            echo "<img data-u=\"thumb\" src=\"img/$column\" />";
        echo "</div>";
    }
}

If you run that code on this table:

CREATE TABLE `tours` (
  `up_id` int(10) NOT NULL,
  `img_1` varchar(100) DEFAULT NULL,
  `img_2` varchar(100) DEFAULT NULL,
  `img_3` varchar(100) DEFAULT NULL,
  `img_4` varchar(100) DEFAULT NULL,
  `img_5` varchar(100) DEFAULT NULL,
  `img_6` varchar(100) DEFAULT NULL,
  `img_7` varchar(100) DEFAULT NULL,
  `img_8` varchar(100) DEFAULT NULL,
  `img_9` varchar(100) DEFAULT NULL,
  `img_10` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `tours` (`up_id`, `img_1`, `img_2`, `img_3`, `img_4`, `img_5`, `img_6`, `img_7`, `img_8`, `img_9`, `img_10`) VALUES
(1, 'imgA.jpg', 'imgB.jpg', 'imgC.jpg', 'imgD.jpg', 'imgE.jpg', 'imgF.jpg', 'imgG.jpg', 'imgH.jpg', 'imgI.jpg', 'imgJ.jpg'),
(2, 'imgK.jpg', 'imgL.jpg', NULL, 'imgN.jpg', 'imgO.jpg', NULL, 'imgQ.jpg', NULL, 'imgS.jpg', NULL);

ALTER TABLE `tours`
  ADD PRIMARY KEY (`up_id`);

ALTER TABLE `tours`
  MODIFY `up_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

Your output source code will look like this:

<div><img data-u="image" src="img/imgK.jpg" /><img data-u="thumb" src="img/imgK.jpg" /></div>
<div><img data-u="image" src="img/imgL.jpg" /><img data-u="thumb" src="img/imgL.jpg" /></div>
<div><img data-u="image" src="img/imgN.jpg" /><img data-u="thumb" src="img/imgN.jpg" /></div>
<div><img data-u="image" src="img/imgO.jpg" /><img data-u="thumb" src="img/imgO.jpg" /></div>
<div><img data-u="image" src="img/imgQ.jpg" /><img data-u="thumb" src="img/imgQ.jpg" /></div>
<div><img data-u="image" src="img/imgS.jpg" /><img data-u="thumb" src="img/imgS.jpg" /></div>

*I added linebreak after each pair for easier reading.

How to retrieve not null data from one fetch_object

2 Answers2