* Justin French
> I'm trying to dig a bit deeper into mysql queries... I've come across an
> application that i'm building which requires (it would seem on
> the surface)
> many queries, do to the relational nature of the data.
No. SQL was created to do queries on data of a relational nature, thus the
name RDBMS: Relational Database Management System. Most RDBMS's, including
mysql, use SQL as the main query language. It is of course capable of
querying multiple related tables in a single query.
> The actual app is way too complex to explain, but I've come up with a
> reasonably simple example.
>
> Let's say i've got the cliche hierarchical menu system:
>
> parent 1
> child 1
> grandchild 1
> grandchild 2
> child 2
> grandchild 1
> grandchild 2
> child 3
> child 4
> parent 2
> child 1
> child 2
> child 3
> child 4
> parent 3
> child 1
> child 2
> child 3
> child 4
> grandchild 1
> grandchild 2
>
>
> From a table point of view, lets say I have 3 tables:
>
> parent (id,name)
> child (id, parentID, name)
> grandchild (id, parentID, name)
This is not a good data model for this task. You only need one table:
item (id,parent,name)
This table is related to itself, using 'parent' as a foreign key.
> Now, it's easy to query to get all the parents:
>
> select * from parents
select * from item where parent is NULL;
> And it only takes 2 queries to get all the children of parent 2 (for
> expanding one section of the menu)
>
> select * from parents and
> select * from children where parentID='2'
select * from item where parent is NULL;
select * from item where parent=2;
...or, using one query:
select p.id,p.name,i.id,i.name
from item as p
left join item as i on
i.parent=p.id and
i.parent=2
where p.parent is NULL;
> But when I decide I want to expand all the parents and children
> to show the
> entire menu structure, it's a HEAP of queries. Sure, I don't
> have to WRITE
> them all, because PHP can do that for me in loops, but it occured
> to me that
> I might be missing a HUGE piece of the puzzle in terms of what MySQL can
> do...
You can also expand all items using only one query:
select p.id,p.name,i.id,i.name
from item as p
left join item as i on
i.parent=p.id
where p.parent is NULL;
> can anyone help with a link to somewhere in the manual, or a
> tutorial that i should be reading :)
Read about JOINS and about database normalization:
<URL: http://www.mysql.com/doc/en/JOIN.html >
<URL: http://www.mysql.com/portal/sites/item-146.html >
<URL: http://www.google.com/search?q=database+normalization >
HTH,
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
