Select between two tables in PHP

Asked

Viewed 1,722 times

4

I am 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 friendships is in the table amizades, where when you’re friends, the column status = 2. How to list both tables to select all posts made by "friends"?

What I got 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 posts made by me and also by my friends (when amizade.status = 2), And then I’m failing, I don’t know how to select just my friends' posts and mine as well, because only mine appear.

Columns:

posts:

id | usuario | conteudo | data | hora

friendships:

id | usuario1 | usuario2 | status
mysql

  • I just read the answer of the link, and I understood that I will have to use INNER JOIN, but I do not know how to adapt to my case, since they are different cases. Could you give me an example? @Bacco

  • 1

    @Bacco worked perfectly, my mistake was in not using the OR within the ON to compare both users, however, there is another problem. If there is no friendship with status = 2 no post is shown, and the correct thing would be to show my posts regardless of whether you have any friends or not. How to tidy up?

  • 1

    @Bacco if you would like to have a look... http://answall.com/questions/99611/problema-com-l%C3%B3gica-ao-usar-left-Join/

  • Put the script for creating the 2 tables.

  • If you only want the results that are in the two tables do not use LEFT JOIN use INNER JOIN. LEFT will continue to bring all the data, but in the return of SQL the data referring to friendship table will be null when it is possible to relate the post to friendship, (but even so bring the posts).

1 answer

1

Follow what you wish

Creating

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);

Search 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 put the ID of the user you want to fetch the messages.

If you only want to search for friends, remove the a.usuario1 part. If you only want to search for yours, remove the a.usuario2 part.

Browser other questions tagged mysql

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