How to offset rows from each other during a While Loop in PHP

Question

I am trying to offset the same columns of data to each other so I can calculate the difference in time between them.

The data I am selecting from the DB looks like the following.

22/12/2015 14:01    22/12/2015 14:01
22/12/2015 14:02    22/12/2015 14:02
04/01/2016 07:48    04/01/2016 07:48
04/01/2016 07:48    04/01/2016 07:48
04/01/2016 07:49    04/01/2016 07:49
04/01/2016 07:49    04/01/2016 07:49
04/01/2016 07:49    04/01/2016 07:49
04/01/2016 07:50    04/01/2016 07:50
04/01/2016 08:00    04/01/2016 08:00
04/01/2016 08:18    04/01/2016 08:18

I need it to look like this -

22/12/2015 14:01    22/12/2015 14:02
22/12/2015 14:02    04/01/2016 07:48
04/01/2016 07:48    04/01/2016 07:48
04/01/2016 07:48    04/01/2016 07:49
04/01/2016 07:49    04/01/2016 07:49
04/01/2016 07:49    04/01/2016 07:49
04/01/2016 07:49    04/01/2016 07:50
04/01/2016 07:50    04/01/2016 08:00
04/01/2016 08:00    04/01/2016 08:18
04/01/2016 08:18    04/01/2016 08:20

As you can see, I have simply removed the first row of the second column so that they are now different and then can be calculated to get the difference.

I could easily get around this in my while loop if you could actually access it with Integer keys like so -

    $results = CDB::ExecuteQuery($SQL);

            $firstMovementCounter = 0; // Start counter as normal
            $secondMovementCounter = 1; // Start offset to first movement to calculate difference between movements.
            while ($rows = CDB::GetAssoc($results))                    
            {
                $differenceInSeconds = $rows["FIRST_MOVEMENT"][$firstMovementCounter] - $rows["SECOND_MOVEMENT"][$secondMovementCounter];  

$firstMovementCounter++;
$secondMovementCounter++;

            }

But this doesn't seem to work. Is there another way of succeeding with this method ?

To delve deeper, I put it into debug and seen that on the first loop of the results the $rows variable was = to the following -

array(2) (
  [FIRST_MOVEMENT] => (string) 2015-12-22 14:01:15
  [SECOND_MOVEMENT] => (string) 2015-12-22 14:01:15
)

And that $rows["FIRST_MOVEMENT"][$firstMovementCounter] was = to 1

Which makes no sense

Another way would be to be able to ignore the first row of the right hand column, without skipping the first row in the first column. But I can't see any easy way around this.

Could any one shed some light on what I am trying to do?

Answer 1

Have you considered doing this in the database? In MySQL for example you could query the same table twice and pull out the date field twice.

SELECT
    A.requestid,
    A.dateime AS starttime,
    B.dateime AS endtime
FROM
    MyTable A
INNER JOIN
    MyTable B ON B.requestid = (A.requestid + 1)
ORDER BY
    A.requestid ASC

This could give you the result set you are looking for with no need to process the result in PHP

+-----------+------------------+------------------+
| requestid | starttime        | endtime          |
+-----------+------------------+------------------+
| 1         | 22/12/2015 14:01 | 22/12/2015 14:02 |
| 2         | 22/12/2015 14:02 | 04/01/2016 07:48 |
| 3         | 04/01/2016 07:48 | 04/01/2016 07:48 |
| 4         | 04/01/2016 07:48 | 04/01/2016 07:49 |
| 5         | 04/01/2016 07:49 | 04/01/2016 07:49 |
| 6         | 04/01/2016 07:49 | 04/01/2016 07:49 |
| 7         | 04/01/2016 07:49 | 04/01/2016 07:50 |
| 8         | 04/01/2016 07:50 | 04/01/2016 08:00 |
| 9         | 04/01/2016 08:00 | 04/01/2016 08:18 |
| 10        | 04/01/2016 08:18 | 04/01/2016 08:20 |
+-----------+------------------+------------------+

Answer 2

I think you should try below code:

$results = CDB::ExecuteQuery($SQL);

$firstMovementCounter = 0;
$secondMovementCounter = 1;
while ($rows = CDB::GetAssoc($results))                    
{
    $allrows[] = $rows;
}

for($i = 0; $i < count($allrows); $i++) {
    $differenceInSeconds = $allrows[$firstMovementCounter]['FIRST_MOVEMENT'] - $allrows[$secondMovementCounter]['SECOND_MOVEMENT'];
    $firstMovementCounter++;
    $secondMovementCounter++;
}

Here for the last array key you have to add condition.