CODEWITHSUNDEEP
phpmysqlarrayssorting
dhruveonmars
dhruveonmars

Reputation: 409

How do I order an array using a calculated value, where the rest of the data is received from mysql database?

I have a page called view.htm that displays results according to the what the user assigns to gold medals, silver medals and bronze medals and whether they select to use gdp or population to calculate the score from view.php. I need to find a way to order these results by the calculated score and then limit it to the top 10. My data is received from a mysql database and the score is calculated in the php page. I am using json_encode to encode the data to display it on the page. $results is a two-dimensional array. This is what the results look like when you echo it out from the view.php: 

{"gold":"0","silver":"0","bronze":"1","gdp":"20343461030","population":"34385000",  
"country_name":"Afghanistan","score":"0.029082448742184"},{"gold":"0",  
"silver":"0","bronze":"0","gdp":"12959563902","population":"3205000",  
"country_name":"Albania","score":"0"},{"gold":"1","silver":"0","bronze":"0",  
"gdp":"188681000000","population":"35468000","country_name":"Algeria",  
"score":"0.14097214390436"}

Below is the php code that is used to calculate the score:

$results = array();

while ($row = $res->fetchRow()){
    $resGold = $row['gold'];
    $resSilver = $row['silver'];
    $resBronze = $row['bronze'];
    $resGdp = $row['gdp'];
    $resPopulation = $row['population'];
    $resCountry = $row['country_name'];

    $gold_score = ($resGold * $gold_value);
    $silver_score = ($resSilver * $silver_value);
    $bronze_score = ($resBronze * $bronze_value);

    $total_medals = ($resGold + $resSilver + $resGold);
    $perMillion = $resPopulation/1000000;
    $perBillion = $resGdp/1000000000;

    $score_pop = (($gold_score + $silver_score + $bronze_score)/$perMillion);
    $score_gdp = ($perBillion/($gold_score + $silver_score + $bronze_score + 1));

    if($population == 'true'){ 
        $row['score'] = "$score_pop";
        array_push($results,$row);
    }

    else if($gdp == 'true'){ 
        $row['score'] = "$score_gdp"; 
        array_push($results,$row);
    }
}

Any help is appreciated. Thanks

Upvotes: 0

Views: 211

Answers (2)

jvilhena
jvilhena

Reputation: 1131

I would try to calculate the score on the database side and limit it there. Something like

select gold,silver,bronze,gdp,population,country_name,(gold * $gold_value + silver * $silver_value + bronze * $bronze_value) / (population / 1000000) as score from table sort by score desc limit 0,10

or

select gold,silver,bronze,gdp,population,country_name,(gdp / 1000000000) / (gold * $gold_value + silver * $silver_value + bronze * $bronze_value + 1) as score from table sort by score desc limit 0,10

depending on the population and gdp values. In any case you should be careful with the amount of information the table has, and if the performance starts to degrade, it may be worth it to keep a separate column with the pre-calculated score, that is updated when the rest of the information is updated.

Upvotes: 0

dougBTV
dougBTV

Reputation: 1878

You should be able to sort your results using array_multisort -- but you'll need to make a list-style array of the scores. So where you push your results, also push to a new array.

Abbreviated... essentially initialize the new array, and where you push to $results, also push to $scores

$results = array();
$scores = array();
while ($row = $res->fetchRow()){
   /* snipped */
   array_push($results,$row);
   array_push($scores,$score_pop);
}

array_multisort($scores, SORT_ASC, $results);

And a working example (that I actually tested)

    $result = array(
            array("name" => "guy", "score" => 30),
            array("name" => "dude", "score" => 1),
            array("name" => "bro", "score" => 20),
    );

$scores = array();
    foreach ($result as $r) {
            array_push($scores,$r['score']);
    }

array_multisort($scores,SORT_ASC,$result);

    print_r($result);

(My apologies for tabs/spaces)

Upvotes: 1

Related Questions