table relations and join

[Gozil]

Hi,
I got two questions thats been bugging me but I start with the first one as its quite much to explain.
I'm trying to create a LEFT JOIN on two tables and then output everything, I have no problems getting the information but once I want to output it I get a problem.
Lets say I select a topic and then do a LEFT JOIN on comments I now output it in php like this:
[PHP]$topics = new topicBinding();
foreach($topics->getTopics() as $topic) {
...
}[/PHP]

The sql SELECT will look something like this:
[PHP]$sql = "SELECT topics.name, comments.comment FROM topics " .
"LEFT JOIN comments ON topics.id = comments.topic_id " .
"WHERE topics.id = '1'";[/PHP]

And here is the problem: because each topic have more comments I will get a new $topic for each comment instead of each topic.. so the topic would output as many times as the the num_rows of the comment,
output example:
[HTML]ID NAME COMMENT
1 first topic comment 1
1 first topic comment 2[/HTML]

I will of course not want to output the topic over and over again and instead I want to control it like this:
[PHP]$topics = new topicBinding();
foreach($topics->getTopics() as $topic) {
...
foreach($topic['comment'] as $comment) {

}
}[/PHP]
Is this possible?

[Atli]

Hi.

The other option would be to get a list of all the topics and issue a separate query for each topic, to get a list of comments for the given topic.

Like, say:

$topics = mysqli_query("SELECT TopicID FROM topic");
while($topic = mysqli_fetch_assoc($topics)) {
  echo "Topic ID: ". $topicID ."\n";
 
  $query = "SELECT * FROM comment WHERE topicID = {$topic['TopicID']}";
  $comments = mysqli_query($query);
  while($comment = mysqli_fetch_assoc($comments) {
    echo " - ". $comment['CommentName'] ."\n";
  }
}
 

Which should generate a list, somewhat like this:

Topic ID: 1
 - Comment 1
 - Comment 2
TopicID: 2
 - Comment 3
 - Comment 4
 

[Gozil]

Yeah thats the way Im working right now but I thought I could do it all in one query and still get all the comments for each topic in a sub object so I could loop them inside the topic loop.

[Atli]

Database queries only return tables. There is really no reasonable way to get them to return something like a multi-dimensional array (beyond the 2 dimensional table format, that is.).

[Gozil]

Database queries only return tables. There is really no reasonable way to get them to return something like a multi-dimensional array (beyond the 2 dimensional table format, that is.).

Wont multiple queries slow down the process of getting the data or is it the only way to do it?

[Atli]

If the only way to get everything in one query is to return every row with a bunch of duplicate fields, then no. Multiple queries would probably be faster.

[Gozil]

Okay I guess I have to stick with it then :)

[cosmos411]

I remember having a situation like this in the past. I think something like CUBE or ROLLUP will work. I would give an example but that would take some digging around. I hope it's ok that I leave this for you to do. This link may help http://www.mysqldictionary.com/keywords/with-rollup

I hope this helps.

Gord