CODEWITHSUNDEEP
php
BigMike
BigMike

Reputation: 1093

While statement help (PHP)

My database contains both questions and answers. The questions have an ID (intQAID) and the responses have an ID (intResponseID). The intRespondID is the same as the intQAID ID that it is responding to. Each entry into the database has its own ID, which is intPostID.

What's i'm trying to do is write a query that will grab all this information and post it to a website using a while statement. However, the structure needs to be Question and underneath be the answer, until the while loop ends.

I can get the questions to post:

$question = mysql_query("SELECT *,
(SELECT cUsername FROM tblUsers tblU WHERE Q2.intPosterID = tblU.intUserID) AS username, 
(SELECT DATE_FORMAT(dPostDateTime, '%b %e %Y %H:%i')) AS post_time 
FROM tblQA Q2 WHERE intResponseID = 0 
ORDER BY Q2.dSortDateTime DESC, Q2.intQAID DESC LIMIT 40");

while($row = mysql_fetch_array($question))
{
echo "<tr class='forum'>";
echo "<td class='forum'>" . substr($row['cBody'], 0, 150) . "</td>";
echo "<td class='forum'>" . $row['cCategory'] . "</td>";
echo "<td class='forum'>" . $row['username'] . "</td>";
echo "<td class='forum'>" . $row['post_time'] . "</td>";
echo "</tr>";
}

But how can I get it to post the answer in the same while statement?

Should output like so:

Question 1:
  Answer 1:
Question 2:
  Answer 2:
Question 3:
  Answer 3:
etc....

Upvotes: 1

Views: 76

Answers (3)

Chud
Chud

Reputation: 77

If every question has only one answer, then you should get the data for both in the one query by joining the questions table with the answers table using intRespondID and intQAI.

Then each trip through the while loop has a , whatever you want on the first line, a , a , whatever you want on the second line, and finally a . So one trip through the loop doesn't have to just show one line of output.

Upvotes: 0

Daan
Daan

Reputation: 3348

Join the table on itself:

SELECT 
*,
(SELECT cUsername FROM tblUsers tblU WHERE Q2.intPosterID = tblU.intUserID) AS username, 
(SELECT DATE_FORMAT(dPostDateTime, '%b %e %Y %H:%i')) AS post_time 
FROM       tblQA Q2 
JOIN       tblQA AS tblQAjoin ON tblQAjoin.intRespondID = Q2.intPostID
WHERE      Q2.intResponseID = 0   
ORDER BY   Q2.dSortDateTime DESC, Q2.intQAID DESC 
LIMIT      40

I'm not sure why you would want to design your table this way, though. If there can just be one answer per question, why not simply have two columns in a row, one containing the question and the other one the answer?

Upvotes: 1

R2-Bacca
R2-Bacca

Reputation: 150

Try using a JOIN statement. As long as your questions and answers are one-to-one, you should be able to pull everything in one fell swoop.

http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 0

Related Questions