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

Beginners - Learning PHP
#1

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

And if this helps, this is what the MySQL table indexes look like:

+--------------------+--------------+------+-----+
|       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   |     |
+--------------------+--------------+------+-----+
#2

What are you trying to do? Because, that just looks horribly complicated without reason to begin with. The nested query isn’t even needed, just a basic select will do.

#3

I agree! Messy code. If you only pull one record (limit 1) there is no need to use a WHILE clause as only one row can exist. And, since the first two queries only ever use just one field from the first record found, it is a horrible mess to get a result with.

#4 
<?php

// Get the highest record with an id less than the requested id:
$result1 = DB::table('my_table')
			  ->where('id', '<', $id)
			  ->orderBY('id', 'desc')
			  ->limit(1)
			  ->fetchOrDie()->toArray();

if( !empty($result1) )
{
	$sum1 = $result1[0]['sum'];

	// Get the requested id:
	// ... id _IS_ a unique, auto-incremented primary index field right???
	$result2 = DB::table('my_table')->find( $id )->toArray();

	if( !empty($result2) )
	{
		$sum2 = $result2[0]['sum'];

		$result3 = DB::table('new_table')->fetchAll()->toArray();

		foreach( $result3 as $row3 )
		{
			$sum3 = $row3['sum'];
			echo $sum1 + $sum2 + $sum3 + "\n";
		}
	}
}

I’d replace all your queries with some kind of query builder class instead.

The bad practice is injecting the $id variable directly into the sql query string.

If I understand your sql correctly, there isn’t anything to JOIN.

Actually… ignore my code above, you could probably get the requested id and the one before it in a single query if you order by id descending and limit to 2 results. The first result will be the requested id and the second will be the one found by your original first query… assuming that I understood your queries correctly and that’s actually what you want.

#5

Here’s a rewrite that sticks to your original code.

<?php

// Get the requested ID and the next lowest ID, 
// order by ID ASC so that the second result is
// the requested ID.

$sql1 = "SELECT * FROM my_table WHERE id <= $id ORDER BY id ASC LIMIT 2";

$result1 = mysqli_query( $conn, $sql1 ) or die( mysqli_error( $conn ) );

// Gather all db records into an array.
$records = array();
while( $result_row = mysqli_fetch_assoc($result1) ) 
{
	$records[] = $result_row;
}

if( count($records) === 2 )
{
	$sum1 = $records[0]['sum'];
	$sum2 = $records[1]['sum'];

	//Loop through every record in this other table 
	//and add their sum to the first two sums.
	$sql3 = "SELECT * FROM new_table"; /*Different table*/
	$result3 = mysqli_query($conn, $sql3) or die( mysqli_error( $conn ) );
	while ($row3 = mysqli_fetch_assoc($result3)) {
		$sum3 = $row3['sum'];
		echo $sum1 + $sum2 + $sum3;
	}
}

I personally prefer working with arrays so that section where I "Gather all db records into an array." is something I would turn into a reuseable function instead of having to repeat that same while syntax for every query.

If your team needs to hire someone good at refactoring ancient garbage I’m available.

1 Like
#6

A horrible mess? Guys, I I do appreciate you stopping by to try and help, but I do believe I posted this in the beginner section as I am brand new to PHP/MySql…And that horrible mess actually took some hard work. I hope you’d show a little more leniency to beginners who actually try… In fact until now I did not realize that you can go without a while clause to echo out a row (I’m a thorough noob, I know :\

#7

Well, that is why we gave you ideas on how to fix it. We asked you what your were attempting to do so we could help you. This process could be done in just one query if set up correctly. But, we do not know what you are trying to solve. There are no comments in your code explaining what each step is for. Therefore we are helping you in the blind. Thinsoldier’s first attempt to help you is using object-orientated code and it is probably better to look at his second one. It is easier to follow.
But, again, let us know what you are trying to do. And, I am sorry if you took my comment badly. It was not meant that way.

#8

Okay, I do realize that my explanation is not too detailed (I was trying to create a simple example for the sake of the question).

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.

In the example I gave, I used variables like $sum1, $sum2 and $sum3 for simplicity, but in actual fact, those are datetimes. I’ll edit the question to make it clearer.

@thinsoldier Thanks a lot for attempting to solve this… I tried it out, but then something crazy happened with the sums. For example, the $sum1 + $sum2+ $sum3 (10 + 20 + 30) turned into a massive numeric that was not supposed to be the answer. I’ll try to edit the question to elaborate. :v:

#9

I mean what I said, however, that is not a detractor to you. We all start somewhere and usually that means you don’t know how to do something better.

So, what is the “sum” value for in the table? Why is it a varchar field, when it appears it should be numeric? That is the reason why thinsoldier’s version didn’t do as expected. It could, but you would need to cast the values to something that math can be done on. However, I still don’t think you are in the right place yet.

Now, there are somethings that get more complicated, but make things easier as well. For instance, rather than tackling the days and hours like you are doing,

SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF(NOW(), f_out)) / 24), ' days ',
MOD(HOUR(TIMEDIFF(NOW(), f_out)), 24), ' hours ') as currentOutDate
FROM table

will give you the days and hours breakdown.

I am still not seeing a need for the multiple queries. But, hopefully you can go into more detail, even if that means you need to do a PM due to privacy concerns.

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

This query for instance. Aside from being bad not using a prepared statement, the nested query isn’t needed and adds a performance hit as well as unnecessary complexity to a nonexistent issue.

select 
    id
    ,f_out
    ,f_in
    ,sum
    ,next_estimate
    ,next_estimate_days
 from 
    my_table 
WHERE 
    id = ? 
ORDER BY id DESC LIMIT 1

First, always name your columns, it is important to always know what you are asking for. Next,
Why the greater that ID?
Why the ORDER BY clause if you just want a single record returned?

I can walk you through things, but I need to know the rational as to why that decision was made as well.

#10

Probably all the answers are being echoed right next to each other with no space or <br> inbetween.

#11

I thought of that, but it wasn’t 102030, it was a completely random number like 2418913. I lost that code, otherwise I could’ve given the exact result. But as astonecipher mentioned, it might just be because of my index fields not being constructed for math.

#12

Yes… You see, I usually don’t like to ask seemingly simple questions when it’s something I should be able to figure out on my own. Normally I would spend hours (even days) on a single problem in order to test my own ability. This time I was completely stumped so I had no choice. So thanks a lot for your time and effort.

^This is a breakthrough! I had to go over each of the sql functions you used to see what they do and try to understand them, and I think this (doing the calculation in SQL instead of PHP) would be a great solution.

The changes I would make though, are to replace the now() functions with the f_in column, because I need to get the time-differences between the last f_in field (eg. 1st July 2018, 00:00:00) and the current f_out field (eg. 30th July 2018, 00:00:00).

Though as I was trying to apply it, I didn’t see how this could be applied to each row… This is where your question comes in:

I’m using the < $id to get the previous row. I need to get the f_in date of the previous row, remember? Because that’s one of the parameters for the TIMEDIFF() function.
And the sub-query (…ORDER BY id DESC LIMIT 1) is to get the last and second last row (at least, according to my researches and viewing other q&a on how to select a last row of a table).

By trying to combine the two (i.e. getting the previous row of a current row) that was how I tried to build that query, and failed.

After looking at your SQL solution however, I think that with a little tweaking it could be the best solution. When I think of applying it, then I think multiple queries, something like

#13

My brain is rather fried due to problem solving solutions for enterprise issues, but I do know a query could still be used for that type of calculation. I just am too spent to offer it at this time.

#14

That is completely understandable. The fact that you suggested an SQL query is enough for me to be on my way. God bless :slight_smile:

Sponsor our Newsletter | Privacy Policy | Terms of Service