I have 2 tables (activities and questions).
Activities Table:
+-------------+------------+------------+
| activity_id | title | flow |
+-------------+------------+------------+
| 1 | Activity 1 | question-2 |
+-------------+------------+------------+
| 2 | Activity 2 | activity-3 |
+-------------+------------+------------+
Questions Table:
+-------------+-----------------+------------+
| question_id | question | flow |
+-------------+-----------------+------------+
| 1 | Lorem ipsum...? | activity-1 |
+-------------+-----------------+------------+
| 2 | Lorem ipsum...? | question-3 |
+-------------+-----------------+------------+
| 3 | Lorem ipsum...? | activity-2 |
+-------------+-----------------+------------+
What I am trying to do, is if I want to look up and see where a user flows after a certain question or activity, then look up that question or activities data, all in one query, how would I do that?
Is there a way in MySQL to analyze the flow field and pull data from the corresponding table and id.
So for instance, if I know a user is on question 2 (question_id: 2) and I want to look up the title/question and flow fields of the item that comes after question 2 (which is question 3 in this case (flow: question-3)), how would I do this in one query.
I hope this is making since. Can I tell MySQL that if it finds activity-1 in the flow field, then it needs to look up the information for activity 1 in the activities table. If it finds question-1 in the flow field, then look up the information for question 1 in the questions table.
If possible, is a complex query like this better than running 2 queries from PHP PDO? Would it be more optimized to look up the flow field, then analyze it in PHP and have a second query to grab the flow fields activity or question data?