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

Question

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 class="date">
                         <h6 class="mb-0 text-sm date"><?php echo substr($fetch['date'], 0, 100);?></h6>
                    </td>
                    <td class="date">
                         <h6 class="mb-0 text-sm date"><?php ?></h6>
                    </td>
                    <td class="type">
                         <h6 class="mb-0 text-sm type"><?php echo substr($fetch['type'], 0, 100);?></h6>
                    </td>
                    <td class="total">
                         <h6 class="mb-0 text-sm total"><?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 class="date">
                     <h6 class="mb-0 text-sm customer"><?php echo substr($fetch2['date'], 0, 100);?></h6>
                  </td>
                  <td class="date">
                     <h6 class="mb-0 text-sm date"><?php ?></h6>
                  </td>
                  <td class="total">
                     <h6 class="mb-0 text-sm type"><?php echo substr($fetch2['type'], 0, 100);?></h6>
                  </td>
                  <td class="total">
                     <h6 class="mb-0 text-sm total"><?php echo "R". substr($fetch2['total'], 0, 100);?></h6>
                  </td>
                    
                    <?php // search close
                    }
                }
                ?>
                    </tr>
                   </div>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, May 30 '22 at 20:03

score 0 · Answer 1 · answered May 30 '22 at 13:52

0

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

answered May 30 '22 at 13:52

Hashir Muzaffar

1
1

score 0 · Answer 2 · answered May 30 '22 at 13:57

0

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.

answered May 30 '22 at 13:57

kissumisha

336
2
10

kissumisha - Trying to access array offset on value of type bool, is the error im getting even if changing the query. – Pierre May 30 '22 at 14:22
Yes, that will happen if one of the arrays reached the end, because mysqli_fetch_array() will return FALSE. Which values are linked between the two sets of results? Is there an ID they share? A date? etc. – kissumisha May 30 '22 at 15:22
yes, they share an customer name witch is set to variable $value – Pierre May 31 '22 at 05:17

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

2 Answers2