Solved

php, form select double order

Posted on 2011-09-14
12
269 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

803 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