I can't figure it out on how to fetch top 10 records from an array on the basis of the highest score calculation using php. I fetch the records out from the table, perform the calculation and store the result in an array name score and associating with rest of the query string(Array) so I can carry that calculation into JSON. My issue is I only need to carried top 10 records which has the highest score.
$gold=$_GET['gold_input'];
$silver=$_GET['silver_input'];
$bronze=$_GET['bronze_input'];
$gdp_value=$_GET['gdp_checked'];
$link = new mysqli('localhost', 'root', '','coa123cdb');
$myArray = array();
$query = "SELECT * FROM coa123cdb.Country";
$result = mysqli_query($link, $query)
or die("Error: ".mysqli_error($link));
$row_cnt = $result->num_rows;
if ($result = $link->query($query)) {
$tempArray = array();
$score=array();
$counter=0 ;
while($row=mysqli_fetch_assoc($result)){
$tempArray['country_name'] = $row['country_name'];
$tempArray['gdp']=$row['gdp'];
$tempArray['population']=$row['population'];
$tempArray['gold']=$row['gold'];
$tempArray['silver']=$row['silver'];
$tempArray['bronze']=$row['bronze'];
if($gdp_value==0)
{
$score=($bronze*$tempArray['bronze'])+($silver*$tempArray['silver'])+($silver*$tempArray['gold']);
}
else
{
$score=($bronze*$tempArray[6]+$silver*$tempArray[5]+$silver*$tempArray[4])*$tempArray[1]/$tempArray[2]/10000;
}
$tempArray['score']=$score;
$data[]=$tempArray;
$counter++;
}
echo json_encode($data);
}
$result->close();
$link->close();
Please advise what should I need to do in order to pass only top 10 records from an array to JSON object.
I hope I've understood you correctly and I think that the best solution would be sorting your array and take first 10 values.
To sort an array there are lots of functions. Because I'm too lazy to learn them all I prefer usort(), which gives me full control what is being done. You can sort by your 'score' field. Perhaps somebody would find a better function.
To take first 10 elements, you can use simple for loop.
If you do not wish to sort the source array, you can always make a copy of it.
First of all, it's much better idea to create complex MySQL query.
MySQL computing score will be almost free, comparising with cost of returning of all results and computing in php.
I suggest create sql query + "order by score limit 10".
SELECT *,CASE gdp
WHEN 0 THEN bronze*silver
ELSE bronze*silver / 1000
END as score FROM coa123cdb.Country order by score desc limit 0,10
This code is for switching between score compute types
CASE gdp
WHEN 0 THEN bronze*silver
ELSE bronze*silver / 1000
END
You have to sort your array first then.. put limit in fetching the records try something like this
---- your code above it ---
$tempArray['score']=$score;
$data[]=$tempArray;
foreach($data as $value)
{
$score[] = $value['score']
}
array_multisort($score, SORT_DESC,$data);
$i = 0;
$newArray = array();
foreach($data as $value)
{
if($i < 10)
{
$newArray[] = $value;
}
$i++;
}
--- code below----------
$newArray will contain the required array ...
You can read more about array_multisort()
Hope it helps you
Since the ORDER BY...LIMIT method is more efficient, I would go for that.
See comments inside code for a short explanation.
/* Initialize variables */
$gold = $_GET["gold_input"];
$silver = $_GET["silver_input"];
$bronze = $_GET["bronze_input"];
$gdp_value = $_GET["gdp_checked"];
$data = array();
/* Construct the query based on user input.
The query should calculate the 'score' (taking '$gdp_value' into account)
and then return the 10 highest scoring entries.
The returned columns include all columns present in the 'Country' table
plus an extra column named 'score' (which contains the calculated score).*/
$scoreFormula = ($gdp_value == 0)
? "((" . $bronze . " * bronze) + (" . $silver . " * silver) + (" . $gold . " * gold))";
: "(((" . $bronze . " * bronze) + (" . $silver . " * silver) + (" . $gold . " * gold)) * (gdp / (population * 10000)))";
$query = "SELECT Country.*, " . $scoreFormula . " AS score
FROM Country
ORBER BY score DESC
LIMIT 10";
/* Connect to the DB and execute the query */
$link = new mysqli("localhost", "root", "", "coa123cdb");
$result = mysqli_query($link, $query)
or die("Error: " . mysqli_error($link));
/* Put the results into the '$data' array */
$row_cnt = $result->num_rows;
forEach ($row = mysqli_fetch_assoc($result)) {
$data[] = $row;
}
/* Release resources */
$result->close();
$link->close();
/* Return the data */
echo(json_encode($data));
NOTE:
The code is for illustration purposes only. It is always a bad, bad, BAD idea to put unfiltered user-input into an SQL statement. You should use a prepared statement instead.