Select data from two tables

Asked

Viewed 886 times

1

I’m having trouble relating data from two tables.

I have two tables. Posts table and Favorites table.

Tabela Favoritos:
id, id_utilizador, id_post
1   109            20 
2   200            20
3   105            104   
4   109            150
-------------------------------------------
Tabela Posts:
id, id_post, id_owner, titulo, discricao
1   20       123            qqcoisa  qqcoisa
2   150      321            xxxxx    xxxxx   


--------------------------------------------

I wanted to present on a page the user’s bookmarks id = 109, including the title and table of posts? 

utilizador post titulo descricao
109        20   qqcoisa qqcoisa
109        150  xxxxxx  xxxxx
php

4 answers

3


I think what you want to do is an INNER JOIN:

SELECT Favoritos.id_utilizador, Posts.id_post, Posts.titulo, Posts.descricao 
FROM Favoritos 
INNER JOIN Posts ON Favoritos.id_utilizador=Posts.id_utilizador;

In the SELECT the fields you want to obtain are placed, not forgetting the name of the table to which the field belongs.

In the FROM you name the tables you want to join and the way they’ll be together (INNER JOIN, OUTER JOIN, LEFT JOIN or RIGHT JOIN). Then you tell them where they’ll be together, that is, the common element in the two tables.

You can still add one WHERE conditions, such as in SELECT you have to put the table name and field name (Ex: Posts.id_post = 20).

  • 1

    Thanks @Rui Cardoso , already knew this INNER JOIN, but at your expense I could understand better how it works. Thanks. Hug!

2

Basically that would be.

Using PDO:

$buscar = $suaConexao->prepare("SELECT * FROM posts LEFT JOIN favoritos ON posts.id = favoritos.id_utilizador");
//Nessa query sera buscado tudo na tabela posts que tenham um ID igual a um id_utilizador da tabela favoritos

$buscar->execute(); // Executa a busca
$linha = $buscar->fetchAll(PDO::FETCH_ASSOC); // Retorna o resultado como array associativo
foreach($linha $resultado): // Inicia o loop para pegar todos os dados
echo $resultado['id_owner'];
echo $resultado['titulo'];
echo $resultado['descricao'];

2

0

Use the INNER JOIN if you want to bring all the fields of the other table you are relating use the LEFT JOIN.

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.