PHP get value of row field from inside of while loop

Suppose I have a table like this:

Table swipes

swp_id    swp_by    swp_to    swp_type    swp_date
1           8          1       top        2020-03-24 02:39:12
2           11         1       right      2020-03-18 02:37:58
3           1          8       right      2020-03-31 04:04:40
4           1         11       top        2020-03-31 04:04:40

Based on which I have my setup like this:

<?php
  $stmt = $pdo->prepare("SELECT * FROM swipes WHERE swp_by = :mem AND swp_type != 'left' ORDER BY swp_id DESC");
  $stmt-> bindValue(':mem', $sessionUser);
  $stmt-> execute();

  while($swp = $stmt->fetch()){
      $match = $pdo->prepare("SELECT swp_id, swp_date FROM swipes WHERE swp_by = :by AND swp_to = :to AND swp_type != 'left'");
      $match-> bindValue(':by', $swp['swp_by']);
      $match-> bindValue(':to', $sessionUser);
      $match-> execute();

      while($mat = $match->fetch()){
?>
  <!-- Some HTML Part here to display fetched data as per second while loop -->
<?php } } ?>

As you can see right now I have ORDER BY swp_id DESC in first query which works fine. But when I try removing from 1st query and adding it in second query it doesn’t work. What I actually want to do is to get the time from the latest swipe.

As you can see in table given above for the 1st two rows with swp_id 1 & 2, Users 8 and 11 swiped user 1 first. Later, user 1 swiped users 8 & 11 as you can see in records with swp_id 3 & 4. Now, record 3 & 4 are the latest records. Hence, I want to fetch the swp_date from these records and not from 1 & 2. Right now, it’s returning date from records 1 and 2.

Answer

Suppose you want all the user_id’s and dates from users that swiped to user_id 1, but only if he(she) swiped that user too, and not to the left.

So extending your table a bit:

swp_by  swp_to  swp_type    swp_date
   8      1       top         ...
   8      4       top         ... <not
   11     1       top         ...
   1      8       top         ...
   1      11      top         ...
   20     1       top         ... < not
   1      40      top         ... < not
   1      41      left        ... < not

First part: select all the swipes from user 1 swp_by = 1 that are not left (the same as your first query):

// :mem = 1
SELECT swp_id, swp_by, swp_to, swp_date 
    FROM swipes
    WHERE swp_by = :mem AND swp_type <> "left"

Result 1:

swp_by  swp_to  swp_type    swp_date
   1      8        top         ...
   1     11        top         ...
   1     40        top         ... <not

Second part: find all the swipes “back” to user 1, so where swp_to = 1

// :mem = 1
SELECT swp_id, swp_by, swp_to, swp_date 
    FROM swipes
    WHERE swp_to = :mem AND swp_type <> "left"

Result 2:

swp_by  swp_to  swp_type    swp_date
   8      1       top         ...
   11     1       top         ...
   20     1       top         ... < not

Now you can cross reference the results finding the swp_to numbers from Result 1 (8, 11, 40) that have a swp_by in Result 2 (8, 11, 20). This can be done using a LEFT JOIN from the same table

//this, in combination with WHERE, gives Result 1
SELECT R1.swp_id, R1.swp_to, R1.swp_type , GREATEST(R1.swp_date,R2.swp_date)

    FROM swipes AS R1

//cross reference with Result 2
LEFT JOIN swipes AS R2
    ON ( 
    //look for those records where R1.swipe_to = R2.swipe_by
    R1.swp_to = R2.swp_by 
        AND
    //swipe_to from Result 2 has to be user 1
    R2.swp_to = R1.swp_by
        AND 
    //no left swipes in Result 2
    R2.swp_type <> "left" 
    )

WHERE 
    //look only at the records from user 1...
    R1.swp_by = :mem 
        AND
    //...that are not empty
    R2.swp_by IS NOT NULL
            AND
    //... that not are left
    R1.swp_type <> 'left' 
ORDER BY R1.swp_id DESC

Here’s a new fiddle *EDIT OP wanted latest date, adjusted with GREATEST for date