SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Sep 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dropdown Menu sorted by name

    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:

    Code PHP:
    <?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>";  
     
    ?>
    Last edited by TechnoBear; Sep 22, 2012 at 12:14. Reason: PHP code tags added

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,127
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I would use a JOIN instead of two separate DB queries

    Code:
    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
    PHP Code:
    $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>"

  3. #3
    SitePoint Member
    Join Date
    Sep 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    I would use a JOIN instead of two separate DB queries

    Code:
    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
    PHP Code:
    $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?

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,127
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kplace View Post
    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

  6. #6
    SitePoint Member
    Join Date
    Sep 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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.

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Good call @cpradio ;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •