PHP MYSQL how to link data elements from multiple tables in one query

Question

I am doing 3 queries to get various data in my script. It runs fine, but can probably be done more efficiently using one query. I fine with doing basic one table queries, but am not sure how to even start doing what I am doing below. I want to retrieve all the fields from the payments table, and then the requests table where the shortcode is the same as the payment tables shortcode and then user information where the user matches the user in the requests table. I think I need to use joins or something, but am not sure how to structure this. Thanks in advance for any help.

$emls = str_replace(';',',',$clientinfo[0]['PaymentNotificationEmail']);
$stmt = $conn->prepare('SELECT * FROM payments WHERE id = :id');
$stmt->bindValue(':id',$id);
$stmt->execute();
if ($stmt->rowCount() == 1){
 $prec = $stmt->fetchAll(PDO::FETCH_ASSOC);
 $shortcode = $prec[0]['shortcode'];
 if ($shortcode != ''){
  $stmt = $conn->prepare('SELECT * FROM payrequests WHERE id = :id');
  $stmt->bindValue(':id',$shortcode);
  $stmt->execute();
  if ($stmt->rowCount() == 1){
   $rrec = $stmt->fetchAll(PDO::FETCH_ASSOC);
   $uid = $rrec[0]['user'];
   if ($uid != ''){
    $stmt = $conn->prepare('SELECT * FROM clientusers WHERE clientid = :clientid AND id = :id');
    $stmt->bindParam(':id', $uid);
    $stmt->bindParam(':clientid', $clientid);
    $stmt->execute();
    if ($stmt->rowCount() != 1){echo 'User Missing';die;}
    $urec = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($urec[0]['email'] != '' && $urec[0]['bccout'] == 'Y'){
     if ($emls != ''){$emls .= ',';}
     $emls .= $urec[0]['email'];
    }
   }
  }
 }
}

Answer 1

It looks like the query you are looking for is

SELECT *
FROM payments p
LEFT JOIN payrequests pr ON pr.id = p.shortcode
LEFT JOIN clientusers c ON c.id = pr.user AND c.clientid = :clientid
WHERE p.id = :id

Note that this will give you all the columns from each table, which may cause issues if you have similar column names (e.g. id), as the last value read will overwrite the earlier ones. To avoid that problem, you should list all columns that you want to read separately, and use column aliases as necessary to distinguish from them. For example,

SELECT p.id AS payment_id, p.col1, p.col2,
       pr.id AS payrequest_id, pr.col1, pr.col2,
       c.id AS clientuser_id, c.clientid, c.col2
FROM ...

Note also that for payments with no corresponding payrequest or clientuser, the columns from those tables will be NULL.