1

I'm new to php and mysql programming and new to this forum. I have a major problem with my website project. I've searched the whole internet for a solution, with no success. I hope here are some experts who can help me with my problem.

I want to learn, how to create a posting system, which does display image albums. By scrolling through posts, the posts should display a complete album each. It should looks like this example:

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

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

+-------------------------+
| Post3: Title3...........|
| img7....................|
| img8....................|
| img9....................|
+-------------------------+

IMPORTANT: display the images as images, not as a text list.

What I got so far:

MYSQL: (2 Tables for a many-to-one relationship)

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

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

PHP: (display.php)

<!DOCTYPE html>
<html>
<body>
<?php
$db = mysqli_connect("localhost", "root", "", "post_images");    

$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\">";

     $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images JOIN posts ON posts.id_post=images.post_id");
     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'];
        }
     }
     echo "</div>";
   echo "</div>";
}
?>
</body
</html>

What do I have to do, to get the output like in my example? - is my many-to-one relationship wrong? - do i have to use a many-to-many relationship? If so, how? - is my mysqli_query selection wrong?

I red in a other forum (and other image gallery topic, but different) a guy who said, he is not sure, but he thinks it is only possible to display 1 image in a php script, in a html tag ('img') and it would work with a separate table, but what am I doing wrong ?

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106
Coding Noob
  • 41
  • 1
  • 1
  • 7

1 Answers1

1

Your first SQL query is fine, but for the second one, you only want to select the images for the current post (row) in the outer while loop, so the second SQL query should be:

"SELECT img_file, img_title FROM images WHERE post_id = $row[id_post]"

Update for showing only img3 of post1:

<!DOCTYPE html>
<html>
<body>
<?php
$db = mysqli_connect("localhost", "root", "", "post_images");    

$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\">";

    if ($row['id_post'] == 1) {
        $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$row['id_post']. " AND img_title = 'img3'");
    } else {
        $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$row['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'];
        }
     }
     echo "</div>";
   echo "</div>";
}
?>
</body
</html>

I've placed the second SQL query in an if statement, which checks if the id of the current post is equal to 1. If it is, then the SQL query only selects the row with "img3" in it. (And if it's not it executes the previous SQL query which selects all the rows.)

Of course this only works if you know the id of the post and the title of the image that you want to display. A more generic solution for always displaying only the third image of the first post would be something like this:

<!DOCTYPE html>
<html>
<body>
<?php
$db = mysqli_connect("localhost", "root", "", "post_images");    

$result = mysqli_query($db, "SELECT * FROM posts ORDER BY id_post");
$rows = mysqli_fetch_all($result,MYSQLI_ASSOC);
foreach ($rows as $key => $value)
   echo "<div class=\"post_container\">";
     echo $row['post_title'];
     echo "<div class=\"image_container\">";

    if ($key == 0) {
        $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$row['id_post']. " ORDER BY id_img LIMIT 1 OFFSET 2");
    } else {
        $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$row['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'];
        }
     }
     echo "</div>";
   echo "</div>";
}
?>
</body
</html>

Here I've stored the entire result of the first SQL query in an array, because then the keys of the array correspond with the number of each post minus one. If $key = 0, the current row is the first post and then we use the SQL query that selects only the third image from the images table. If $key isn't 0 we use the other SQL query that selects all of the images.

In the new SQL query, LIMIT 1 means select 1 row only, and OFFSET 2 means start with row 3 (counting starts at 0, so offset 2 returns row 3).

I've added ORDER BY id_img to make sure images are always returned in the same order, the order in which they were added to the database. (And I've done the same with id_post in the first query.)

Marleen
  • 2,245
  • 2
  • 13
  • 23
  • Thank you very much :) I was looking for this solution for long time ... – Coding Noob Aug 08 '18 at 21:59
  • 1
    (for other people with the same problem) what i changed in my example: $resultx = mysqli_query($db, "SELECT img_file, img_title FROM images WHERE post_id = " .$rowx['id_post']); – Coding Noob Aug 08 '18 at 22:22
  • What do I have to write in the SELECT statement, if i only want to display "img3" of "post1"? Is it even possible? – Coding Noob Aug 10 '18 at 07:31
  • Updated my answer, hopefully this'll help. :) – Marleen Aug 10 '18 at 09:08
  • Thank you Stanzi for the solution and the updated answer :) At the end, I have to call every single image separately of each post with 2+ images. What I'm trying to do is, I want combine my posting system with an image slider, inspired by https://codepen.io/mayurbirle/pen/eEevBZ/. This works completely without JS. If a post does contain only 1 image, then it shows the code which you can see above, but if a post contain 2 or more images, then I want the images of this post in an image slider. In the codepen example, he needs a link of each image separately. – Coding Noob Aug 10 '18 at 15:36
  • It's not too difficult to print a single image in the manner you're doing above and a set of multiple images in a div for use with a slider. But the problem with a non-JavaScript slider as you've linked above is that it uses ids to work. Every image on your page is going to need a unique id, and you'd have to generate the CSS too for it to work, which is going to get really difficult if you have more than one slider on the page. You'd be better off looking for a JavaScript solution for the slider instead and then it shouldn't be too hard to get this to work. – Marleen Aug 10 '18 at 16:05
  • I think this slider should work with multiple sets of images on the same page: https://bxslider.com/ – Marleen Aug 10 '18 at 16:11
  • I had the idea that I limit, how many images per post can be uploaded to max10. Then there would be a placeholder for the image slider. The image slider would be saved in a separate PHP and the placeholder for the sliders would contain a "include" string. I would create for each case a different slider. For example: if(mysqli_num_rows($resultx) == 1) then "normal code". if(mysqli_num_rows($resultx) == 3) then ---> include 'image_slider_3'. if(mysqli_num_rows($resultx) == 7) then ---> include 'image_slider_7'. Do you think, it is realistic? I want JS as less as possible on the website. – Coding Noob Aug 10 '18 at 16:25
  • The main problem with the non-JavaScript slider is with the ids, even if you'd use include files you'd still have to give every single image on the page a unique id (pass those to the include file) and generate the custom CSS for each individual slider, so that a click on the label shows the corresponding image (this is done by id). (And ideally you'd also want to group all of this css together in the html header or in a separate file, that'll be another problem.) I'm not saying it's impossible, but it'll lead to a lot of hard to understand, difficult to maintain code. I wouldn't recommend it. – Marleen Aug 10 '18 at 17:04
  • Hmm ok, thank you for the information anyway. Seems I have to use a js slider. – Coding Noob Aug 10 '18 at 17:09
  • I've tried some vanilla JS image slider, but still have the problems:
    - 1.) I only can have 1 slider on the website (because of "include_once img_slider.php", when I used "include", the website went going crazy)
    - 2.) I still have to know, how many images are in 1 post.
    I tried to make those static slider to a dynamic one (database based slider):
    https://codepen.io/mayurbirle/pen/eEevBZ/
    https://www.youtube.com/watch?v=Ipa9xAs_nTg
    And I want to avoid third-party stuff, like bootstrap etc.     – Coding Noob Aug 13 '18 at 13:20
  • (ノ°Д°）ノ ︵ ┻━┻ Should I start a new question and paste my code? – Coding Noob Aug 13 '18 at 13:22
  • Yes, I think you should start a new question for the slider problem, one subject per question makes it easier to find for others with the same problem. Also your question will be at the top of the list again and more people will see it so you'll have an answer much faster. :) – Marleen Aug 13 '18 at 15:01
  • Ok, I opened a new question: https://stackoverflow.com/questions/51827471/how-to-create-multiple-dynamic-image-slider-using-php-mysql-vanilla-js-onl – Coding Noob Aug 13 '18 at 17:17