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>";
}