sorting sql result by array order......

[jonperks]

I am developing an application to sort results by geographical location... I have an sql table with postcodes and when the user enters a postcode I get the distance in miles between the entered postcode and the postcodes in the database. These then go into an array. I use the the ID from the database to decide which cell of the array to enter then distance in.

 

For example itemID 12 goes into array[12].

 

I then sort the array ..

 

asort($array);
  foreach($array as $key => $value){
    print $key . " = " . $value. "<br />";
  }

 

The easiest thing to do would be loop through the array and grab details from the database and it would be in distance order. However this is being clipped on to an already existing application. That already has some pretty complex ordering.

 

I was wondering if there is a way to specifiy the order in sql by the array $key order... So that I can pull all the details from the database in one statement that outputs them in the order of the array... Hope that makes sense.

[MadTechie]

ok..whats the problem ?

[jonperks]

Bottom of the post got cut off, i've added the rest now...

[MadTechie]

Yes you can use "ORDER BY" ie

 

SELECT *

FROM `people`

ORDER BY `Age` ASC

 

 

will need more info about what data your working with for a better solution

[jonperks]

The data I want to order on isn't in the database. Its in an array.... I have a cell in the array for each item in the table.

 

I can match each item in the array to the correct record of the database.

[shoz]

You can generate an order_by that would do what I think you're asking but you'll have to explain what you're looking for more clearly.

 

You're saying that using the results of a query done by the existing application you'd like to make another query that retrieves the information using the $keys or the $values and then ORDER based on the keys? Give an example array and an example table that you'd be querying and show how the results will be retrieved and how they should be ordered.

[jonperks]

Thanks for the reply...

 

Array

[3]-->234

[4]-->324

[5]-->4

[6]-->65

[7]-->876

[8]-->222

 

I've ordered that using asort();

 

Array

[5]-->4

[6]-->65

[8]-->222

[3]-->234

[4]-->324

[7]-->876

     

 

Table

 

ID    Name      Description

1      Paris

2      London

3      New York

4      Rome

5      Berlin

6      Madrid

7      Brussels

8      Amsterdam

 

The result would then be....

 

Berlin - 4 miles

Madrid - 65 miles

Amsterdam - 222 miles

New York - 234 miles

Rome - 324 miles

Brussels - 876 miles

 

Hope that makes sense, the array cell is associated with the table record ID

 

[shoz]

Hope that makes sense, the array cell is associated with the table record ID

 

Yes, that makes things very clear.

 

<?php
function orderByArray($a, $colname)
{
    $order_by = 'ORDER BY CASE ';
    $pos = 0;
    foreach ($a as $k => $v)
    {
        $order_by .= "WHEN $colname = $k THEN $pos ";
        ++$pos;
    }
    $order_by .= 'END ';
    return $order_by;

}
$an_array = array(1=>'400', 2=>'700', 5=>'600');
asort($an_array);

$query = 'SELECT * FROM tablename '
        .'WHERE idcol IN ('.implode(',', array_keys($an_array)).') '
        .orderByArray($an_array, 'idcol');
mysql_query($query);
?>

[jonperks]

Thanks a lot, i'll give that a try....

[shoz]

Note that if you're pulling more than a few thousand ids that the ordering will be slow.

 

Btw, you're saying that you can't/don't want to modify the query that pulls the results in the first place? If you don't mind doing that you could post the first query and it could be modified.

 

[fenway]

Also, you can use ORDER BY FIELD()...

[shoz]

Also, you can use ORDER BY FIELD()...

 

Didn't even consider that, good one. It would be the easier of the two to generate.

[fenway]

Also, you can use ORDER BY FIELD()...

 

Didn't even consider that, good one. It would be the easier of the two to generate.

Just remember that you have to put the fields in "backwards", and deal with the boundary cases.

[jonperks]

I've implemented the first suggestion and it works great... How would ORDER BY FIELD help, would it improve speed or anything?

 

Thanks for the help...

[shoz]

How would ORDER BY FIELD help, would it improve speed or anything?

 

It would help because it's easier to generate the query using it. In my opinion It should not improve the performance of the query. In theory, MYSQL should parse it more quickly but it would be interesting to see how the function call compares to the CASE statement.

 

<?php
$query = 'SELECT .... ORDER BY FIELD(idcolname,'.implode(',', array_keys($the_array)).')';
?>