[PHP] Best way to do this (sub selects?)

[motorpsychkill]

I have a query that returns a result set like the following:

TOPIC   QUESTIONANSWER
1   A   B
1   C   D
1   E   F
2   G   H
1   I   J
2   K   L
3   M   N

Presentation-wise in PHP, how would I go about making a table to display
results like (i.e. grouped by topic):

1
A   B
C   D
E   F
I   J



2
G   H
K   L

3
M   N


I've done this with two queries, the first selecting distinct topics and
then running a subquery on all question/answers pertaining to that topic.  I
was just curious if anybody was handing these situations differently.

Thank you!

-m

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Best way to do this (sub selects?)

[Richard Davey]

Hello motorpsychkill,

Friday, March 5, 2004, 1:47:59 AM, you 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.

-- 
Best regards,
 Richard Davey
 http://www.phpcommunity.org/wiki/296.html

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Best way to do this (sub selects?)

[joel boonstra]

On Fri, Mar 05, 2004 at 01:53:45AM +, 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   QUESTIONANSWER
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   QUESTIONANSWER
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=3td'.$topic.'/td/tr';
  }
  print trtdnbsp;/tdtd$question/tdtd$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