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

Discussion in 'PHP' started by prohor, Jul 18, 2020.

  1. prohor

    prohor

    Joined:
    Feb 7, 2020
    Messages:
    5
    Likes Received:
    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 (Text):

    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>';
                   }

     
     
    prohor, Jul 18, 2020
    #1
    1. Advertisements

  2. prohor

    sunfighter

    Joined:
    Nov 27, 2019
    Messages:
    122
    Likes Received:
    17
    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.
     
    sunfighter, Jul 19, 2020
    #2
    prohor likes this.
    1. Advertisements

  3. prohor

    prohor

    Joined:
    Feb 7, 2020
    Messages:
    5
    Likes Received:
    0
    Thank you, I will try this.
     
    prohor, Jul 20, 2020
    #3
  4. prohor

    sunfighter

    Joined:
    Nov 27, 2019
    Messages:
    122
    Likes Received:
    17
    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.
     
    sunfighter, Jul 21, 2020
    #4
    prohor likes this.
  5. prohor

    prohor

    Joined:
    Feb 7, 2020
    Messages:
    5
    Likes Received:
    0
    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 (Text):

    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>';
         
         
                                                                }      
       
                   ?>      
               



     
     
    prohor, Aug 9, 2020
    #5
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.