How to query data from a table using INNER JOIN in a sub-query in PHP

Asked

Viewed 764 times

0

I have the following SELECT to select data from a table (chat) in a chat system:

SELECT * FROM (SELECT * FROM chat WHERE id_chat = '$chat_id' ORDER BY id DESC LIMIT 10) S WHERE id_chat = '$chat_id' ORDER BY id ASC LIMIT 10

The same makes the query returning the last ten messages to display them in decreasing form.

In this table (chat), there’s a column usuario where it represents the id of the user who sent the message.

I would like to, from the id of the user who sent the message, be able to return the data of such user (table usuarios).

Table example usuarios:

id | nome  | foto
1  | Lucas | perfil.jpg

What is the best way to do this using the above query? LEFT JOIN? INNER JOIN? And, how to do?

php sql pdo select join

2 answers

2


I don’t know exactly what table structure you have but let’s assume it’s this

tb_usuario
- id_usuario
- nome
- foto

tb_chat
- id_chat
- id_usuario
- tx_msg
- dt_envio

id_user being rigid and necessary

SELECT
    U.nome,
    U.foto,
    C.tx_msg,
    C.dt_envio
FROM
    tb_chat C
    INNER JOIN tb_usuario U ON U.id_usuario = C.id_usuario
WHERE
    id_chat = '{$id_chat}'
ORDER BY
    dt_envio DESC

The INNER already restricts to QUERY to return only results that have id_user

Example

- Lucas     |   perfil1.jpg     | teste msg  | 12/01/2016 08:20:14
- Rafael    |   perfil2.jpg     | teste msg2 | 12/01/2016 08:24:37

id_user not being rigid

SELECT
    U.nome,
    U.foto,
    C.tx_msg,
    C.dt_envio
FROM
    tb_chat C
    LEFT JOIN tb_usuario U ON U.id_usuario = C.id_usuario
WHERE
    id_chat = '{$id_chat}'
ORDER BY
    dt_envio DESC

The LEFT says to give preference to the content of the left in case tb_chat, thus if there is no link with tb_usuario the columns belonging to it resewelling NULL

Example

-           |                   | teste msg  | 12/01/2016 08:20:14
- Rafael    |   perfil2.jpg     | teste msg2 | 12/01/2016 08:24:37

Note

  • If you use LEFT and id_usuario should be rigid, it will be necessary to WHERE

    WHERE
    id_chat = '{$id_chat}'
    AND id_usuario IS NOT NULL

  • I believe that using the example of INNER JOIN will work, since there will always be a link between the two tables (because I will have to consult the data of the user who sent the chat, and this user will always be present in both tables), however, how to adapt this solution to my query existing (present in the question), bearing in mind that I will have to return the last ten messages in descending order?

  • @Igor I don’t understand why you’re wearing a two querys, and you can use only the subQuery, to adapt my query only changes the SELECT * and add LIMIT 10.

1

This depends on the view you will create. In case it may be a INNER JOIN to the chat table. As long as all chats have a user.

Follow an example:

Select * 
From 'chat' 
INNER JOIN 'usuarios' ON 'chat'.$chave_estrangeira_do_id_entre_aspas = usuarios.id 
ORDER BY 'chat'.'id' ASC LIMIT 10

If you want to show messages without users for example do not need user for chat or anything, can use the left join.

  • All chats have a user in all cases. And in this case, as this query would be, adapting it to my current query? SELECT * FROM (SELECT * FROM chat WHERE id_chat = '$chat_id' ORDER BY id DESC LIMIT 10) S WHERE id_chat = '$chat_id' ORDER BY id ASC LIMIT 10

  • You need Mysql to follow how it would look Select * From 'chat' INNER JOIN 'users' ON 'chat'. $chave_estrangeira_do_id_entre_aspas = usuarios.id ORDER BY 'chat'. 'id' ASC LIMIT 10 .. Remember that the * in select is not very good put what you need to show, as chat. 'message', 'user'. 'name'

Browser other questions tagged php sql pdo select join

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