Add values in select query in php

Question

How do i add values in the select query? example in my code below i have 5 gold, 2 silver and 1 bronze. 1 gold is equal to 3 points and 1 silver is 2 points. My question is how do i compute for this in the select query? is it possible?

Here is my php script

$sql = "SELECT team.shortcut,
            SUM(IF(rank = 1, 1, 0)) AS gold, 
            SUM(IF(rank = 2, 1, 0)) AS silver,
            SUM(IF(rank = 3, 1, 0)) AS bronze
        FROM team 
            INNER JOIN academicorg ON team.shortcut = academicorg.shortcut 
        GROUP BY shortcut 
        ORDER BY gold DESC, silver DESC, bronze DESC";                         

$con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name); 

$result = mysqli_query($con,$sql);
$response = array();
while($row=mysqli_fetch_array($result))
{
    array_push($response, array("shortcut"=>$row[0], "gold"=>$row[1], "silver"=>$row[2], "bronze"=>$row[3], "total"=>$row[4]));
}

echo json_encode (array("news_response"=>$response));
mysqli_close($con);
?>

Here is what i get

    {"news_response":[{"shortcut":"aaa","gold":"6","silver":"3","bronze":"5","total":null},

{"shortcut":"bbb","gold":"6","silver":"3","bronze":"4","total":null}

Here is what i want. I got the total of 29 because gold is equal to 3 points and silver is 2 and bronze is 1.

 {"news_response":[{"shortcut":"aaa","gold":"6","silver":"3","bronze":"5","total"29},

{"shortcut":"bbb","gold":"6","silver":"3","bronze":"4","total":28}

I think you'd have to wrap the select within another select. It might be easier just to do the math within your while loop. — aynber
– aynber, Commented Jan 9, 2017 at 13:41

RiggsFolly · Accepted Answer · 2017-01-09 13:53:36Z

1

I would be tempted to try this. Its not tested I dont have a database that fits requirements

SELECT team.shortcut,
    SUM(IF(rank = 1, 1, 0)) AS gold, 
    SUM(IF(rank = 2, 1, 0)) AS silver,
    SUM(IF(rank = 3, 1, 0)) AS bronze,
    SUM(IF(rank = 1, 3, IF(rank = 2, 2, IF(rank = 3, 1, 0)))) as total
FROM team 
    INNER JOIN academicorg ON team.shortcut = academicorg.shortcut 
GROUP BY shortcut 
ORDER BY gold DESC, silver DESC, bronze DESC

answered Jan 9, 2017 at 13:53

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RiggsFolly Over a year ago

@Strawberry That might have worked except OP stored Gold with a Rank of 1 and Bronze with a rank of 3. So I dont think that would work

Strawberry Over a year ago

@RiggsFolly You''ve lost me :-(

RiggsFolly Over a year ago

@Strawberry Maybe if you expand on your comment a bit, maybe I misunderstood

Strawberry Over a year ago

In MySQL (specifically) SUM(IF(rank = 1, 1, 0)) AS gold can be replaced with SUM(rank=1) gold

Collectives™ on Stack Overflow

Add values in select query in php

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related