On Fri, Mar 05, 2004 at 01:53:45AM +0000, Richard Davey wrote:
> m> I've done this with two queries, the first selecting distinct topics and
> m> then running a subquery on all question/answers pertaining to that topic. I
> m> was just curious if anybody was handing these situations differently.
>
> You should find that doing a GROUP BY and then ORDER BY on the Topic
> field gives you the results back in the right sequence for what you
> want. Your display life in PHP will then be much simpler.
ORDER BY will definitely help, but GROUP BY will not be useful here.
The original query results looked like:
TOPIC QUESTION ANSWER
1 A B
1 C D
1 E F
2 G H
1 I J
2 K L
3 M N
By adding "ORDER BY topic" or something similar[1] to the query, you can
get your results to look like this:
TOPIC QUESTION ANSWER
1 A B
1 C D
1 E F
1 I J
2 G H
2 K L
3 M N
I'd also recommend adding a secondary ORDER BY to make sure that
QUESTION is ordered properly. The example results wouldn't change, but
presumably those aren't ordered yet.
However, GROUP BY will do unexpected things to the results, since the
point of this query isn't to perform aggregate operations[2] on rows
(SUM, AVG, etc...), it's simply to display all the data for all
questions. If you do a GROUP BY, MySQL won't quite know what to do.
To get this to display properly, you'll want to keep track of what topic
you previously displayed in your loop, and only display a new one if the
previous one is different. Something like:
<?php
# do query, so $results contains MySQL result set
$last_topic = '';
print '<table>';
while ($row = mysql_fetch_assoc($results)) {
# for convenient variable names
extract($row);
# only show the header if the topic has changed
if ($topic != $last_topic) {
print '<tr colspan="3"><td>'.$topic.'</td></tr>';
}
print "<tr><td> </td><td>$question</td><td>$answer</td></tr>";
# store the topic for next time around
$last_topic = $topic;
}
print '</table>';
?>
HTH!
[1] http://www.mysql.com/doc/en/SELECT.html
[2] http://www.mysql.com/doc/en/GROUP-BY-Functions.html
--
[ joel boonstra | gospelcom.net ]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
