Foreach loop with two queries inside

Question

I have two queries which is working fine and it would retrieve 3 rows each query. I am trying to display a query result on a table using foreach with two(2) queries inside a foreach loop. I tried putting two result() on foreach but its an error. How can i display a two result() on a single foreach loop? I don't know how can i achieve this.

Query 1 would be on column "Investor Name" then query 2 will be on "Amount".

Here is the code:

<?php

    $query5 = $this->db->query("SELECT * FROM ".tbl_investors." WHERE id IN (SELECT MAX(investor_id) FROM ".tbl_investors_ledger." GROUP BY investor_id ) AND deleted = 0");

    $query6 = $this->db->query("SELECT * FROM ".tbl_investors_ledger." WHERE id IN (SELECT MAX(id) FROM ".tbl_investors_ledger." GROUP BY investor_id ) AND deleted = 0");

?>

                    <table class="table table-striped table-bordered table-hover" id="dataTables">
                        <thead>
                            <tr>  
                                <td>Investor Name</td>
                                <td>Amount</td>
                          </tr>
                        </thead>
                        <tbody style="text-align: center;">

                                <?php
                                foreach ($query5->result() as $row) && ($query6->result() as $row2){
                                ?>
                                <tr>
                                    <td><?php echo $row->last_name.', '.$row->first_name; ?></td>
                                    <td><?php echo $row2->amount; ?></td>
                                </tr>
                                <?php } ?>

                        </tbody>
                    </table>

score 0 · Answer 1 · edited May 23 '17 at 10:29

0

As far as I know, a foreach loop can only handle one query. I see two options out of this, really..

Option 1

Create two different foreach loops, not inside each other since that would make double results, but outside each other. That might not do what you want to, but it could be worth a shot, like so:

<!-- FIRST LOOP -->

<?php 
foreach ($query5->result() as $row) {
?>

  <td> Your data goes here </td>

<?php 
}
?>

<!-- SECOND LOOP -->

<?php 
foreach ($query6->result() as $row) {
?>

  <td> Your data goes here </td>

<?php 
}
?>

Option 2

Create only one query, requiring only one of the loops, by taking use of "JOIN"

See answer at this post

edited May 23 '17 at 10:29

Community

1
1

answered Jun 13 '16 at 07:04

Xariez

759
7
24

when i tried option 2, the rows and column are not arranged, thanks for your suggestion on option 2 – zen Jun 13 '16 at 07:34
1

@zen If you need them ararnged per a column, for example names alphabetically, just add "ORDER BY [columnname] descending" into the end of your query (even although I see you already got a answer, I want to add this here) – Xariez Jun 13 '16 at 08:58

score 0 · Accepted Answer · answered Jun 13 '16 at 07:12

You can't use two array_expressions on foreach loop. It is better to use join on your query to make it one. something like

$query = "SELECT `tbl_investors`.* , `tbl_investors_ledger`.*  
 FROM `tbl_investors` 
 LEFT JOIN  `tbl_investors_ledger` 
 ON `tbl_investors`.id = `tbl_investors_ledger`. investor_id 
 WHERE `tbl_investors`.deleted = 0 AND `tbl_investors_ledger`.deleted = 0 
 GROUP BY `tbl_investors_ledger`.investor_id 
 ORDER BY `tbl_investors_ledger`.id DESC ";

Foreach loop with two queries inside

2 Answers2