Forum Moderators: open
I have a table which stores different lenses. Some lenses are variations of other lenses. They are all stored in the same table and I differentiatie between the parent and the child via 2 columns in that table: 'multiple' and 'is_child'.
What I do now is query for all lenses in the database and ignore the childs:
SELECT * FROM lenses
WHERE lid IS NOT NULL AND is_child=NULL
this list is sorted in natural order via PHP scripts based upon the values of 3 different fields in the table
I can not query for the childs, as they will return the lenses as unique ones, whereas I want to display them as subversions/ variations right below its parent on a HTML page generated via the script.
Now I achieve this via a lot of complicated PHP loops and maybe I can optimize this much more by 'simply' adjusting the query.
problems I encounter:
- Fetching all rows and sorting them in natural order via a SQL statement will not ensure that the childs are displayed directly below their respective parents, but I do need the natural sorting.
- the childs also need to be sorted natural below the parent
Basically I need:
1 select and natural sort all lenses in the table until a row is encountered where the value meets a certain requirement (multiple='1').
2 If such row is encountered, search for rows where is_child has the same value as the row which met the requirement and natural sort those in return (but on its own level, so not in order with the primary lenses)!)
3 skip back to the row after where the requirement was met and continue step 1+2 until the end of the table is met
