MySql displaying results in same order no matter "array-order"

Question

I am using "solr" search engine to query an index for classifieds that match a given criteria. The results are the ID:numbers of the classifieds, which I then use to find all matches in a MySql database with those ID:s. The ID:s returned are put into an array.

As you can see below the array is imploded. Then I use the "IN" to find all matches.

$solr_id_arr_imploded = implode("', '", $solr_id_arr);
$query = "SELECT mt.*, $sql_tbl.* FROM classified mt LEFT JOIN $sql_tbl ON 
$sql_tbl.classified_id = mt.classified_id WHERE mt.ad_id IN ('$solr_id_arr_imploded')";

$sql_tbl is the category chosen by the user, in this case lets say it is "cars".

My problem is this:
I have the ID:numbers in an order (inside the array), but MySql doens't "care" about this order. MySql displays the oldest item first no matter what order the array is in.

So here is one same query displayed with two different "array-directions":

SELECT mt.*, fordon.* FROM classified mt LEFT JOIN fordon ON fordon.classified_id = mt.classified_id WHERE mt.ad_id IN ('Bmw_520i_Svensksald_784332731', 'Bmw_M3_Svensksald_755599519', 'Bmw_M3_E46_Full-utrustad_338210082')

SELECT mt.*, fordon.* FROM classified mt LEFT JOIN fordon ON fordon.classified_id = mt.classified_id WHERE mt.ad_id IN ('Bmw_M3_E46_Full-utrustad_338210082', 'Bmw_M3_Svensksald_755599519', 'Bmw_520i_Svensksald_784332731')

As you can see the ID:s are reversed in the second query above... But they are still displayed in the same order anyways. Why?

Should I use some other method of finding all MySql matches with ID:s from an array?

Ideas?

Thanks

Answer 1

This should do it:

SELECT mt.*, $sql_tbl.* FROM classified mt 
LEFT JOIN $sql_tbl  
ON $sql_tbl.classified_id = mt.classified_id 
WHERE mt.ad_id IN ('$solr_id_arr_imploded') 
ORDER BY FIELD(mt.ad_id,'$solr_id_arr_imploded')

See Order By Field in Sorting Rows.

Answer 2

MySQL will return the data in the order it "wants" (I suppose it'll be the order of the clustered index, or something like that), if you do not specify an order by clause.

If you want to change the order in which MySQL returns the results, you'll have to add an order by clause.

If that's not possible in your case, you'll have to re-order the elements from the PHP code -- for instance, instead of displaying the results from what MySQL returns, you should iterate over the list of ids returned by Solr, and display the results starting from there.

Basically, you'll first execute the MySQL query to fetch the results :

SELECT mt.*, fordon.* 
FROM classified mt 
    LEFT JOIN fordon ON fordon.classified_id = mt.classified_id WHERE mt.ad_id IN (
        'Bmw_520i_Svensksald_784332731', 'Bmw_M3_Svensksald_755599519', 
        'Bmw_M3_E46_Full-utrustad_338210082'
    )

Then you can loop over those results, in PHP, storing them in an associative array (pseudo-code) :

$hash = array();
foreach ($db_results as $elem) {
    $hash[$elem->ad_id] = $elem;
}

$hash will contain the data, indexed by id.

And, then, you'll display the data, using what Solr returned as a starting point for the loop (pseudo-code) :

foreach ($solr_results as $id_solr) {
    echo $hash[$id_solr]->some_field . '<br />';
}

With this, you will :

• display the results in the order returned by Solr
• not do an additionnal (and possibily costly) sort on the database-side.