Fetch only a number of rows from the parent table

Hello i have these two tables invoices and payments, now the problem is when i run this query, i wan’t to fetch only the total number of invoices but to get it i need to loop through payments which is giving many records because the payments matching invoice id 1 are many, help me please just to loop through the invoices instead of payments:

$py = mysqli_query($conn, "SELECT * FROM payments WHERE payment_by='1' AND status!='rejected' ");
  while($rowpy = mysqli_fetch_assoc($py)){
      $invoiceId = mysqli_real_escape_string($conn, $rowpy['invoice_id']);
      $PaymentStatus = mysqli_real_escape_string($conn, $rowpy['status']);
      //Getting invoice from the id
      $Invoice = mysqli_query($conn, "SELECT * FROM invoices WHERE id='$invoiceId'");
      while($rowInv = mysqli_fetch_assoc($Invoice)){
      $NewFeeId = $rowInv['id'];
      $total_price += $rowInv['fee']; 
$query = '
    SELECT SUM(fee) as total
    FROM invoices 
    WHERE id IN 
    (SELECT DISTINCT invoice_id FROM payments WHERE payment_by='1' AND status!='rejected')

$py = mysqli_query($conn, $query);
$total_price = mysqli_fetch_assoc($py)['total'];

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.