Solved

php, form select double order

Posted on 2011-09-14
12
272 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 110

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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
LVL 1

Author Comment

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

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 110

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 110

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 110

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
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 …

718 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