Search code examples
phpmysqljoinforeachhtml-table

How to combine two SQL queries into one and display in foreach loop PHP

I am trying to display two specific persons (a couples income) in a table (by foreach loop PHP). Each person should be shown in a column and the income per row.

To connect the couple/persons I have created a table j_b_controller which can join the couples together by a joint_budget_id. The j_b_controller also has a position_order column to control where to put the person1 and person2. Person1 have position_order = 1 and person2 = 2.

When one of the accounts log on I start this session

$_SESSION['budget_id'] = $jointbudget['joint_budget_id'];

An example: the income table look like this

  • person1 have income1, income2, income 3 and income 5.
  • person2 have income1
  • The amount in income1 for each person is not the same.
  • Each income have its own specific row_type_id so income1 has row_type_id = 1.

Income table

id name account_id amount note row_type_id date_added
1 Income1 1 14000.99 1 2022-01-01 00:00:00
2 Income2 1 1900.99 2 2022-01-03 00:00:00
3 Income3 1 3269.99 3 2022-01-06 00:00:00
4 Income1 2 5200.99 1 2022-01-01 00:00:00
5 Income5 1 130.99 5 2022-01-01 00:00:00

The j_b_controller table looks like this

  • Person1 has joint_budget_id = 1 and position_order = 1
  • Person2 has joint_budget_id = 1 and position_order = 2

j_b_controller table

id account_id joint_budget_id accept_invitation position_order date_added
1 1 1 1 2022-02-10 11:45:22
2 2 1 2 2022-02-11 11:45:24

Everything should be displayed in a HTML table (PHP foreach loop)

ORDER BY row_type_id

Person 1 Person 2
Income1 Income1
Income2 Empty
Income3 Empty
Income5 Empty

Hopefully you can see the picture below HTML Table

The two SQL queries that should be joined/unioned is

//Select incomes from person with position_order = 1
    $stmt = $pdo->prepare('SELECT i.id, i.name, i.amount AS p2_amount, i.row_type_id FROM incomes i JOIN j_b_controller jb ON i.account_id = jb.account_id AND jb.joint_budget_id = ? AND jb.position_order = **1** ORDER BY i.id AND i.row_type_id ASC');
    $stmt->execute([$_SESSION['budget_id']]);
    $income_rows_122s = $stmt->fetchAll(PDO::FETCH_ASSOC);

and

//Select incomes from person with position_order = 2
    $stmt = $pdo->prepare('SELECT i.id, i.name, i.amount AS p2_amount, i.row_type_id FROM incomes i JOIN j_b_controller jb ON i.account_id = jb.account_id AND jb.joint_budget_id = ? AND jb.position_order = **2** ORDER BY i.id AND i.row_type_id ASC');
    $stmt->execute([$_SESSION['budget_id']]);
    $income_rows_122s = $stmt->fetchAll(PDO::FETCH_ASSOC);

I have already tried to use UNION the two and I have tried to make a subquery

My subquery is the closed I got so far but with the subquery I can only return 1 row for person2.. and it is repeated as many times as Person1 have incomes.

$stmt = $pdo->prepare('SELECT i.*, i.amount AS p1_amount, (SELECT amount FROM incomes i JOIN j_b_controller jb ON i.account_id = jb.account_id AND jb.joint_budget_id = ? AND jb.position_order = 2 ORDER BY i.id AND i.row_type_id ASC) AS p2_amount FROM incomes i JOIN  j_b_controller jb ON i.account_id = jb.account_id AND jb.joint_budget_id = ? AND jb.position_order = 1 ORDER BY i.id AND i.row_type_id ASC');
    $stmt->execute([$_SESSION['budget_id'], $_SESSION['budget_id']]);
    $income_rows_1s = $stmt->fetchAll(PDO::FETCH_ASSOC);

My foreach looks like this

<tbody>
                <?php if (empty($income_rows_1s)) : ?>
                    <tr>
                        <td colspan="8" style="text-align:center;">There are no objects</td>
                    </tr>
                <?php else : ?>
                    <?php foreach ($income_rows_1s as $income_rows_1) : ?>
                       
                        <?php if (empty($income_rows_1['row_type_id'])) :?>


                        <?php else : ?>
                            <tr role="row" class="item" data-modal=".incomeInfo<?= md5($income_rows_1['row_type_id']) ?>">
                                <td role="gridcell" class="table-edit-link"></td>
                                <td role="gridcell" class="table-view-link"><a href="#"><?= $income_rows_1['name'] ?></a></td>
                                <?php if (empty($income_rows_1['p1_amount']) or ($income_rows_1['p1_amount']) == 0.00) :  ?>
                                    <td role="gridcell"></td>
                                <?php else : ?>
                                <td role="gridcell"><?= number_format($income_rows_1['p1_amount'], $decimals, ',', '.'); ?></td>
                                <?php endif; ?>
                                <?php if (empty($income_rows_1['row_type_id']) or (empty($income_rows_1['p2_amount']) or ($income_rows_1['p2_amount']) == 0.00)) : ?>
                                    <td role="gridcell"></td>
                                 <?php else : ?>
                                <td role="gridcell"><?= number_format($income_rows_1['p2_amount'], $decimals, ',', '.'); ?></td>
                                <?php endif; ?>
                                <td role="gridcell"><?= number_format(($income_rows_1['p1_amount'] + $income_rows_1['p2_amount']), $decimals, ',', '.') ?></td>
                                <td role="gridcell"><?= number_format(($income_rows_1['p1_amount'] + $income_rows_1['p2_amount']) * 12, $decimals, ',', '.') ?></td>
                            </tr>
                        <?php endif; ?>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>

The result looks like this (picture) HTML Table

Person2 should only have income1 shown 1 time and the rest is empty. If person2 had income 6 then the income6 row for person1 would be empty and then person2 had the income6 amount in the table.

Hopefully this make sence. I am NOT a skilled/educated programmer but I like to program a bit in my spare time.

Solution

  • This will give you your desired result set in one query:

    SELECT
    IFNULL(i.row_type_id, 'Total') AS Name,
    SUM(IF(jb.position_order = 1, i.amount, NULL)) AS "Person 1",
    SUM(IF(jb.position_order = 2, i.amount, NULL)) AS "Person 2"
FROM incomes i
INNER JOIN j_b_controller jb ON i.account_id = jb.account_id
WHERE jb.joint_budget_id = 1
GROUP BY i.row_type_id WITH ROLLUP
ORDER BY ISNULL(i.row_type_id), i.row_type_id

    result:

    +---------+----------+----------+
| Name    | Person 1 | Person 2 |
+---------+----------+----------+
| 1       | 14000.99 |  5200.99 |
| 2       |  1900.99 |     NULL |
| 3       |  3269.99 |     NULL |
| 5       |   130.99 |     NULL |
| Total   | 19302.96 |  5200.99 |
+---------+----------+----------+

    In your PHP code, you can check for NULL values when iterating your table.