Mysql get key and use it for querying another table

[mogaka]

$query = "SELECT encounter,amount1,amount2,posted1,posted2 FROM payments WHERE pid='$patient_id'";
   $res = mysql_query($query);
$max =25;
$num_rows=mysql_num_rows($res);
$x_count=0;
   while($row=sqlFetchArray($res)){
    $encounter_id=$row['encounter'];
        //$method=$row['method'];   
        $amount1=floatval($row['amount1']);
        $amount2=floatval($row['amount2']);
        $posted1=floatval($row['posted1']);
       $posted2=floatval($row['posted2']);
       $total_amount_paid=floatval($amount1+$amount2+$posted1+$posted2);
      for($x_count=0;$x_count<$num_rows;$x_count++){
       //$encounter_id[$x_count]=$encounter_id;
     $query = "SELECT date,SUM(fee) AS totalcharges FROM billing WHERE pid='$patient_id' AND encounter=$encounter_id";
   $res = mysql_query($query);
     while($row=sqlFetchArray($res)){
       $date_fee=$row['date'];
       $total_charges=floatval($row['totalcharges']);
        $balance=floatval($total_charges-$total_amount_paid);

 $pdf->SetFillColor(254,254,254);
$pdf->Ln();
$pdf->SetX(10);
                $pdf->Cell(50,4,$date_fee,1,0,'L',1);
        $pdf->Cell(40,4,$encounter_id,1,0,'L',1);
        $pdf->Cell(40,4,$total_charges,1,0,'L',1);
                $pdf->Cell(40,4,$total_amount_paid,1,0,'L',1);
                 $pdf->Cell(40,4,$balance,1,0,'L',1);}
}
}

The above code shows my attempt to fetch key called encounter from table called payments. for each encounter, i want to obtain records(many) from another table called billing. i want to sum some values based on an encounter.
the above code returns only one row and i have over 6 rows that i expect to see. where is the problem ? can somebody help me ?

[pixelsoul]

From your description, it sounds like a SQL Join (or a Left Join) would be much more efficient here. Then you would have only one SQL query to deal with. Both of the tables have "pid" so you could do the join on that column.

Just an example

SELECT
p.encounter,
p.amount1,
p.amount2,
p.posted1,
p.posted2,
b.date,
SUM(b.fee) AS totalcharges

FROM
payments p
JOIN
billing b
ON
p.pid = b.pid

WHERE
p.pid = '$patient_id'

[mogaka]

Your code is god but still only lists one row,sums up everything and stops there. note that i wanted to for instance encounter_id 25 in payments table appears only once. but on billing table, it appears 6 times since the person received 6 services. each service has a fee so i want to get the total cost for encounter 25 obtained from payments table so that i can know how much you paid,cost of services(in billing table and computed from cost of each of six services) so as to get balance.