Recursive SELECT query?

Question

I am trying to create a PHP Chatbox, I implemented my SQL database like this :

ID      ID_sender      ID_parent      content
1       1              0              "Hello Mark"
2       2              1              "How are you Steve ?"
3       3              0              "Hi John, are you still coming tonight ?"
4       4              3              "Yes Elena, I will be there"
5       1              2              "I am good, what about you ?"

I would like to know if there was a way to implement a request that would select messages from a conversation and all the child messages, with one session variable in input. For example, if my session variable is 1, I would like to get the following conversation with my request

Steve : "Hello Mark"
Mark : "How are you Steve?"
Steve : "I am good, how are you ?"

I tried some requests with count() method but I was not able to get a good result. Thanks for your help.

which dbms do you use? MySQL? Which version? Take a look here https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Mark, Jun 12 '18 at 08:35
this may seems nice the only thing is that i can't separate two distincts conversation with this query — nicoolaslb, Jun 12 '18 at 12:39
Look at [nested sets](https://en.wikipedia.org/wiki/Nested_set_model) — Mulan, Jun 12 '18 at 13:13

score 0 · Accepted Answer · answered Jun 12 '18 at 09:00

The only way to do this in MySQL is with a loop, continually querying for the children.. either with JOINs or separate queries (unless you have a maximum number of messages per chat and a ridiculously large query).

I'd abandon this approach and use a column to separate the chats and a timestamp column to order the messages within the chat. Add any other columns you need, such as sender_id, as appropriate.

  SELECT *
    FROM chat_message
   WHERE chat_id = :id
ORDER BY sent_at ASC

I used your structure, simply to separate the conversation I added a column ID_receivern thanks — nicoolaslb, Jun 12 '18 at 12:58

Naman Bansal · Answer 2 · 2018-06-12T09:07:38.280

Please check the below sql statement -

SELECT content FROM your_table_name WHERE ID_sender = 1 or ID_parent = 1 order by ID_parent asc

Assuming you have different mechanism to fetch user name (may be from different table based on sender ID)

In this scenario you need to take care of one more case - suppose when you ID_sender is chatting with multiple persons at a time, then this query will fetch all results where ID_sender = 1 is chatting to.

score 0 · Answer 3 · answered Jun 12 '18 at 09:05

0

$sql = "select * from table where ID_sender = ".$_SESSION['session_variable']." OR ID_parent = '".$_SESSION['session_variable']."'";

answered Jun 12 '18 at 09:05

Shringiraj Dewangan

784
10
22

While this code snippet may be the solution, [including an explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Narendra Jadhav Jun 12 '18 at 09:57

score 0 · Answer 4 · answered Jun 12 '18 at 12:20

Note that if a person is chatting with multiple people then this may create a confusion. i will suggest you to create a logic where you can get logged in userid and (session variable for that matter) and concerned user id.

SELECT id_parent, id_sender, content
FROM (
SELECT *
FROM [input_table]
WHERE id_sender = [@session variable]

UNION ALL

SELECT *
FROM [input_table]
WHERE id_parent =  [@session variable]
) A
ORDER BY ID

hope this helps..

Recursive SELECT query?

4 Answers4