使用PHP获取排序查询的第一行和第二行中的值之间的差异

Question

Sorry for the rather long title.

I have the current table in my database:

I then use the following PHP code to display the information on my website:

<?php

function time_elapsed_string($datetime, $full = true) {
    $now = new DateTime;
    $ago = new DateTime($datetime);
    $diff = $now->diff($ago);
    $diff->w = floor($diff->d / 7);
    $diff->d -= $diff->w * 7;
    $string = array('y' => 'year','m' => 'month','w' => 'week','d' => 'day','h' => 'hour','i' => 'minute','s' => 'second',);
    foreach ($string as $k => &$v) {if ($diff->$k) {$v = $diff->$k . ' ' . $v . ($diff->$k > 1 ? 's' : '');} else {unset($string[$k]);}}
    if (!$full) $string = array_slice($string, 0, 1);
    return $string ? implode(', ', $string) . ' ago' : 'just now';
}

$servername = "localhost";
$username = "root";
$password = "";

$conn = new mysqli($servername, $username, $password);
mysqli_select_db($conn,"ai-database");
// if ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);}

$sql = "SELECT * FROM `steam` ORDER BY `id` DESC LIMIT 1";
$query = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($query)){
    $cards = $row['cards'];
    $backgrounds = $row['backgrounds'];
    $emoticons = $row['emoticons'];
    $gifts = $row['gifts'];
    $timestamp = $row['timestamp'];
}

echo "
    <div class='category'>
      <h2>Steam Collection</h2>
      <p class='info'>
        Last Updated: <span>" . time_elapsed_string("@" . $timestamp) . "</span>
      </p>
      <div class='item'>
        <div class='title'>Cards</div>
        <div class='stat'>" . number_format($cards) . "</div>
      </div>
      <div class='item'>
        <div class='title'>Backgrounds</div>
        <div class='stat'>" . number_format($backgrounds) . "</div>
      </div>
      <div class='item'>
        <div class='title'>Emoticons</div>
        <div class='stat'>" . number_format($emoticons) . "</div>
      </div>
      <div class='item'>
        <div class='title'>Gifts</div>
        <div class='stat'>" . number_format($gifts) . "</div>
      </div>
    </div>
    ";

?>

After a bit of css, I get something a little like this:

I'm wanting to gather the first and second rows after ordering it by id (Desc), Find the difference between the two values and display it on the website.
I'm hoping the result should look like this:

I've tried using the following, but I feel it looks rather messy and probably bad practice too.

<?php

$sql = "SELECT * FROM `steam` ORDER BY `id` DESC LIMIT 2";
$query = mysqli_query($conn, $sql);
$i = 0;
while($row = mysqli_fetch_assoc($query)){
    if($i==0){
        $cards = $row['cards'];
        $backgrounds = $row['backgrounds'];
        $emoticons = $row['emoticons'];
        $gifts = $row['gifts'];
        $timestamp = $row['timestamp'];
        $i+=1;
    } else {
        $cards2 = $row['cards'];
        $backgrounds2 = $row['backgrounds'];
        $emoticons2 = $row['emoticons'];
        $gifts2 = $row['gifts'];
        $timestamp2 = $row['timestamp'];
    }
}

?>

Is there a cleaner and more efficient way to do this?

</div>

Answer 1

Why not just use MySQL?

e.g.

SELECT
(
    (SELECT cards FROM steam ORDER BY id DESC LIMIT 1) 
    - (SELECT cards FROM steam ORDER BY id DESC LIMIT 1,1) 
) AS cardsDiff

Edit: You can comma separate into a single query. Then no extra variables are required in PHP.

Edit 2: Scratched up the same table / data as shown, see below:

mysql> DESC steam;
+-------------+------------------+------+-----+---------------------+-----------------------------+
| Field       | Type             | Null | Key | Default             | Extra                       |
+-------------+------------------+------+-----+---------------------+-----------------------------+
| id          | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| cards       | int(11)          | YES  |     | NULL                |                             |
| backgrounds | int(11)          | YES  |     | NULL                |                             |
| emoticons   | int(11)          | YES  |     | NULL                |                             |
| gifts       | int(11)          | YES  |     | NULL                |                             |
| timestamp   | timestamp        | NO   |     | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM steam;
+----+-------+-------------+-----------+-------+---------------------+
| id | cards | backgrounds | emoticons | gifts | timestamp           |
+----+-------+-------------+-----------+-------+---------------------+
|  1 |   191 |         419 |       187 |    32 | 2015-04-04 16:40:42 |
|  2 |   192 |         419 |       187 |    41 | 2015-04-04 16:40:42 |
|  3 |   190 |         351 |        20 |    56 | 2015-04-04 16:40:55 |
+----+-------+-------------+-----------+-------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT
    -> (
    ->    (SELECT cards FROM steam ORDER BY id DESC LIMIT 1)
    ->     - (SELECT cards FROM steam ORDER BY id DESC LIMIT 1,1)
    -> ) AS cardsDiff,
    -> (
    ->    (SELECT backgrounds FROM steam ORDER BY id DESC LIMIT 1)
    ->     - (SELECT backgrounds FROM steam ORDER BY id DESC LIMIT 1,1)
    -> ) AS backgroundsDiff,
    -> (
    ->    (SELECT emoticons FROM steam ORDER BY id DESC LIMIT 1)
    ->     - (SELECT emoticons FROM steam ORDER BY id DESC LIMIT 1,1)
    -> ) AS emoticonsDiff,
    -> (
    ->    (SELECT gifts FROM steam ORDER BY id DESC LIMIT 1)
    ->     - (SELECT gifts FROM steam ORDER BY id DESC LIMIT 1,1)
    -> ) AS giftsDiff,
    -> (
    ->    (SELECT timestamp FROM steam ORDER BY id DESC LIMIT 1)
    ->     - (SELECT timestamp FROM steam ORDER BY id DESC LIMIT 1,1)
    -> ) AS timestampDiff;
+-----------+-----------------+---------------+-----------+---------------+
| cardsDiff | backgroundsDiff | emoticonsDiff | giftsDiff | timestampDiff |
+-----------+-----------------+---------------+-----------+---------------+
|        -2 |             -68 |          -167 |        15 |            13 |
+-----------+-----------------+---------------+-----------+---------------+
1 row in set (0.00 sec)