Controller
New Member
I want to understand how stackoverflow Question Answers System Works. Stackoverflow Question Answer System is very fast and efficient. I am developing a political forum site. And I also want to implement such kind of comment system in my site. So, I want to understand stackoverflow system. Suppose there are 4 tables with the following fields \[code\]1. tbl_users (user_id, user_name)2. tbl_questions (question_id, question_body, user_id (who posted the question),question_time)3. tbl_answers (answer_id, question_id (to link with question), answer_body, user_id (who posted the answer), answer_time)4. tbl_comments (comment_id,user_id(who posted the comment), answer_id(to link with answer), comment_body, comment_time)\[/code\]Suppose Following Query will retrieve the the Question from datbase\[code\]$question_qry = "SELECT tbl_questions.*, tbl_users.user_name, ";$question_qry .= "tbl_users.user_id FROM tbl_questions INNER JOIN tbl_users ";$question_qry .= "on tbl_questions.user_id = tbl_users.user_id "; $question_qry .= "WHERE question_id = {$question_id}"; \[/code\]Suppose here is the Query for the Answers\[code\]$answers_qry = "SELECT tbl_answers.*, tbl_users.user_name, tbl_users.user_id, ";$answers_qry .= "tbl_comments.* FROM (tbl_answers INNER JOIN tbl_users on ";$answers_qry .= "tbl_answers.user_id = tbl_users.user_id) LEFT JOIN ";$answers_qry .= "tbl_comments on tbl_answers.answer_id =tbl_comments.answer_id ";$answers_qry .= "WHERE tbl_answers.question_id = {$question_id}";\[/code\]Note: (In this Post I am ignoring the comments under the question post intentionally)Now, Here I am confused. I want to know that When an Answer is shown, if there are some comments on this answer in the tbl_comments, they must also be displayed under the answer. Please Check my $answers_qry and Tell me that is this query right to achieve this purpose. If not Please suggest me the correct query.And Please tell how this all process is from efficiency perspective? I mean there are some Table Joins in the queries.