Kodabears Posted February 17, 2016 Share Posted February 17, 2016 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>"; } Quote Link to comment Share on other sites More sharing options...
requinix Posted February 17, 2016 Share Posted February 17, 2016 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.