How to display selected values from two database tables in table row-CodePudding

I have an accounting statement and want to fetch the expense and receive payment values, Using an While loop to display each one below each other.

I managed to do it but as separate table rows, one for expenses and the other for receive payment.

The variable $value is the customers name. It loops each table until all the given values are displayed, i want to combine the code so that it displays each expense and payment underneath each other and not separate... As following but im getting duplicate values

<?php   //***************** Expenses  ************
                
                require '../Database/conn.php';
                $query = mysqli_query($conn, "SELECT * FROM expenses WHERE payee LIKE '$value' ");
                                    
                while($fetch = mysqli_fetch_array($query))  {
                    $expTot = substr($fetch['total'], 0, 100);
                ?>
             
        <div style="word-wrap:break-word;">
                
                <tr>
                    <td >
                         <h6 ><?php echo substr($fetch['date'], 0, 100);?></h6>
                    </td>
                    <td >
                         <h6 ><?php ?></h6>
                    </td>
                    <td >
                         <h6 ><?php echo substr($fetch['type'], 0, 100);?></h6>
                    </td>
                    <td >
                         <h6 ><?php echo "R-". substr($fetch['total'], 0, 100);?></h6>
                    </td>
                    <?php } ?>
                    
            </tr>
            </div>

And the same for income.

<?php  //************ recive payment ************

                require '../Database/conn.php';
                $query2 = mysqli_query($conn, "SELECT * FROM `recive_payment` WHERE `customer` LIKE '$value' ");
                while($fetch2 = mysqli_fetch_array($query2)) {
         ?> 
         
            <div style="word-wrap:break-word;">
                
                <tr>
                  <td >
                     <h6 ><?php echo substr($fetch2['date'], 0, 100);?></h6>
                  </td>
                  <td >
                     <h6 ><?php ?></h6>
                  </td>
                  <td >
                     <h6 ><?php echo substr($fetch2['type'], 0, 100);?></h6>
                  </td>
                  <td >
                     <h6 ><?php echo "R". substr($fetch2['total'], 0, 100);?></h6>
                  </td>
                    
                    <?php // search close
                    }
                }
                ?>
                    </tr>
                   </div>

CodePudding user response：

You can make a foreign key in any table and the join both the tables using joins on the foreign key and then you can display them in one table

CodePudding user response：

Ideally you should just create one result in MySQL joining tables, but if you want to go down the "quick and dirty" route then try the below. You might have to make amendments to it if you have different amount of rows on each query, but just so you know the while loop will accept as many arguments as you want. This operation is possible:

while($fetch = mysqli_fetch_array($query) && $fetch2 = mysqli_fetch_array($query2)) {
...
...
...
}

You can also create a new array by iteration and then use the new array to iterate and render the table.