Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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

0
movieprodw
Asked:
movieprodw
  • 6
  • 5
2 Solutions
 
Ray PaseurCommented:
Maybe rearrange the ORDER BY clause to put the important parts first?
0
 
boon86Commented:
try change:

 $result= mysql_query("SELECT id, parent, name
                    FROM categories
                    ORDER BY COALESCE(NULLIF(parent, 0), id), parent, name")
                        or die(mysql_error());  

Open in new window

to:
 $result= mysql_query("SELECT id, parent, name
                    FROM categories
                    ORDER BY parent ASC")
                        or die(mysql_error());  

Open in new window


hope that help
0
 
movieprodwAuthor Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
movieprodwAuthor Commented:
You can see how it is displayed on the attached photo f
0
 
Ray PaseurCommented:
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.
0
 
movieprodwAuthor Commented:
It helps organize it by groups of parents, you can see the image above for a better understanding.
0
 
Ray PaseurCommented:
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
0
 
movieprodwAuthor Commented:
Interesting, I am not sure.

It seems to work great except it is not ordering the parents alphabetically
0
 
Ray PaseurCommented:
What kind of results do you get if you just ORDER BY parent, name
0
 
movieprodwAuthor Commented:
It sticks all of the parents up top then the sub-categories at the bottom
0
 
Ray PaseurCommented:
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.
0
 
movieprodwAuthor Commented:
Thanks for your help guys, sorry for the late closing
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now