CODEWITHSUNDEEP
phpmysqlloopspdowhile-loop
user6625547
user6625547

Reputation: 187

Fetch current and next row and compare in a loop

I would like to compare two values from a table:

This is my table:

ID;VALUE
1;700
2;600
3;800
4;900

This is my query:

$stmt = $db->query("SELECT ID, VALUE FROM TABLE);

Now i like to compare the result of the current row with the next row. In mysql it was easy beacause i have to set the row number. I did not find any solution with PDO.

This is my code yet:

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $id = $row["ID"];
      $val_current = $row["VALUE"];
      $row_next = $stmt->fetch(PDO::FETCH_ASSOC);
      $val_next = $row["VALUE"];
      if ($val_current > $val_next){
         echo "$id is greate!";
      }else{
         echo "$id is less!";
      }
}

The Result is:

1 is greater
3 is less

He missed to check the ID 2 agains ID 3 becaue i fetch the next element in the while loop. So i fetch it to get the next value and i fetch again in the loop. Can i reposition the cursor at the end of the loop?

Upvotes: 1

Views: 1543

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157981

You just need to remember the previous row and use it in the next iteration pretending it's the current one, whereas the current row will serve as the next one. 

$row = null;
while($row_next = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($row !== null)) {
        $id = $row["ID"];
        $val_current = $row["VALUE"];
        $val_next = $row_next["VALUE"];
        if ($val_current > $val_next){
            echo "$id is greater!";
        }else{
            echo "$id is less!";
        }
        echo "\n";
    }
    $row = $row_next;
}

Upvotes: 1

GMB
GMB

Reputation: 222652

If you are running MySQL 8.0, this is straight-forward with window function lead():

select 
    t.*,
    (value - lead(value) over(order by id) > 0) is_greater
from mytable t

This gives you a boolean flag called is_greater, with following possible values:

  • 1: this value is greater than the next one
  • 0: this value is smaller than the next one
  • null: there is no next value

Demo on DB Fiddle:

ID | VALUE | is_greater
-: | ----: | ---------:
 1 |   700 |          1
 2 |   600 |          0
 3 |   800 |          0
 4 |   900 |       null

In earlier versions, one option is to use a correlated subquery:

select 
    t.*,
    (value - (
        select t1.value 
        from mytable t1 
        where t1.id > t.id 
        order by t1.id limit 1
    ) > 0) is_greater
from mytable t

Upvotes: 2

Related Questions