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

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

#1

(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*/
}


#2

You would need to explain exactly what the single query should return.


#3

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.


#4

I am still not following. Can you give me the basic structure of the table(s) (i.e. field names, data types) you are using in the query and some sample data. I don’t understand what you mean by mysql cell. Is there a particular field which comes back in the first query’s result which you are trying to reference in the second query?


#5

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…


#6

OK, I recreated your table, but I don’t know what the missing record looked like. Can you copy and paste the record which is not showing, so I can test out a couple of things?

Also, can you try to explain what the ultimate goal for this code is? I mean, what are you wanting to display to the page? All the records, a specific record based on a condition? I am not following what the end goal is for this page. There are many repeated lines of sql, so it is not clear what you want the page to display other than you said it should be showing one extra record.

EDIT: Can you post the 10 records that are actually in the table along with the field names? I tried to recreate your table based on what you posted before, but then I realized, that was what the being calculated/displayed by the php script and was not the actual data.


#7

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.


#8

When does sum get calculated? How is it calculated? Can you show the data for one row in this table (not the displayed data on the page), but the actual data. Sometimes, it is better to make calculations when a query is performed instead of storing calculated data in a table. This is especially true if you are making other calculations on the field containing the calculated data. You have sum and nex_estimate_days as varchar datatypes, which does not seem logical to me if you are making calculations off these fields in some way.

If you can show me the actual data in the table and explain how some fields are calculated before the data is inserted into the table, that would help a lot.

Another thing I am still confused about is if the 10 records for the appointments are in this table and you only want to show these 10 records, then why are you not just using a single select query to get the data you need. For example, just SELECT * FROM myTable and then perform any additional calculations for display purposes in php before the final result is displayed?

I could be wrong, but honestly, you only need the following fields to make all the calculations shown above.

id, f_in, f_out

If you can explain how Next Estimate is calculated, then I believe I can create a single query to display what you want, without having to store sum, next_estimate, or next_estimate_days in the table.