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.
Advertisement
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