Jump to content

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


Kodabears

Recommended Posts

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>";
}
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.