PHP Sorting associative-array by other array

Question

I need to sort an associative-array in the exact order of the content of another array. The Arrays are retrieve by 2 separate sql-requests (stated below). The requests could not be combined to only one request, so I have to sort the second array into the order of the first one.

These are the arrays:

#Array which contains the id's in needed order
$sorting_array = array(1,2,3,8,5,6,7,9,11,10...);

#Array which contains the values for the id's, but in order of "id" ASC
$array_to_sort = array(
              array("id" => "1", "name" => "text1", "help" => "helptxt2");
              array("id" => "2", "name" => "text2", "help" => "helptxt2");
);

The SQL-Queries:
SQL-Ouery for $sorting_array:
(the db-field 'conf' is setup as "text", maybe this is my problem so that I have to first explode and implode the entries before I could use it for the next query.)

$result = sql_query("select conf from config where user='me'", $dbi);
$conf = sql_fetch_array($result, $dbi);
$temp = explode(',', $conf[0]); 
$new = array($temp[0], $temp[1], $temp[2], $temp[3],$temp[4],
             $temp[5], $temp[6], $temp[7], $temp[8], $temp[9],
             $temp[10], ...);#Array has max 30 entries, so I count them down here
$sorting_array = implode(',', $new);

SQL-Ouery for $array_to_sort:

$result = sql_query("SELECT id, name, helptxt
                   FROM table 
                   WHERE id IN ($sorting_array)
                   AND language='english'"); 
while ($array_to_sort[] = mysql_fetch_array ($result, MYSQL_ASSOC)) {}
array_pop($array_to_sort);#deleting the last null entry

I could access $array_to_sort as follows to see the content one by one:
(if the lines below don't match the array above, than I mixed it up. However, the lines below is what brings the content)

echo $array_to_sort[0]["id"];
echo $array_to_sort[0]["name"];
echo $array_to_sort[0]["helptxt"];

But it is sorted by "id" ASC, but I need exactly the sorting as in $sorting_array. I tried some things with:

while(list(,$array_to_sort) = each($sorting_array)){
$i++;
echo $array_to_sort . "<br>";
}

which only brings the Id's in the correct order, but not the content. Now I'm a bit confused, as I tried so many things, but all ended up in giving me the same results.
Maybe the sql-query could be done in one step, but I didn't brought it to work. All results to my searches just showed how to sort ASC or DESC, but not what I want.

Furthermore I must confess that I'm relative new to PHP and MySQL.
Hopefully some one of you all could bring me back on track.
Many thanks in advance.

Answer 1

Its a little hard to tell because there is a lot going on here, in the future you'll probably get better/more responses if you ask several simple questions and figure out yourself how to make the answers fit together.

Your best bet long term is going to be to restructure your SQL tablessuch that you can combine these query together. You can do what you're asking in PHP, but it's going to be slower than doing it in MySQL and much more complicated.

To do what you're asking (pretty slow in PHP):

$sorted = array();
foreach ( $sorting_array as $id )
{
    foreach ( $array_to_sort as $values )
    {
         if ( $values['id'] == $id )
         {
            $sorted[] = $values;
            break;
         }
    }
}

Answer 2

To fetch your results:

$result = mysql_query("SELECT id, name, helptxt
  FROM table 
  WHERE id IN ($sorting_array)
  AND language='english'");
$array_to_sort = array();
while ( ($row = mysql_fetch_assoc($result)) !== false ) {
  // associate the row array with its id
  $array_to_sort[ $row[ "id" ] ] = $row;
}

To display them in order of $sorting_array:

foreach ( $sorting_array as $id ) {
  // replace the print_r with your display code here
  print_r( $array_to_sort[ $id ] );
}

And a bonus tip for the code fetching $sorting_array:

$result = mysql_query("select conf from config where user='me'", $dbi);
$conf = mysql_fetch_array($result, $dbi);
$temp = explode(',', $conf[0]);
// limit to 30 ids
$new = array();
// no need to do this manually, use a loop
for ( $i = 0; $i < 30; ++$i )
  $new[] = $temp[ 0 ];
$sorting_array = implode(',', $new);

Answer 3

what I tend to do in such a situation is first to rearrange the array with the data. so the keys represent ids
In your case:

$array_to_sort_ids = array();

foreach ($array_to_sor as $item)
{
    $array_to_sort_ids[$item['id']] = $item;
}

Then sorting is as simple as:

$array_sorted = array();

foreach ($sorting_array as $id)
{
    $array_sorted[] = $array_to_sort_ids[$id];
}

This solution is quite efficient, since you only have 2 foreach loops.

Answer 4

EDIT!!!

As I couldn't edit my question anymore, I just like to state my solution this way:

The tip to rethink my database was what brought me to some testings and then I found the solution, with the following query:

$result = sql_query("SELECT id, name, helptxt
               FROM table 
               WHERE id IN ($sorting_array)
               AND language='english'
               ORDER BY FIELD(id,$sorting_array)"); 
while ($array_to_sort[] = mysql_fetch_array ($result, MYSQL_ASSOC)) {}
array_pop($array_to_sort);#deleting the last null entry

Just the line:

ORDER BY FIELD(id,$sorting_array)

will do the trick. It orders the results the way you want, even if this means 1,4,2,3,9,7,...
Sometimes it's so easy, when you know where to look.
Thanks again!!!