rearranging an array

[catwebster]

I am selecting a group of people from our mysql database and I want to display their names with the supervisor first and then the rest of the members alphabetically.  I can't figure out how to sort the array so that the supervisor will be first.  Here is the code I am using:

 

$query = "

SELECT last_name, person_id

FROM our_people

WHERE our_people.person_id in (595,585,159,112,140,692)

";

 

$get_person = doQuery($query);

 

while ($row = mysql_fetch_assoc($get_person)){

              echo $row['last_name']  ."<br />";

}

 

I want the results to appear in the order listed in the IN statement (in (595,585,159,112,140,692)

 

Can anyone help me with this?  I will always know the person_id of the supervisor if that helps.

 

 

[gizmola]

SQL does not offer this --- you'll need to render them out using code in your PHP script.  The easiest way would be to create an array that provides the order you desire like this and use that to cross reference a second array that contains the ordered data.

 

Here's an approximation:

 

$order = array(595 => 1, 585 => 2, 159 => 3, 112 => 4, 140 => 5, 692 => 6);
$rows = array();
while ($row = mysql_fetch_assoc($get_person)){
              $roworder = $order[$row['person_id']];
              $rows[$roworder] = $row;
}

// Now rows is in the order of the IN statement.

foreach ($rows as $row) {
  echo "{$row['last_name']} 
"; 
}

[catwebster]

That works great - I added

 

ksort($rows);

 

and it printed them out perfectly.  Thanks for your help.

 

One more thing - can you think of a way where I wouldn't have to list the other people in $order, just the one I wanted to print out first and have the rest print alphabetically?  The reason is that if someone is added to the group, I will have to redo the entire array $order.

 

Many Thanks!

[roopurt18]

Can't you just order by the supervisor in your query?

 

ORDER BY supervisor, user

[gizmola]

Can't you just order by the supervisor in your query?

 

ORDER BY supervisor, user

 

That would be ideal.

[greatstar00]

if u want the order exactly like the order u have

(595,585,159,112,140,692)

595 first, then 585, then 159, then 112, then 140, then 692

if u dont have a long list, u can use union all

select * from our_people where person_id=595 union all select * from our_people where person_id=585  union all .................

[catwebster]

I can't order by supervisor because that isn't a field unfortunately.  But, the union command got me thinking and I found the create temp table code and did this:

 

CREATE TEMPORARY TABLE IF NOT EXISTS alph_people LIKE our_people

INSERT INTO alph_people  SELECT * FROM our_people WHERE person_id = 692

INSERT INTO alph_people  SELECT * FROM our_people WHERE person_id in (595,585,159,112,140) order by last_name asc

 

and it actually works. (692 is the supervisor)

 

Thanks for all the help.

 

[roopurt18]

I don't see any reason to create temporary tables for this.  You're using excessive resources for something that can be easily accomplished with a CASE structure.

<?php

// The ids to search for
$ids = array( 595, 585, 159, 112, 140, 629 );

// We want results to be in the same order as the ids, so
// we will assign incrementing integers to each id in the results
$max = count( $ids );
$cases = array();
for( $i = 0; $i < $max; $i += 1 ) {
    $cases[] = "WHEN `person_id`={$ids[$i]} THEN {$i}";
}
$cases[] = "ELSE {$i}";
$cases = "CASE \n" . implode( "\n", $cases ) . "\nEND CASE";

$sql = "
    SELECT
        `last_name`, `person_id`,
        {$cases} AS `myorder`
    FROM `our_people`
    WHERE `person_id` IN (" . implode( ', ', $ids ) . ")
    ORDER BY `myorder`, `last_name`
    ";
    
echo $sql;
?>

 

Outputs:

    SELECT
        `last_name`, `person_id`,
        CASE 
WHEN `person_id`=595 THEN 0
WHEN `person_id`=585 THEN 1
WHEN `person_id`=159 THEN 2
WHEN `person_id`=112 THEN 3
WHEN `person_id`=140 THEN 4
WHEN `person_id`=629 THEN 5
ELSE 6
END CASE AS `myorder`
    FROM `our_people`
    WHERE `person_id` IN (595, 585, 159, 112, 140, 629)
    ORDER BY `myorder`, `last_name`