Tracking a conversation in PHP MYSQL

Question

Background: I have been working with twitter API. Have it setup so that when someone mentions/messages me, it gets sent to a chatbot. Bot make reply, I tweet it out using API. Each tweet has a unique status_id in addition to a field called rep_to_stat_id so a conversation looks like this:

person 'hi how are you?'          stat_id = 1 rep_to_stat_id = blank
    me 'ok, and you?'             stat_id = 2 rep_to_stat_id = 1
person 'are you stalking me'      stat_id = 3 rep_to_stat_id = 2
    me 'no you are stalking me'   stat_id = 4 rep_to_stat_id = 3

etc.

When I send to bot first time, make a random converstation ID (conv_id) so the bot can track the conversation.

Problem is tracking this, it's like a ladder, leading up to the original stat_id with no rep_to_stat_id, and has to include the common conv_id.

Up until now I have always had tables with a one to many type setup, I cannot get my head around what type of structure this is.

Is there an option in mysql to add to query to follow a ladder of each conversation to its first/last point?

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

There are many ways to store hierarchical data in relational databases.

One of the most common ways is to simply have a table with all the columns you mentioned (stat_id, rep_to_stat_id [nullable], and conv_id). The problem is, to retrieve the full tree up to the root node, you'd need a recursive function (in MySQL or your scripting language of choice), making several queries.

This Stack Overflow question should give you a nice overview of all the available options for modelling your tree structure.

edited May 23 '17 at 12:03

Community

1
1

answered Jun 11 '12 at 18:44

bfavaretto

71,580
16
111
150

That's exactly what I was looking for - ' hierarchical data'. THANKS! – owner66 Jun 11 '12 at 20:35

score 1 · Answer 2 · answered Jun 11 '12 at 18:41

Have one column tag the conversation_id, and another that tracks the iteration within that conversation.

person    text                       conversation_id    reply_num
------------------------------------------------------------------
0         "Hi"                       0                  0
1         "Hello, who are you"       0                  1
0         "I am a bot"               0                  2
1         "Goodbye"                  0                  3
0         "Hi"                       1                  0           //new conversation thread
1         "who is this?"             1                  1
0         "This is a bot"            1                  2
1         "leave me alone"           1                  3

Tracking a conversation in PHP MYSQL

2 Answers2