How can I retain variables between multiple sql queries (without nesting the queries)?

(Edited details to clarify)

For some reason the first record in this table is not being displayed. Refer to the ID numbers, you’ll notice that only records #2 through #10 are displayed, and not #1.

This is what the table should look like:

+----+------------------------+---------------------+---------------------+---------------------------+-------------------------------+-------------------------+
| Id | Days since last period |         Out         |         In          |       Period Lasted       |         Next Estimate         | Notes about this period |
+----+------------------------+---------------------+---------------------+---------------------------+-------------------------------+-------------------------+
|  1 | unknown                | 2018-09-12 00:00:00 | 2018-09-28 00:00:00 | 16 days 0 hours 0 minutes | 2018-10-18 00:00:00 (20 days) |                         |
|  2 | 8 days 0 hours         | 2018-09-12 00:00:00 | 2018-09-28 00:00:00 | 16 days 0 hours 0 minutes | 2018-10-18 00:00:00 (20 days) |                         |
|  3 | 1 days 0 hours         | 2018-09-27 00:00:00 | 2018-09-17 00:00:00 | 10 days 0 hours 0 minutes | 2018-09-18 00:00:00 (1 days)  |                         |
|  4 | 13 days 0 hours        | 2018-09-30 00:00:00 | 2018-09-30 00:00:00 | 0 days 0 hours 0 minutes  | 2018-10-03 00:00:00 (3 days)  |                         |
|  5 | 1 days 0 hours         | 2018-09-29 00:00:00 | 2018-09-29 00:00:00 | 0 days 0 hours 0 minutes  | 2018-10-01 00:00:00 (2 days)  |                         |
|  6 | 12 days 0 hours        | 2018-10-11 00:00:00 | 2018-10-13 00:00:00 | 2 days 0 hours 0 minutes  | 2018-10-19 00:00:00 (6 days)  |                         |
|  7 | 19 days 0 hours        | 2018-11-01 00:00:00 | 2018-11-09 00:00:00 | 8 days 0 hours 0 minutes  | 2018-11-11 00:00:00 (2 days)  |                         |
|  8 | 4 days 0 hours         | 2018-11-13 00:00:00 | 2018-11-19 00:00:00 | 6 days 0 hours 0 minutes  | 2018-11-28 00:00:00 (9 days)  |                         |
|  9 | 11 days 0 hours        | 2018-11-30 00:00:00 | 2018-12-04 00:00:00 | 4 days 0 hours 0 minutes  | 2018-12-27 00:00:00 (23 days) |                         |
| 10 | 4 days 0 hours         | 2018-11-30 00:00:00 | 2018-12-29 00:00:00 | 29 days 0 hours 0 minutes | 2019-01-01 00:00:00 (3 days)  |                         |
+----+------------------------+---------------------+---------------------+---------------------------+-------------------------------+-------------------------+

Code:

$sqlQuery = "SELECT * FROM my_table";
$result = mysqli_query($conn, $sqlQuery);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
	while ($row = mysqli_fetch_assoc($result)) {
		$id = $row['id'];
		$out = $row['f_out'];
		$in = $row['f_in'];
		$sum = $row['sum'];
                
        /*Need help trying to construct this statement:*/
		$sqlQueryLastDate = "SELECT * FROM (select * from my_table WHERE id < $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
        /*I assume the above statement is the only problem*/
		$resultLastDate = mysqli_query($conn, $sqlQueryLastDate);
		$resultCheckLastDate = mysqli_num_rows($resultLastDate);
		if ($resultCheckLastDate >= 0) {
			while ($rowLastDate = mysqli_fetch_assoc($resultLastDate)) {
				$lastInDate = $rowLastDate['f_in'];
                //Nested query
				$sqlQueryCurrentDate = "SELECT * FROM (select * from my_table 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['f_out'];
						
						$lastIn = new DateTime($lastInDate);
						$currentOut = new DateTime($currentOutDate);
						$intervalLastCurrent = $lastIn->diff($currentOut);
						$elapsedLastCurrent = $intervalLastCurrent->format('%a days %h hours');
						/*Why? Php is erasing everything after adding the above variable to the table...Entire first row gets erased.*/
						echo "
								<tr>	
									<td>".$id."</td>
									<td class='test'>".$elapsedLastCurrent."</td> 
									<td class='dateOutResult'>".$out."</td>
									<td class='dateInResult'>".$in."</td>
									<td class='sumHours'>".$sum."</td>
									<td class='nextEstimate'>".$nextEstimate." (".$nextEstimateDays.")</td>
								</tr>";
					} /*$sqlQueryCurrentDate*/
				}
			} /*$sqlQueryLastDate*/
		} 
	} /*$sqlQuery*/
}

Sorry for late response (been offline!)

I’ll try to summarize the question more clearly:
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?

In other words…when I attempt it using the above code, the result is successful in getting all the rows except the first row (because I am depending on a “previous” row inside the queries, like this part WHERE id < $id).

I hope that is a little more understandable.

Alright, I’ve edited the question to include a more practical example of what I’m trying to achieve… I am such a noob in php/mysql that I lack a lot of the terminology required to explain what I actually need. Please bare with me :slight_smile:

Yes, that’s the sole reason I’m calling an entire new nested query. I need to fetch the ID (auto-incremented primary key) to use inside the second query, and I hope that’s the correct way to do it…

Yes, the goal of this page is to list a history of appointments. Similar to what a secretary or doctors assistant would do - fill in the dates of the coming appointments and check the past appointments. So of course, the first record (appointment) shouldn’t be left out.

I edited the question to include what the html table should look like. This is how the SQL table looks:

+--------------------+--------------+------+-----+
|       Column       |     Type     | Null | Key |
+--------------------+--------------+------+-----+
| id                 | int(11)      | NO   | PRI |
| f_out              | datetime     | YES  |     |
| f_in               | datetime     | YES  |     |
| sum                | varchar(256) | YES  |     |
| next_estimate      | datetime     | YES  |     |
| next_estimate_days | varchar(7)   | NO   |     |
+--------------------+--------------+------+-----+

The sum column is a calculation of the difference between the two dates found in f_out and f_in. So you were right in assuming that not all of the fields can be directly edited in SQL.