How to select 1 specific image of a post ? [using PHP and MySQL only]

Question

I'm new to php and mysql programming and new to this forum.

I want to know, if it is possible to select 1 specific image of a post to display. The posts are looking something like that:

+-------------------------+
| Post1: Title1...........|
| img1.......................|
| img2.......................|
| img3.......................|
+-------------------------+

+-------------------------+
| Post2: Title2...........|
| img4.......................|
| img5.......................|
| img6.......................|
+-------------------------+

How do I select for example "img3" of "Post1"?
I have 2 tables for many-to-one relationship:

CREATE TABLE posts (<br>
    id_post int(11) not null AUTO_INCREMENT PRIMARY KEY,<br>
    post_title varchar(100),<br>
    post_descr varchar(100)<br>
);

CREATE TABLE images (<br>
    id_img int(11) not null AUTO_INCREMENT PRIMARY KEY,<br>
    img_file varchar(100),<br>
    img_title text(100),<br>
    post_id int(11) not null REFERENCES posts(id_post)<br>
);

and this is how I display all images of a post:

$resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$rowx['id_post']);
 if(mysqli_num_rows($resultx) > 0) {
   while ($rowx = mysqli_fetch_array($resultx)) {
    echo "<img src='../folder_image_uploads/".$rowx['img_file']."' >";
    echo $rowx['img_title'];
   }
 }

and it works perfectly for what it should do, but again:
How do I select "img3" of "Post1" to echo it?
Is it even possible in my example?

Does the order of the pictures within one post / the whole thing matter? If so, you should mark the single images with numbers representing their order in the post. Then, you could select e.g. each 2nd image of a post. — Tobias F., Aug 10 '18 at 08:19
you can see my full "display.php" in my first question: https://stackoverflow.com/questions/51755878/sos-how-to-display-image-albums-in-posts-using-php-and-mysql-only — Coding Noob, Aug 10 '18 at 08:20
The images will be saved in the "images" table with a "post_id". There can be multiple images in each post, but I want integrate a image slider. For each "nav dot" I need to call 1 image or I would have the same image for each nav dot. The image slider is inspired by https://www.youtube.com/watch?v=z74ExMz-cWU . He also have a codepen: https://codepen.io/mayurbirle/pen/eEevBZ/ . The cool thing is, it works completely without JS. — Coding Noob, Aug 10 '18 at 08:34
supposed to be
in https://codepen.io/mayurbirle/pen/eEevBZ/. Means I have a post xy and in the post I have the Image slider, if more then 1 picture is uploaded. Was a idea, i hope it works. — Coding Noob, Aug 10 '18 at 08:59
So the slider requires a div with the images and stuff to be **one** image or can you just throw in all the images you need? — Tobias F., Aug 10 '18 at 09:01
I have a if statement, that when post xy contain only 1 image then it should display only the code, i wrote above. And when the post contain more then 1 image, then there should be shown a image slider which contains the image album. — Coding Noob, Aug 10 '18 at 09:09
Currently your CSS may only support five images, it will be a bit more complicated to do it for 'as many as needed', since you would generate CSS depending on the images. I'd add some part about that to my answer. — Tobias F., Aug 10 '18 at 10:02
Would it work, if I would make a image_slider.php for each cases? For example: if(mysqli_num_rows($resultx) == 2) ---> include 'image_slider_2.php' . And if the post would have for example 5 images. then: if(mysqli_num_rows($resultx) == 5) ---> include 'image_slider_5.php' . Or is it a stupid idea? — Coding Noob, Aug 10 '18 at 10:11
This won't be a good idea. I'll have a closer look at the CSS to check whether we can avoid generating CSS on the fly ^^ — Tobias F., Aug 10 '18 at 10:14

score 0 · Answer 1 · answered Aug 10 '18 at 08:56

To get single image in your example it can be done in many ways:

1- Either you can modify your query to get only one image in result as below:

$resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$rowx['id_post'] . " LIMIT 1");

2- Or you can exclude while loop and just get one row as below:

$rowx = mysqli_fetch_array($resultx);
echo "<img src='../folder_image_uploads/".$rowx['img_file']."' >";
echo $rowx['img_title'];

3- Or you can use mysqli_fetch_all function instead of mysqli_fetch_array and get all results in a multi-dimensional array at once and then use index in that array to access particular image as below:

$rowx = mysqli_fetch_all($resultx, MYSQLI_ASSOC);
echo "<img src='../folder_image_uploads/".$rowx[0]['img_file']."' >";
echo $rowx[0]['img_title'];

score 0 · Answer 2 · answered Aug 10 '18 at 11:10

You are correctly getting the images from your database for your post, but currently you can't handle the results that well, but there is a solution to that. First of all, you want to fetch all of the images for the current post, and then do your "magic stuff" (the image slider) with it. For this, you should also modify your query, since currently du to a missing WHERE in your query, you are selecting over all images for every post.

$result = mysqli_query($db, "SELECT * FROM posts");
while ($row = mysqli_fetch_array($result)) {
    echo "<div class=\"post_container\">";
    echo $row['post_title'];
    echo "<div class=\"image_container\">";

    // Get only the images assigned to this specific post
    $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " . $row['id_post']);
    if (mysqli_num_rows($resultx) > 0) {
        // get all images as an array
        $images       = mysqli_fetch_all(MYSQLI_ASSOC);
        $imageCount   = count($images); // Will come in handy
        $checkboxes   = '';
        $imagesString = '';
        $dots         = '';
        for ($i = 0; $i < $imageCount; $i++) {
            // Create the checkboxes
            $checkboxes .= sprintf('<input type="radio" id="i%d" name="images" %s/>', $i, ($i == 0 ? 'checked' : ''));
            // Div for Image
            $imagesString .= sprintf('<div class="slide_img" id="img_%d">', $i);
            // Image
            $imagesString = sprintf('<img src="../folder_image_uploads/%s">', $images[$i]['img_file']);
            // Prev & Back - Buttons
            $imagesString .= sprintf('<label class="prev" for="i%d"><span>&#x2039;</span></label>', ($i == 0 ? $imageCount : $i - 1));
            $imagesString .= sprintf('<label class="next" for="i%d"><span>&#x203a;</span></label>', ($i == $imageCount ? 0 : $i + 1));
            $imagesString .= sprintf('</div>');

            $dots .= sprintf('<label for="i%d" class="dots" id="dot%d"></label>', $i, $i);
        }

        echo $checkboxes . $imagesString . "<div id='nav_slide'>" . $dots . "</div>";
    }
    echo "</div></div>";
}

Note: For n images you still have to generate the CSS on the fly.

Thank you for your detailed answer. I will try to implement it in my code and let you know, if it works. — Coding Noob, Aug 10 '18 at 12:01
My major Div does close with this --> $imagesString .= sprintf(''); What is the usage of it? When I disable this string, then my major div is normal again. — Coding Noob, Aug 10 '18 at 14:26
but this string references to my major div, when it is activated — Coding Noob, Aug 10 '18 at 14:32

How to select 1 specific image of a post ? [using PHP and MySQL only]

2 Answers2