To retrieve data from SQL table fields with different IDs into corresponding different variables

The sql database table named ‘data’ has the fields ‘date’, ‘project_id’ and ‘amount’. I am able to retrieve the total amount spent on a single project with the code below:

$sql = "SELECT sum(data.amount) AS wn001_amount FROM data WHERE  project_id='wn001'";

$result = $link->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $wn001 = $row['wn001_amount'];
        echo $wn001;
    }
}

But what I want to do is to retrieve the total amount of all projects and store them in different variables like:

$wn001 = total amount entered on different dates for project wn001  
$wn002 = total amount entered on different dates for project wn002
$wn003 = total amount entered on different dates for project wn003 etc...

How can I code for that? Find arrays and loops a bit confusing. Thanks.

Answer

If you know what the projects are, you can use conditional aggregation:

SELECT SUM(CASE WHEN d.project_id = 'wn001' THEN d.amount END) AS wn001_amount,
       SUM(CASE WHEN d.project_id = 'wn002' THEN d.amount END) AS wn002_amount,
       SUM(CASE WHEN d.project_id = 'wn003' THEN d.amount END) AS wn003_amount       
FROM data d;

You could use GROUP BY, but then you would need to cycle through the result set to assign the appropriate values in your application code.