showing total population changes and sorting by the last time pop was changed

[Kodabears]

I'm trying to get create a table using the data i have in my database and using php code that will look at pop of each town and add them all up to get the told pop for each day  (I know how to do this part its the next part i am having problems with)  and then takes the told pop and finds when the last time the pop has been changed and outputs that data and sort by the last time there was a pop change. Any help would be great

What it shows right now

+-----------------------+---------------+-----------------------+
| Last_time_pop_changed | Player_name |total_population_change|
+-----------------------+---------------+-----------------------+
| 2016-02-07 00:03:46 | .Mjölnir. | 383846|
| 2016-02-08 00:03:25 | .Mjölnir. | 384309|
| 2016-02-09 00:03:30 | .Mjölnir. | 384347|
| 2016-02-10 00:04:09 | .Mjölnir. | 384374|
| 2016-02-11 00:03:29 | .Mjölnir. | 385163|
| 2016-02-12 00:02:51 | .Mjölnir. | 385173|
| 2016-02-13 00:02:59 | .Mjölnir. | 385190|
| 2016-02-14 00:03:12 | .Mjölnir. | 385904|
| 2016-02-15 00:04:11 | .Mjölnir. | 386217|
| 2016-02-16 00:03:58 | .Mjölnir. | 386254|
| 2016-02-17 00:03:39 | .Mjölnir. | 386295|
| 2016-02-07 00:03:46 | Rebellions | 23084|
| 2016-02-08 00:03:25 | Rebellions | 22382|
| 2016-02-09 00:03:30 | Rebellions | 22382|
| 2016-02-10 00:04:09 | Rebellions | 22382|
| 2016-02-11 00:03:29 | Rebellions | 22382|
| 2016-02-12 00:02:51 | Rebellions | 22382|
| 2016-02-13 00:02:59 | Rebellions | 22382|
| 2016-02-14 00:03:12 | Rebellions | 22382|
| 2016-02-15 00:04:11 | Rebellions | 22382|
| 2016-02-16 00:03:58 | Rebellions | 22382|
| 2016-02-17 00:03:39 | Rebellions | 22382|
+-----------+------------+---------------+-----------------------+

What i would like it to show

+-----------+------------+---------------+-----------------------+
| Last_time_pop_changed | Player_name |total_population_change|
+-----------+------------+---------------+-----------------------+
| 2016-02-08 00:03:25 | Rebellions | -702|
| 2016-02-16 00:03:58 | .Mjölnir. | 41|
+-----------+------------+---------------+-----------------------+

PHP code

$sql="SELECT players.*, town_deltas.*, town_deltas.town_id, sum( town_deltas.population ) as total_population FROM `town_deltas` LEFT JOIN players ON town_deltas.owner_id = players.player_id GROUP BY owner_id, data_timestamp ORDER BY `town_deltas`.`town_id` ASC, `town_deltas`.`data_timestamp` ASC";
$result = mysqli_query($con,$sql);
echo "<table>
<tr>

<th>Last_time_pop_changed</th>

<th>Player_name</th>


<td>total_population_change</td>

</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";


echo "<td>" . $row['data_timestamp'] . "</td>";

echo "<td>" . $row['Player_name'] . "</td>";


echo "<td>" . $row['total_population'] . "</td>";


echo "</tr>";
}

[requinix]

I take it your example desired output is demonstrating the total_population_change value and should show that for all dates (except the first per player) and not just those two?

 

There's a pure SQL solution to this, and I think it would work more nicely than the PHP alternative, so I'll move the thread over there.