Re: nested queries/loops

// pseudo code (mostly) if (mysql_query("SELECT * FROM partner, category, service WHERE partner.id = service.id AND service.categoryid = category.id")) { while ($array = mysql_fetcharray($result)) { $partners[$array["id"][]= $array; } } // then do ... foreach($partners as $services) { ... foreach($services as $service) { ... } } this may not be quite what you need but you get the general idea. looks like services as effectively a many to many link table, nothing wrong with that as far as I can see Tim Ward http://www.chessish.com mailto:timward@chessish.com ----- Original Message ----- From: Justin French <justin@indent.com.au> To: php <php-general@lists.php.net> Sent: Thursday, January 23, 2003 11:26 AM Subject: [PHP] nested queries/loops > Hi all, > > I'm aware that this post is borderline MySQL, but the solution may be more > PHP-oriented, so I'm asking here first. > > I have 4 related tables: > > partner (id,name,url,desc) > category (id,name) > service (id,categoryid,name) > pid_to_sid (pid,sid) > > To point out the obvious, a partner provides one or more services, and a > service belongs to a category. > > However, the above table structure may not be the best solution, because I'm > find that I need to perform MANY queries to get the information I need. > > For example, to retrieve all 20 partners, with a list of the services they > perform, broken into the 5 categories, requires heaps of queries: > > while(partners loop of 20+ partners) > { > while(category loop of 5+ categories) > { > list services that match this cat and partner > } > } > > Or, if I want to show tick boxes for all the services (broken by category) > for a particular partner (say on an admin form): > > while(category loop of 5) > { > while(all services loop of 30) > { > query DB to see if service is true for this partner > } > } > > > So, what I'm looking for is some missing snippet of code or information, or > some little theory or tutorial that shows me the light on how I might reduce > the number of queries, perform smarter, more complex queries, or something > like that. > > Otherwise, I can see myself writing some really sluggish code :) > > > Or perhaps my problem is back at the database level, with too many tables, > or whatever. > > > Justin French > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >