I want to create a flowchart with unordered list with php and mySQL.
Table - Questions
id | question | answer_id
Table - Answers
id | answer | question_id
Every question has a reference to one answer_id. And the answer_id has a reference to a question_id (see the attached image).
The php so far:
// Starts with the first question (answer_id=0)
$sql = db_query("SELECT * FROM questions WHERE answer_id='0'");
while ($row = mysqli_fetch_assoc($sql)){ ?>
<ul>
<li>
<?php echo $row['question'];
$question_id = $row['id'];
}
$sql = db_query("SELECT * FROM answers WHERE question_id='$question_id'");
while ($row = mysqli_fetch_assoc($sql)){
$question_id = $row['id'];
?>
<ul>
<li>
<?php echo $row['answer'];?>
<?
$answer_id = $row['id'];
$sqlf = db_query("SELECT * FROM questions WHERE answer_id='$answer_id'");
while ($rowf = mysqli_fetch_assoc($sqlf)){ ?>
<ul>
<li>
<?php echo $rowf['question'];?>
</li>
<?
$question_id = $rowf['id'];
$sqls = db_query("SELECT * FROM answers WHERE question_id='$question_id'");
while ($rows = mysqli_fetch_assoc($sqls)){ ?>
<ul>
<li>
<?php echo $rows['answer'];?>
</li>
</ul>
<?
}
}
}
I have a total brainfreeze about this. How can I get a loop to print out questions with child answers and child questions and so on?
This helped me! Printing down all row elements as tree in PHP
Thanks Naruto for the guidance to recursive functions :)
For writing functions, see php documentation, e. g. here or here. A recursive function is one that calls itself again. Be careful with, you can end up in endless recursion (until stack overflow, at least...), if you do not do it right. The important thing is a condition where you break the recursion. This could be if your answer's question_id is null (-> no follow up question).
Additionally - although you did not ask for - I propose a redesign for your database. From the image you show, it appears that each answer belongs to exactly one question, but each question can have more than one answer.
So have the table Questions only as (id, question) and have table answers as (id, answer, answered_question, followup_question). You avoid to have duplicates in your database then (but need a join to get the answers to a question).