SELECT * FROM partner p LEFT JOIN pid_to_sid p2s ON p.id=p2s.pid LEFT JOIN service s ON p2s.sid=s.id LEFT JOIN category c ON s.categoryid=c.id WHERE $your_conditions ORDER BY p.name, c.name, s.name
The important part is ORDER BY clause, so when you retrieve the rows, by checking p.id and c.id to its previous ids, you find out you encountered next partner or category, respectively (if p.id has changed, c.id has changed too). LEFT JOINs are used so you get also partners not providing any service. You also use same column names in several tables, so instead of asterix use p.name AS partner_name and so on.
Hope this helps
Justin French wrote:
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
