0

I've been looking for a few hours for an answer to this, and a lot of what I found either doesn't work or is just off what I need. But here is my situation:

I have an array, as follows:

Array ( 
    '0' => Array ( 
        'city' => Therizo 
        'city_id' => 10722 
        'nation_id' => 1459 
        'nation' => Synkomdi 
        'infra' => 2500.00  
    ) 
    '1' => Array (
        'city' => Anoixi 
        'city_id' => 11822 
        'nation_id' => 1459 
        'nation' => Synkomdi 
        'infra' => 2500.00 
    ) 
    '2' => Array ( 
        'city' => Alitheia 
        'city_id' => 14256 
        'nation_id' => 1459 
        'nation' => Synkomdi 
        'infra' => 2500.00 
    ) 
    '3' => Array ( 
        'city' => Vlastisi 
        'city_id' => 15117 
        'nation_id' => 1459 
        'nation' => Synkomdi 
        'infra' => 2500.00 
    ) 
    '4' => Array ( 
        'city' => Gyri 
        'city_id' => 20507 
        'nation_id' => 1459 
        'nation' => Synkomdi 
        'infra' => 2500.00 
    ) 
    '5' => Array ( 
        'city' => Prasino Pyrgoi 
        'city_id' => 21486 
        'nation_id' => 1459 
        'nation' => Synkomdi 
        'infra' => 2500.00 
    ) ....
)

As you can see, it's a nested array (so an array of arrays essentially). I need to sort the arrays by the "infra" value.

I already tried the sort method, which I read more about on W3 schools here. I then tried the multisort method using the PHP documentation here. Both sort and multisort returned either "true" or 1.

My code for each: sort:

<?php
    [...]
    echo sort($checked);
    # checked is the array I linked above
?>

echo sort() returned 1; The second method I tried, which I was more sure about:

$infra = array_column($checked, 'infra');
echo array_multisort($infra, SORT_DESC, $checked);

Returned "true", I then resorted to the pre-PHP 5 method of using multisort:

foreach ($checked as $key => $row) {
    $infra[$key]  = $row['infra'];
}
$checked = array_multisort($infra, SORT_DESC, $checked);

I have no idea how to proceed since none of these worked and I've never sorted by a specific key-value only, in nested arrays. Is it even possible? And if so, how?

Edit: Someone asked if I had control over how the array is built, the answer is yes, I am making the array from individual database queries.

Edit Part 2: And here is the full code, for better reference.

 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        # Step 1: Get the nation from GET_nation
        $stmt = $conn->prepare("SELECT * FROM `hour_nations_v2` WHERE nation_id=$natid OR nation='$natid'");
        $stmt->execute();
        $nation = $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $nation = $stmt->fetchAll();
        if(empty($nation)) {
            echo json_encode(array("success" => false, "data" => "Nation not found"));
        }
        else {
        $min = $nation[0]['score'] * 0.25;
        $max = $nation[0]['score'] * 1.25;
        # Step 2: Get all nations
            $stmt = $conn->prepare("SELECT * FROM `hour_nations_v2` WHERE alliance_id='$id' OR alliance='$id'");
            $stmt->execute();
            
            $targets = $stmt->fetchAll();
            foreach($targets as $thisTarget) {
                if($thisTarget['score'] < $max && $thisTarget['score'] > $min && $thisTarget['defensive_wars'] < 3) {
                    $stmt = $conn->prepare("SELECT * FROM `hour_cities` WHERE nation_id=" . $thisTarget['nation_id']);
                    $stmt->execute();
                    
                    $cities = $stmt->fetchAll();
                    foreach($cities as $thisCity) {
                        $checked [] = array("city" => $thisCity['city_name'], "city_id" => $thisCity['city_id'], "nation_id" => $thisTarget['nation_id'], "nation" => $thisTarget['nation'], "infra" => $thisCity["infra"]);
                    }
                }
            }
            $infra = array_column($checked, 'infra');
            foreach ($checked as $key => $row) {
                $infra[$key]  = $row['infra'];
            }
            $checked = array_multisort($infra, SORT_DESC, $checked);
            
            echo json_encode(array("success" => true, "data" => $checked));
        }
  • Yes, I am compiling the array out of individual database queries, why? – Lord Vader Mar 16 '21 at 20:01
  • You could adjust your query to sort them right away. Btw, all of the `infra` values are `2500.00` in that array, by which criteria are you gonna sort those? – brombeer Mar 16 '21 at 20:01
  • That's actually just a snapshot of the full array, the full array has `infra` values ranging from 0 to 12000. And because of the way to DB is set up, and the API pulls, it is not possible to sort infra from the array itself. – Lord Vader Mar 16 '21 at 20:04
  • @LordVader could you pls add db query to question, may be we can help you there only – Serving Quarantine period Mar 16 '21 at 20:06
  • Sure, just edited it. – Lord Vader Mar 16 '21 at 20:12
  • https://www.php.net/manual/en/function.usort – Sammitch Mar 16 '21 at 20:13
  • `$stmt = $conn->prepare("SELECT * FROM `hour_cities` WHERE nation_id=" . $thisTarget['nation_id']." order by infra DESC");`.... try this – Serving Quarantine period Mar 16 '21 at 20:16
  • Even doing that only sorts infra for that specific nation, but later nations may have more or less infra, which is not what I need. – Lord Vader Mar 16 '21 at 20:18
  • As a general tip, running SQL in a loop is almost always a bad design; read up on how to use joins in your SQL, and you may find that this whole block of PHP code can be rewritten as a single SQL query. For a small amount of data on a quiet site, you might not notice the difference in performance, but it's a really important skill to practice. – IMSoP Mar 17 '21 at 09:47

0 Answers0