Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

selecting a row when a certain field IS NOT NULL

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

Preferred Member

10+ Year Member

joined:Dec 8, 2004
posts: 500
votes: 0


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

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

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

joined:Aug 10, 2004
votes: 96

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members