movieprodw
asked on
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?
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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
It helps organize it by groups of parents, you can see the image above for a better understanding.
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
http://bugs.mysql.com/bug.php?id=11142
ASKER
Interesting, I am not sure.
It seems to work great except it is not ordering the parents alphabetically
It seems to work great except it is not ordering the parents alphabetically
What kind of results do you get if you just ORDER BY parent, name
ASKER
It sticks all of the parents up top then the sub-categories at the bottom
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.
ASKER
Thanks for your help guys, sorry for the late closing
ASKER
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