Why in 'due column', same value is showing for every clients?


Joined
Feb 7, 2020
Messages
5
Reaction score
0
I have become able to display due data in due column but the same value is showing for each customer - what is the wrong I am doing? Here is the code, please somebody help!

Code:
while ($row_custact = mysqli_fetch_assoc($query_custact)){
               $currentuser = $row_custact['cust_id'];
               $sql_inccur ="SELECT i.inc_date, t.inctype_type, i.inc_amount, i.inc_text, c.cust_no, c.cust_name, i.inc_receipt
                From customer AS c
                LEFT JOIN incomes AS i ON c.cust_id = i.cust_id
                LEFT JOIN inctype AS t ON i.inctype_id = t.inctype_id
                WHERE c.cust_id = '$currentuser'
                ORDER BY inc_date DESC";

                $query_inccur = mysqli_query($db_link, $sql_inccur);
               checkSQL($db_link, $query_inccur);     
               
               
                   
               while ($row_inccur = mysqli_fetch_assoc($query_inccur)){
                   
               $inc_amount = $row_inccur['inc_amount'];
               $inc_text = $row_inccur['inc_text'];   
               
               
               //Iterate over income types and add matching incomes to $total
                   
                       $total_row = $total_row + $row_inccur['inc_amount'];  
                       $total_paid = $total_paid + $total_row;
               
               // part for total due finding.. and "inc_text" is due column
                       $total_row_due = $total_row_due + $row_inccur['inc_text'];  
                       $total_due = $total_due + $total_row_due;
               
               // this part gathers only total due paied for an account
               if($row_inccur['inctype_type']=='Duepay')
               {
                   $total_duepay = $total_duepay + $row_inccur['inc_amount'];
               }
               $remaining_due = $total_row_due - $total_duepay;   
               //echo $remaining_due;    
                   
               }
               
               
               //echo $currentuser;
                   
               echo '<tr>
                               <td>
                                   <a href="customer.php?cust='.$row_custact['cust_id'].'">'.$row_custact['cust_no'].'</a>
                               </td>
                               <td>'.$row_custact['cust_name'].'</td>
                               <td>'.$row_custact['custsex_name'].'</td>
                               <td>
                                   '.$remaining_due. '  // showing due left, here is the problem
                               </td>
                               
                               <td>'.$row_custact['cust_address'].'</td>
                               <td>'.$row_custact['cust_phone'].'</td>
                               <td>'.date("d.m.Y",$row_custact['cust_since']).'</td>
                           </tr>';
               }
 
Ad

Advertisements

Joined
Nov 27, 2019
Messages
129
Reaction score
19
Querying a database in a loop is not a great idea and you have two. My guess is that the second loop runs through the DB and your computing the due amount for al clients before you output the data and the due amount is the last persons amount that your outputting for everybody.
Solution is to get away from using the second while loop.
 
Joined
Feb 7, 2020
Messages
5
Reaction score
0
Querying a database in a loop is not a great idea and you have two. My guess is that the second loop runs through the DB and your computing the due amount for al clients before you output the data and the due amount is the last persons amount that your outputting for everybody.
Solution is to get away from using the second while loop.
Thank you, I will try this.
 
Joined
Nov 27, 2019
Messages
129
Reaction score
19
I have thought about this and now would like to suggest Moving the HTML ECHOs to the second loop instead of having them in the main loop. Easy to do by just moving the ending curly bracket.
 
Ad

Advertisements

Joined
Feb 7, 2020
Messages
5
Reaction score
0
I have thought about this and now would like to suggest Moving the HTML ECHOs to the second loop instead of having them in the main loop. Easy to do by just moving the ending curly bracket.

Thank you, I was just away for few days. Just received this reply. I will test this. In this time between I have got the result by suing SUM and Group BY , bellow what I have done is. Now can you suggest me - I need to get the names and address which are in another table called "customer" and in both table cust_id is the common thing. Please let me know if JOIN query can be used.
Code:
echo " <th> Client Id </th> <th> Client Dues Left </th> ";
   
     foreach($db_link->query('SELECT cust_id, SUM(inc_text)- (SUM(inc_amount)-  
       SUM(
           case
               when inctype_id =11 then 0
               else inc_amount
           end
       )) Total_due_left  FROM incomes
       
                       
  GROUP BY cust_id') as $row) {
   
                                                                     
     echo '<tr>
   <td> <a href="customer.php?cust='.$row['cust_id'].'">'.$row['cust_id'].'</a>
       </td>
    <td>'.$row['Total_due_left'].'</td>
   
   <td>'. $row['Total_due_left'].  '</td>
  
   </tr>';
     
     
                                                            }       
    
               ?>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top