Display images that are in another table

Question

Display images that are in another table

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Hello, I have the following two tables:

IMAGES table

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

POSTS Table

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 table slug posts:

$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 code above takes the url's slug and searches the DB.

www.meusite.com.br/titulo_do_post_2

The above php code will take the title titulo_do_post_2 and look at the DB something identical. So here it is ok, the next step is through the slug find out what the id of the post and with the id of the post yes yes make an inner join

I would like the slug to be able to know the id_post and return from the IMAGES table that has the identical post_id to display all images

I do not know how to do this, inner join still have a lot of trouble to use

Thank you for your help

php

asked by anonymous 17.03.2016 / 22:36

2 answers

Vb.Net - Total record in descending order How to use the SharedPreferences of Android? Can I choose multiple file names?

score 0 · Answer 1

You do not need to use join 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;

It will return the directory of all images with post_id equal to id_post of slug post 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();
?>

score 0 · Answer 2

Since you are using PDO, replace 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);
}