I'm using "solr" internet search engine to question a catalog for classifieds that match confirmed criteria. The outcomes would be the ID:amounts from the classifieds, that we then use to locate all matches inside a MySql database with individuals ID:s. The ID:s came back they fit into an assortment.

As you can tell underneath the array is imploded. I Quickly make use of the "IN" to locate 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 may be the category selected through the user, within this situation allows say it's "cars".

My issue is this:
I've the ID:amounts within an order (within the array), but MySql doens't "care" relating to this order. MySql shows the earliest item first regardless of what order the array is within.

Here is a 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 tell the ID:s are corrected within the second query above... But they're still displayed within the same order anyways. Why?

Must I apply certain other approach to finding all MySql matches with ID:s from an assortment?

Ideas?

Thanks

This will get it done:

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.

MySQL will return the information within the order it "wants" (I guess it will be an order from the clustered index, or something like that like this), if you don't specify an order by clause.

If you wish to alter the order by which MySQL returns the outcomes, you will need to add an order by clause.


If that is difficult inside your situation, you will need to re-order the sun and rain in the PHP code -- for example, rather than exhibiting the outcomes from what MySQL returns, you need to iterate within the listing of ids came back by Solr, and display the outcomes beginning after that.

Essentially, you'll first execute the MySQL query to fetch the outcomes :

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'
    )

You'll be able to loop over individuals results, in PHP, storing them within an associative array (pseudo-code) :

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

$hash will retain the data, listed in id.

And, then, you'll display the information, in what Solr came back like a beginning point for that loop (pseudo-code) :

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


With this particular, you'll :

  • display the outcomes within the order came back by Solr
  • not do an additionnal (and possibily pricey) sort around the database-side.