Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

php, form select double order

Posted on 2011-09-14
12
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 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 1000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

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

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 111

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 111

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 111

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 i…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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 look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

610 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