1 reply to this topic

[matrob]

Is it possible to have nested MySQL queries?

I'm dynamically building drop-down navigation menu's (purely HTML and CSS). In my database table, I have these columns:

id - unique identifier
User - to allow for multiple sites to use the same table
DisplayText - the label visible to the user
Url - the link's URL
Parent-id - id of the parent element (NULL if the link is a main-level link, not a sub link)



Php Pseudocode:

Select all rows where user = $User

For each row in the table:

If the row is a main-level element

If the element is a link, add it to the navigation bar

Else...

For each row in the table:

If the row contains a child of the current main-level link, add it as a sub-link

The code works fine until AFTER a main-level element with sub-links is added. After it finishes looping through the code to add the sub-links, it ends the original loop as well, meaning any main-level links stored in the database table AFTER the first element with sub-links will not be added.

Does anyone know why this is? If not, can anyone suggest a better code or database design? Thanks in advance.

[Orjan]

if you have parent-child connection inside the same table, you don't need to nest the queries, otherwise, it works just fine to nest, just use different variables to store the different results so they don't overwrite eachother.

a parent-child version would be:

#, parent, url
1, 0, main url
2, 1, sub to 1
3, 0, another main level
4, 2, a third level
5, 3, sub to 3

etc... this way, you just read your menu and build up an array structure straight from one pure reading of the table...