Diğer dizi PHP Sıralama ilişkisel-dizi

4 Cevap php

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.

Bu diziler şunlardır:

#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 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.

4 Cevap

Sonuçlarınızı almak için:

$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;
}

Sırasına göre görüntülemek için $sorting_array:

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

Ve alma kodu için bir bonus ipucu $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);

Birkaç basit soru sormak ve nasıl cevaplar birbirine uygun yapmak için kendinizi anlamaya eğer muhtemelen daha iyi / daha fazla yanıt alırsınız gelecekte, burada olacak bir çok şey var çünkü biraz sert söylemek.

Yapabileceğiniz en iyi şey, uzun vadeli Birlikte bu sorguyu birleştirmek SQL tablessuch yeniden yapılandırılması için olacak. Eğer PHP soruyorsun ne yapabilirim, ancak MySQL bunu yaparken daha yavaş ve çok daha karmaşık olacak.

Eğer (PHP oldukça yavaş) ne soruyorsun yapmak için:

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

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;
}

: O sıralama kadar basittir

$array_sorted = array();

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

Eğer sadece 2 foreach döngüleri var çünkü bu çözüm, oldukça etkilidir.

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

Sadece satır:

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!!!

etiketler