homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

selecting a row when a certain field IS NOT NULL

10+ Year Member

Msg#: 3487172 posted 9:35 am on Oct 25, 2007 (gmt 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



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

Msg#: 3487172 posted 1:55 pm on Oct 25, 2007 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved