How to recover values from two tables in Mysql

Question

is the first time I ask a question here, and I’m having the following difficulty.

I’m doing my TCC info, it’s a website for students and such... it’s like Facebook sacks? I locked in the part where PHP retrieves data from Mysql 'post' table to appear in the timeline.

I tried so :

SELECT * FROM `usuario`, `post` WHERE `usuario`.`id` = '[ID_DO_USUÁRIO]'

And using PHP’s Var_dump() I get this result:

I’m getting all the user data unnecessarily, where what I just wanted was the name, surname, and some arguments...

Then I tried so:

(I must have missed this class)

SELECT `id`, `nome`, `sobre_nome`, `foto` FROM `usuario`, SELECT * FROM `post` WHERE `usuario`.`id` = '9223372036854775807'

And give me this mistake:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM `post` WHERE `usuario`.`id` = '9223372036854775807' LIMIT 0, 25' at line 1 

If you noticed, in the result of Var_dump... I get two results that is equivalent to the user ID (unnecessary)

This is personal, I don’t know if they will take the logic, but I just want to get some (not all) arguments from the table 'user' and the table 'posts' without having to do 2 Querys (I don’t know if this is how you speak...).

In short, Socorro kk.

Answer 1

If I understand correctly, you want to relate these two tables.

Then you can use the Inner Join in the query, for example:

SELECT nome, sobre_nome, foto
FROM usuario
INNER JOIN post ON usuario.id = post.idusuario;

Take a look there for a better understanding: https://www.w3schools.com/sql/sql_join_inner.asp

Answer 2

you can use JOIN to join the results of the two tables before from you specify all the columns you want as return.

SELECT id, name, surname, photo FROM post INNER JOIN usuario ON usuario.id = post.id_usuario_post WHERE usuario.id = 9223372036854775807

Learn more. https://www.w3schools.com/sql/sql_join_inner.asp