Do SELECT between two tables in PHP

Navigation
4

I'm currently using the following query to select all saved posts in the database: "SELECT * FROM postagens ORDER BY id DESC LIMIT 7" , however, I want to select only the posts made by "friends".

The relationship of friends is in the amizades table, in which when they are friends, the status = 2 column. How do you relate both tables to select all posts made by "friends"?

What I've achieved so far: 

SELECT
   postagem.id, postagem.usuario, postagem.conteudo, postagem.data, postagem.hora,
   amizade.usuario1, amizade.usuario2, amizade.status
FROM
   postagens postagem
LEFT JOIN
   amizades amizade ON postagem.usuario = amizade.usuario2
WHERE
   amizade.status = 2
ORDER BY
   postagem.id DESC
LIMIT 10

However, I want to select all the posts made by me and my friends (when amizade.status = 2 ), and then I'm failing, I do not know how to select only my posts and my friends' appear.

Columns:

posts : 

id | usuario | conteudo | data | hora

Friendships : 

id | usuario1 | usuario2 | status
    
asked by anonymous 20.11.2015 / 23:39

1 answer

1

Follow what you want

Creation 

create table postagens (
  id int primary key auto_increment,
  usuario int,
  conteudo text);

create table amizades (
  id int primary key auto_increment,
  usuario1 int,
  usuario2 int,
  status int);

insert into postagens (usuario, conteudo) values
(1, 'aksdjasdkasdj'),
(2, 'ja-s09920masd'),
(1, '90123091231092'),
(3, 'Nao devo ser exibidor');

insert into amizades (usuario1, usuario2, status) values
(1, 2, 2);

Find the data 

SELECT
        p.*,
        case when p.usuario = a.usuario1 then 'eu' else 'amigo' end as quem_enviou
FROM postagens p
INNER JOIN amizades a ON p.usuario = a.usuario1 OR p.usuario = a.usuario2
where (a.usuario1 = 1 or a.usuario2 = 1) and a.status = 2;

Where is the user1 = 1 or a.user2 = 1 enter the user ID you want to fetch the messages from.

If you want to search only from friends, remove the user1 part. If you want to search only yours, remove the part of the user2.

    
22.11.2015 / 02:29
requestAnimationFrame, setInterval, and setTimeout Installing packages in Rstudio