How to use a foreach with a while loop

[kuau]

I thought this would be fairly easy but I am having trouble.

I have a list of 200 member names. The members can belong to more than one category. There is a member table, a category table, and a membercat table that has 2 fields: member_id and cat_id.

I would like to list the members grouped by category with a subheading every time the category changes.

The SQL command I tried to join the 3 tables produced 66,220 results (!). So I am having trouble with that. Or do I do the SQL command on just 2 tables inside the while loop? I'd like to learn the best way to do it. I read the php manual but...

I figure there will be a foreach loop for the categories and a while loop inside to list the members (?). Is it something like this? Thanks for any help. :)


$sql = "SELECT * FROM category";
$resultcat = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mysql error: ".mysql_error());

foreach ($cat_id as $category){
while($member = mysql_fetch_assoc($result2)){
echo $member['company'];
}
}

[djr33]

Can you post some sample output of what you'd like to get from the database as a final result?
I understand the structure of the DB but not the goal.

The foreach(while()) setup above looks a little complex. The approach might not be wrong, but I think the easier way to go would be to use a while inside a while:

while ($result1 = mysql_fetch_assoc($query1)) {
while ($result2 = mysql_fetch_assoc($query2)) {
//do stuff here
}
}

As is the foreach loop is operating based on an array that does not exist-- you could always run through the results first (in a while loop) and store them to an array THEN do foreach(while()), but I don't see an advantage and it would be more code.
You can have more than one mysql query at the same time, as long as they are working within different variables.

[kuau]

Hi Daniel! :)

The easiest way to show you what I am trying to do is show you what it is at the moment. When I list all the members at once, the list looks like this:

http://www.mauiweddingclub.com/php/all-members.php

What would be more useful is a list grouped by categories such as this:

Photographers
Aloha Images
Joe's Photography
Maui Wedding Memories

Musicians
Maui Underground
Elite Productions

Flowers
Paia Flower Shop
Nui's Flowers

etc etc. I was able to do it in a situation where it was a one-to-one relationship between the member and the category, but as soon as the 3rd table was thrown in, I lost my grip. Thanks so much for lending your mastery once again. e :)

[kuau]

Dear Daniel:

I got it to work using your 2 while loops. Thanks a million!!! e :)

PS. I don't know how to change the status to "Resolved."

[djr33]

You should be able to edit the original post and choose 'resolved' from the dropdown menu near the title. Then just save that post.


Glad it worked out.


By the way, your idea of a foreach loop is basically right except that in the case of mysql, a while loop is essentially a foreach loop.
Think of it this way: "continue while my mysql query keeps returning new values", which is basically like saying "go through each value from the query" or "for each query value...."