[SOLVED] display results by order of matching criteria

[webent]

I am running mysql version 4.1.22-standard-log on Cpanel version 11.23.4-RELEASE

 

First let me show you the code, it'll help explain what I'm trying to accomplish...

unset ($query);
$product_line_result = mysql_query ("SELECT product_line FROM $local_database.product_lines ORDER BY sort_order", $b_link);
    while ($product_line_row = mysql_fetch_assoc($product_line_result)) {
        $query=$query."'$product_line_row[product_line]', ";
    }
if (!empty($query)) {
    $query = substr_replace($query," ",-2);    
$vendor_result = mysql_query ("SELECT vendor_name FROM $local_database.vendor_accts", $b_link);
    while ($vendor_row = mysql_fetch_assoc($vendor_result)) {   
    $pre_query = "SELECT DISTINCT product_line FROM webwired_dropshipdirect.products WHERE product_vendor = '$vendor_row[vendor_name]' AND product_line IN ( $query )";            
        $pre_result = mysql_query($pre_query, $a_link);
        while ($pre_row = mysql_fetch_assoc($pre_result)) {
            $product_line = urlencode($pre_row['product_line']); 
            echo'<li><a href="index.php?product_line=' . $product_line . '">' . $pre_row['product_line'] . '</a></li>';

 

Ok, so basically, on the first part of the code, I'm building a query, using a "sort_order" field to put it in a user defined listing order... So what I'd like to accomplish is that the "pre_query" query, I'd like to make it spit out the results in the same order in which it receives the criteria from the built query...

 

So if the "pre_query" looks something like this,...

product_line IN ( Jewelry, Automotive )

It will list the relevant data with Jewelry first and not Automotive ...

 

I hope all of this makes sense, thanks in advance for any help...

[fenway]

Why so many separate queries?

[webent]

Fenway, the reason there are so many seperate queries is because some of the queries access a centralized database ($a_link) and others access settings on a local database ($b_link) ... The other reason there are so many queries is because the sub-categories get queried only after a parent category is selected... You can see how it works at http://imauser.webzcart.com/ , the categories section on the left...

Here is the entire piece of code...

 

<?  
echo '
<div id="categories">
<div id="categories-header">
Categories
</div>
<div id="product_lines">
<ul>';  

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
unset ($query);
$product_line_result = mysql_query ("SELECT product_line FROM $local_database.product_lines ORDER BY sort_order", $b_link);
    while ($product_line_row = mysql_fetch_assoc($product_line_result)) {
        $query=$query."'$product_line_row[product_line]', ";
    }
if (!empty($query)) {
    $query = substr_replace($query," ",-2);    
$vendor_result = mysql_query ("SELECT vendor_name FROM $local_database.vendor_accts", $b_link);
    while ($vendor_row = mysql_fetch_assoc($vendor_result)) {   
    $pre_query = "SELECT DISTINCT product_line FROM webwired_dropshipdirect.products WHERE product_vendor = '$vendor_row[vendor_name]' AND product_line IN ( $query )";            
        $pre_result = mysql_query($pre_query, $a_link);
        while ($pre_row = mysql_fetch_assoc($pre_result)) {
            $product_line = urlencode($pre_row['product_line']); 
            echo'<li><a href="index.php?product_line=' . $product_line . '">' . $pre_row['product_line'] . '</a></li>';
            if ($categories_liststyle_showsubs == "yes") {  
            if (urldecode($_GET['product_line']) == "$pre_row[product_line]") { 
                echo '<ul class="sub">';
                $result = mysql_query("SELECT DISTINCT product_master_category FROM webwired_dropshipdirect.products WHERE product_vendor = '$vendor_row[vendor_name]' AND product_line = '$pre_row[product_line]'", $a_link);
                while ($row = mysql_fetch_assoc($result)) {
                    if (!empty($row['product_master_category'])) {
                    $product_master_category = urlencode($row['product_master_category']);
                    echo '<li><a href="index.php?product_line=' . $product_line . '&product_master_category=' . $product_master_category . '">' . $row['product_master_category'] . '</a></li>';
                    }       
                    if (urldecode($_GET['product_master_category']) == "$row[product_master_category]") {
                        echo '<ul class="sub_sub">';
                        $sub_result = mysql_query("SELECT DISTINCT product_category FROM webwired_dropshipdirect.products WHERE product_vendor = '$vendor_row[vendor_name]' AND product_line = '$pre_row[product_line]' AND product_master_category = '$row[product_master_category]'", $a_link);
                        while ($sub_row = mysql_fetch_assoc($sub_result)) {
                            if (!empty($sub_row['product_category'])) {
                            $product_category = urlencode($sub_row['product_category']);
                            echo '<li><a href="index.php?product_line=' . $product_line . '&product_master_category=' . $product_master_category . '&product_category=' . $product_category . '">' . $sub_row['product_category'] . '</a></li>';
                            }    
                        }
                        echo '</ul>';
                    }
                }
                echo '</ul>'; 
            }
            }
        }
    }
}                
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
echo '
</ul>
</div>
</div>       
    ';    
?>

[fenway]

If that's the case, you can use the FIELD() expression in the ORDER BY clause... just like the same order (unless there will be results that don't match).

[webent]

I'm uncertain as to what you mean by using the FIELD() expression, but I will research it and see what I can find out, I'll let you know...

[fenway]

As in:

 

ORDER BY FIELD( product_line, 'Jewelry', 'Automotive' )

 

Just be aware that this assigns a value based on the 1-indexed position in the list you provided -- non-matching results will get a value of 0 --- so if you have non-matches, then flip the order:

 

ORDER BY FIELD( product_line, 'Automotive', 'Jewelry' ) DESC, product_line

[webent]

Thank you very much fenway, it worked perfectly... Here's the final query...

 

SELECT DISTINCT product_line FROM webwired_dropshipdirect.products WHERE product_vendor = 'DropShipDirect' AND product_line IN ( 'Jewelry', 'Automotive' ) ORDER BY FIELD( product_line, 'Jewelry', 'Automotive' )