Link to home
Start Free TrialLog in
Avatar of movieprodw
movieprodw

asked on

php, form select double order

Hello,

I have a table with 3 columns id,parent,name I am using the below code to pull the parents and then the subs below. The issue I have is that the parents are not alphabetically ordered.

Can we alphabetically order the parents?

<select name="category">
                    <option value="0" style="color:#666;">Please Select</option>
                    <? $result= mysql_query("SELECT id, parent, name
                    FROM categories
                    ORDER BY COALESCE(NULLIF(parent, 0), id), parent, name")
                        or die(mysql_error());  
                        while($row = mysql_fetch_array( $result )) {
                            if($row['parent'] == '0') {
                                echo '<option disabled="disabled" class="select_category">'.$row['name'].'</option>';
                            } else { ?>
                                <option <? if($category == $row['id']){ echo 'selected="selected"'; } ?> value="<?=$row['id']?>"><?=$row['name']?></option>
                           <?  } } ?>
                    </select>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of movieprodw
movieprodw

ASKER

Hello Ray,

That is what I was thinking, I am able to sort them by ORDER BY name, COALESCE(NULLIF(parent, 0), id), parent

but it breaks the grouping
You can see how it is displayed on the attached photo User generated image
What's the COALESCE(NULLIF part doing for you?  Not a technical explanation, just a plain language description of what it's expected to achieve, thanks.
It helps organize it by groups of parents, you can see the image above for a better understanding.
I am wondering if there is some kind of GROUP clause that might be helpful.  I am not familiar with using COALESCE and NULLIF together.  Not sure if this is in play:
http://bugs.mysql.com/bug.php?id=11142
Interesting, I am not sure.

It seems to work great except it is not ordering the parents alphabetically
What kind of results do you get if you just ORDER BY parent, name
It sticks all of the parents up top then the sub-categories at the bottom
Hmm... This is maybe too far afield but I will suggest it anyway.  Add an "ordering" column to the table.  You can put in some kind of gap numbers like 100, 200, 300 that will facilitate inserting things between 200 and 300.  Since this will only control ordering and not be part of the table's interaction with other tables, you will be able to renumber it if that is ever needed.
Thanks for your help guys, sorry for the late closing