Organize Questions & Answers from database in php and mysql
In my database I have questions and answers. Every post has a 'postID' and only answers have a 'responseID', which references the 'postID' of the question.
I can identify which answers go to which question since responseID = postID
I'd like to write some php code that would display the data in the following format:
Question:
- answer - answer - answerQuestion:
- answer - answer - answer...and so on
Additional Info:
The Questions and Answers are in the same tblQA table. The colums are QAID, PosterID, ResponseID, TimeStamp, Category, Body.So, every post has a QAID. A question would have a category, and an a开发者_StackOverflow社区nswer would have a responseID that equaled the questions QAID.
This would grab ALL questions:
SELECT * FROM tblQA WHERE category IS NOT NULLThis would grab all answers:
SELECT * FROM tblQA WHERE ResponseID IS NOT NULLLooping isn't necessary. You can do a SQL query that uses a JOIN
.
If your tables looks like this:
QuestionID Question
123 "What do you think about..."
124 "Who is your favorite..."
AnswerID Answer
123 "I think that..."
123 "I tend to think..."
124 "My favorite..."
You could craft a JOIN
query something like
SELECT * FROM Questions LEFT JOIN Answers ON QuestionID = AnswerID
This isn't tested, but that's the theory. What that will give you for results is something like
QuestionID Question Answer
123 "What do you think about..." "I think that..."
123 "What do you think about..." "I tend to think..."
There are a number of JOIN
s you can do depending on your RDBMS. Mostly the flavors have to do with which "side" of the join takes priority when there's a conflict or a null
value.
Read more about MySQL
here: http://www.tizag.com/mysqlTutorial/mysqljoins.php
Try this:
$sql="SELECT * FROM tblQA WHERE category IS NOT NULL";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
{
echo $row[Body];
$sql1="SELECT * FROM tblQA WHERE ResponseID IS NOT NULL and qaid = $row[responseid]";
$result = mysql_query($sql1);
while($row = mysql_fetch_assoc($result))
{
echo $row[Body];
}
}
精彩评论