Dropdown Menu sorted by name

[kplace]

Can someone help me? I don't know how to get this dropdown list to sort by products_options_values_name

Here is the code below:

<?php 
 
//return the setting options
 
// consists of: Shape 5, Type 1, Size 6, Material 7
//if a value has been selected, it will show as selected.
 
$opts = array( 1 => 'Decibel', 2 => 'Need');
 
// the number before the word represents the option ID in the `product_options` table
// the word after contains the displayed name of the dropdown box, which would usually be the same as the name
 
//title of the box
 
$heading = 'Quick Search';
 
// you should not need to edit below this line 
//------------------------
 
$mainhtml = ""; //the var to hold all of the html
 
foreach ( $opts as $opt => $name) {
 
	unset($html);
 
	$html = "<tr><td width='161px'>
				$name 
			</td></tr>
			<tr><td>
			<SELECT name='$opt' onchange='document.m_srch.submit();'>
			<OPTION value='not'>---</OPTION>";//print the name of the box and start the drop down
 
	$sql = "SELECT `products_options_values_id` from `products_options_values_to_products_options` WHERE `products_options_id`='$opt'"; 
	$res = tep_db_query($sql);// get the values of all the options for that catagory
	while($id = tep_db_fetch_array($res)){
 
		$optnamear = tep_db_query("SELECT `products_options_values_name` from `products_options_values` WHERE `products_options_values_id`='$id[products_options_values_id]'");
 
		$optname = tep_db_fetch_array($optnamear);
 
		//create the dropdown
 
		$html .= "<OPTION value='$id[products_options_values_id]' ";
 
		if($_GET[$opt] == $id['products_options_values_id']){
			$html .= "selected='selected'"; // if the product has already been selected keep it selected!
			} 
 
		$html .= ">$optname[products_options_values_name]</OPTION>";
 
 
		}
 
		$mainhtml .= $html."</SELECT></td></tr>";
 
 
 
	} 
 
 
echo "<tr><td>
<table border='0' width='100%' cellspacing='0' cellpadding='0'>
  <tr>
    <td height='14' class='infoBoxHeading'><img src='images/infobox/corner_left.gif' border='0' alt='' width='11' height='14'></td>
    <td  width='100%' height='14' class='infoBoxHeading' align='center'>$heading &nbsp;</td>
	</tr>
	</table>
	<table border='0' width='100%' cellspacing='0' cellpadding='1' class='infoBox'>
			<tr><td>
			<table class='infoBoxContents'>
			<FORM name='m_srch' action='advanced_search_result.php' method='get'>
			<INPUT type='hidden' value='1' name='m_op'> <INPUT type='hidden' value='1' name='keywords'> \n
			$mainhtml
			</table>
			</td></tr>
			</FORM>
		</table>
	</td></tr>";  
 
?>

[cpradio]

I would use a JOIN instead of two separate DB queries

SELECT o.products_options_values_id, products_options_values_name from `products_options_values_to_products_options` AS o LEFT JOIN products_options_values AS v ON o.products_options_values_id = v.products_options_values_id WHERE o.products_options_id='$opt' ORDER BY products_options_values_name

Then your code will become

$html = "<tr><td width='161px'>
$name 
</td></tr>
<tr><td>
<SELECT name='$opt' onchange='document.m_srch.submit();'>
<OPTION value='not'>---</OPTION>";//print the name of the box and start the drop down

$sql = "SELECT o.products_options_values_id, products_options_values_name from `products_options_values_to_products_options` AS o LEFT JOIN products_options_values AS v ON o.products_options_values_id = v.products_options_values_id WHERE o.products_options_id='$opt' ORDER BY products_options_values_name"; 
$res = tep_db_query($sql);// get the values of all the options for that catagory

while($row = tep_db_fetch_array($res)){

//create the dropdown

$html .= "<OPTION value='$row[products_options_values_id]' ";

if($_GET[$opt] == $row['products_options_values_id']){
$html .= "selected='selected'"; // if the product has already been selected keep it selected!
} 

$html .= ">$row[products_options_values_name]</OPTION>";


}

$mainhtml .= $html."</SELECT></td></tr>"; 

[kplace]

I would use a JOIN instead of two separate DB queries

SELECT o.products_options_values_id, products_options_values_name from `products_options_values_to_products_options` AS o LEFT JOIN products_options_values AS v ON o.products_options_values_id = v.products_options_values_id WHERE o.products_options_id='$opt' ORDER BY products_options_values_name

Then your code will become

$html = "<tr><td width='161px'>
$name 
</td></tr>
<tr><td>
<SELECT name='$opt' onchange='document.m_srch.submit();'>
<OPTION value='not'>---</OPTION>";//print the name of the box and start the drop down

$sql = "SELECT o.products_options_values_id, products_options_values_name from `products_options_values_to_products_options` AS o LEFT JOIN products_options_values AS v ON o.products_options_values_id = v.products_options_values_id WHERE o.products_options_id='$opt' ORDER BY products_options_values_name"; 
$res = tep_db_query($sql);// get the values of all the options for that catagory

while($row = tep_db_fetch_array($res)){

//create the dropdown

$html .= "<OPTION value='$row[products_options_values_id]' ";

if($_GET[$opt] == $row['products_options_values_id']){
$html .= "selected='selected'"; // if the product has already been selected keep it selected!
} 

$html .= ">$row[products_options_values_name]</OPTION>";


}

$mainhtml .= $html."</SELECT></td></tr>"; 

Thank you so much. This worked for the colors but my sizes are not sorting correctly. sizes.png Any ideas on how to fix this?

[Cups]

This worked for the colors but my sizes are not sorting correctly.

Presumably this is the result of another query, is it?

If so and product_size it the column in your db, then just append "ORDER BY product_size" to that query.

Does this do it? You really want to avoid having PHP re-sorting an array you grabbed from Mysql in the first place.

[cpradio]

Thank you so much. This worked for the colors but my sizes are not sorting correctly. Any ideas on how to fix this?

I'm with @Cups ;, I need a bit more information. Is the same query used for the sizes too? As this just became a lot more complicated, but good news, there is an easy fix if you are "willing" to update your database values.

In short, it is sorting your sizes as if they were a string, not a number. The quickest way to resolve this (if using the same query as the one I posted previously) is to put a 0 in front of the single digit numbers, so they are padded as two digits (4 becomes 04, 5 to 05, etc). This will let it sort it as a string correctly.

If it is a separate query, chances are either the column needs updated in the ORDER BY or the column is of VARCHAR or textual and not numeric, in which case you would need to add leading 0's to get it to sort the string properly, or using the MySQL CAST() function to convert the VARCHAR column to INT

[kplace]

I'm with @Cups ;, I need a bit more information. Is the same query used for the sizes too? As this just became a lot more complicated, but good news, there is an easy fix if you are "willing" to update your database values.

In short, it is sorting your sizes as if they were a string, not a number. The quickest way to resolve this (if using the same query as the one I posted previously) is to put a 0 in front of the single digit numbers, so they are padded as two digits (4 becomes 04, 5 to 05, etc). This will let it sort it as a string correctly.

If it is a separate query, chances are either the column needs updated in the ORDER BY or the column is of VARCHAR or textual and not numeric, in which case you would need to add leading 0's to get it to sort the string properly, or using the MySQL CAST() function to convert the VARCHAR column to INT

Thank you. I added the 0 in front of the numbers as you suggested and this works perfect for me. I truly appreciate the help.

[Cups]

Good call @cpradio ;