// 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:[EMAIL PROTECTED]
----- Original Message -----
From: Justin French <[EMAIL PROTECTED]>
To: php <[EMAIL PROTECTED]>
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
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
