How can I "use" a mysql cell from the previous row of the current row, and display all rows including the first row, in one SQL statement?

Question

Let's say I have an HTML table with dynamically added content that needs to look like this:

+----+---------------------+---------------------+--------------------------+-----------------------------+
| ID |          A          |          B          |            C             | Elapsed since last checkout |
+----+---------------------+---------------------+--------------------------+-----------------------------+
|  1 | 2018-09-01 00:00:00 | 2018-09-03 00:00:00 | 2 days 0 hours 0 minutes | unknown                     |
|  2 | 2018-09-05 00:00:00 | 2018-09-06 00:00:00 | 1 days 0 hours 0 minutes | 2 days 0 hours              |
|  3 | 2018-09-06 00:00:00 | 2018-09-08 00:00:00 | 2 days 0 hours 0 minutes | 0 days 0 hours              |
+----+---------------------+---------------------+--------------------------+-----------------------------+

Columns A and B are added into the table through a form. Columns C and "Elapsed..." are calculations depending on the values from the previous and current rows.

When I try to output the table data into HTML from Php/MySql, this is what I'm getting:

+----+---------------------+---------------------+--------------------------+-----------------------------+
| ID |          A          |          B          |            C             | Elapsed since last checkout |
+----+---------------------+---------------------+--------------------------+-----------------------------+
|  2 | 2018-09-05 00:00:00 | 2018-09-06 00:00:00 | 1 days 0 hours 0 minutes | 2 days 0 hours              |
|  3 | 2018-09-06 00:00:00 | 2018-09-08 00:00:00 | 2 days 0 hours 0 minutes | 0 days 0 hours              |
+----+---------------------+---------------------+--------------------------+-----------------------------+

I'm using the logic from this question: How to get next/previous record in MySQL? to make up my SQL statement to get a value from within the previous row of the current row, but unfortunately the first row is not being displayed:

"SELECT * FROM (select * from bookings WHERE id < $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";

How can I display all the rows from the database and select a mysql cell from the previous row of the current row in one SQL statement?

This is my full code if you need to skim through it. I am using nested queries because I have no idea how else to do it... (I read about JOIN and LEFT JOIN but according to my understanding that is only if you are using different tables, which is not the case for me.)

$sqlQuery = "SELECT * FROM bookings";
$result = mysqli_query($conn, $sqlQuery);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $id = $row['id'];
        $out = $row['check_out'];
        $in = $row['check_in'];
        $sum = $row['sum'];

        $sqlQueryLastDate = "SELECT * FROM (select * from bookings WHERE id < $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
        $resultLastDate = mysqli_query($conn, $sqlQueryLastDate);
        $resultCheckLastDate = mysqli_num_rows($resultLastDate);
        if ($resultCheckLastDate > 0) {
            while ($rowLastDate = mysqli_fetch_assoc($resultLastDate)) {
                $lastInDate = $rowLastDate['check_in'];
                //echo "previous row's in date:" .$lastInDate;

                $sqlQueryCurrentDate = "SELECT * FROM (select * from bookings WHERE id = $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
                $resultCurrentDate = mysqli_query($conn, $sqlQueryCurrentDate);
                $resultCheckCurrentDate = mysqli_num_rows($resultCurrentDate);
                if ($resultCheckCurrentDate > 0) {
                    while ($rowCurrentDate = mysqli_fetch_assoc($resultCurrentDate)) {
                        $currentOutDate = $rowCurrentDate['check_out'];
                        //echo "current row's out date:" .$currentOutDate;

                        $lastIn = new DateTime($lastInDate);
                        $currentOut = new DateTime($currentOutDate);
                        $intervalLastCurrent = $lastIn->diff($currentOut);
                        $elapsedLastCurrent = $intervalLastCurrent->format('%a days %h hours');
                        echo "
                                <tr>    
                                    <td>".$id."</td>
                                    <td>".$out."</td>
                                    <td>".$in."</td>
                                    <td>".$sum."</td>
                                    <td>".$elapsedLastCurrent."</td> 
                                </tr>
                            ";
                    } /*$sqlQueryCurrentDate*/
                }
            } /*$sqlQueryLastDate*/
        }
    } /*$sqlQuery*/
}

?>

Even if you can just provide me with the necessary terminology to get this to work, I would do my own research. For example, someone suggested I used a LEFT JOIN but after looking into it found it was not ideal for what I want to achieve here.

Things I have tried to implement and couldn't get right:

How can I subtract a previous row in sql?

SQL Previous row from the same column

(And more)

"I read about JOIN and LEFT JOIN but according to my understanding that is only if you are using different tables" - No, you can always do Self-Join (using JOIN) — Madhur Bhaiya, Sep 09 '18 at 10:26
I see...Can the `SELF JOIN` clause apply to what I'm trying to achieve? — J Doe, Sep 09 '18 at 10:35
Ok, thanks for the heads up. I'll come back if it didn't work :) — J Doe, Sep 09 '18 at 10:40

score 0 · Answer 1 · answered Sep 09 '18 at 10:51

The proper way to solve this is with window functions which are available only in MySQL 8

Without window functions you can use something like the following query:

SELECT 
  *,
  (SELECT value FROM bookings WHERE id < b.id ORDER BY id LIMIT 1) AS prev 
FROM 
  bookings b 
ORDER BY id;

How can I "use" a mysql cell from the previous row of the current row, and display all rows including the first row, in one SQL statement?

1 Answers1