Welcome to WebmasterWorld Guest from 50.17.79.100

Forum Moderators: open

Message Too Old, No Replies

selecting a row when a certain field IS NOT NULL

     

dmmh

9:35 am on Oct 25, 2007 (gmt 0)

10+ Year Member



hello,

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

phranque

1:55 pm on Oct 25, 2007 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



i don't believe this can be done in the query - it must be done programmatically...
 

Featured Threads

Hot Threads This Week

Hot Threads This Month