Solved

php, form select double order

Posted on 2011-09-14
12
270 Views
Last Modified: 2012-05-12
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
Comment
Question by:movieprodw
  • 6
  • 5
12 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 36536866
Maybe rearrange the ORDER BY clause to put the important parts first?
0
 
LVL 7

Assisted Solution

by:boon86
boon86 earned 250 total points
ID: 36537145
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
 
LVL 1

Author Comment

by:movieprodw
ID: 36537176
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
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 1

Author Comment

by:movieprodw
ID: 36537193
You can see how it is displayed on the attached photo f
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36537195
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
 
LVL 1

Author Comment

by:movieprodw
ID: 36537322
It helps organize it by groups of parents, you can see the image above for a better understanding.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36537363
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
 
LVL 1

Author Comment

by:movieprodw
ID: 36537433
Interesting, I am not sure.

It seems to work great except it is not ordering the parents alphabetically
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36537454
What kind of results do you get if you just ORDER BY parent, name
0
 
LVL 1

Author Comment

by:movieprodw
ID: 36537579
It sticks all of the parents up top then the sub-categories at the bottom
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36537762
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
 
LVL 1

Author Closing Comment

by:movieprodw
ID: 36989707
Thanks for your help guys, sorry for the late closing
0

Featured Post

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
If your site has a few sections that need to be secure when data is transmitted between the server and local computer, such as a /order/ section for ordering or /customer/ which contains customer data, etc it would of course be recommended to secure…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question