View images that are in another table

Question

Hello, I have the two tables below:

Table IMAGES

id_imagens  | diretorio_imagem | post_id
       1        imagem1.jpg          1
       2        imagem2.jpg          1
       3        imagem3.jpg          1
       4        imagem4.jpg          1
       5        imagem5.jpg          2
       6        imagem6.jpg          2
       7        imagem7.jpg          2
       8        imagem8.jpg          2

Table POSTS

id_post   |          slug        |   titulo     
 1           titulo_do_post         Titulo do Post 
 2           titulo_do_post_2       Titulo do Post 2

In the post.php page I search for the posts table Slug:

$slug = $_GET['slug'];
$stmt = $db->prepare("SELECT * FROM posts WHERE slug=:slug");
     $stmt->execute(array(":slug"=>$_GET['slug']));
     while($row=$stmt->fetch(PDO::FETCH_BOTH))
{
print utf8_encode($row['titulo']);

}

The above code takes the url’s Slug and searches the DB.

www.meusite.com.br/titulo_do_post_2

The php code above will take the titulo_do_post_2 snippet and search in DB something identical. So far ok, the next step is through Slug to find out what the id of the post and with the id of the post ai yes do an Inner Join

I would like by Slug to know the id_post and return from the table IMAGES that has the identical post_id to display all images

Gave bug don’t know how to do, Inner Join still have a lot of difficulty to use

I appreciate help

Answer 1

You don’t need to use John in this case, try:

SELECT i.diretorio_imagem
FROM Posts p, Imagens i
WHERE p.slug='titulo_do_post_2' AND i.post_id = p.id_post;

Will return the directory of all images with which post_id is equal to id_post of the post by titulo_do_post_2.

The code would look something like this:

<?php

    $servername = "";
    $username = "";
    $password = "";
    $database = "";
    $dbport = 3306;
    $db = new mysqli($servername, $username, $password, $database, $dbport);

    $slug = $_GET['slug'];

    $sql = "SELECT i.diretorio_imagem FROM POSTS p, IMAGENS i WHERE p.slug='$slug' AND i.post_id = p.id_post";

    $results = $db->query($sql);

    while($row = $results->fetch_row()) {
        // Mostrando o diretório das imagens
        echo "<p>" . $row[0] . "</p>";
    }

    $db->close();
?>

Answer 2

As you are using PDO, replace it with the code below. In the result will come all the necessary data in a single query.

$slug = $_GET['slug']; 
$stmt = $db->prepare("SELECT * 
                      FROM posts, imagens 
                      WHERE posts.id_post = imagens.post_id 
                      AND slug=:slug"); 
$stmt->execute(array(":slug"=>$_GET['slug'])); 
while($row=$stmt->fetch(PDO::FETCH_BOTH)) 
{ //print utf8_encode($row['titulo']); 
print_r($row);
}