How to join 2 tables and display data in for loop in php?

Question

I have two tables invoice_in and invoice_out. and in both table i have columns of date, trad_id,net,vat,total respectively. I am sharing my images for both of the tables.

My concern is to display month wise data in table format.And if i want data from two tables so how to write for loop for this. in my current code i am fetching data from only one table. so it comes in correct way.

SELECT SUM(total) FROM invoice_out GROUP BY YEAR(date), MONTH(date). this is my current query to display data from one table only.

Below is my function:

public function get_all_data_for_vat($table)
{                
    $table = self::get_table_name($table);
    $con = $this->__construct();
    $sql="SELECT SUM(total) FROM invoice_out GROUP BY YEAR(date), MONTH(date)";
    $execute = mysqli_query($con, $sql);
    $rows = mysqli_num_rows($execute);
    $data=mysqli_fetch_all($execute);
    return $data;
}

$data1=$obj->get_all_data_for_vat('invoice_out',$_SESSION['trade_id']); 



<tbody>                                                                    
    <?php for($i=0;$i<count($data2);$i++){                         
     ?>
        <tr class="gradeA">
            <td><?php echo $data2[$i][1];?></td>
            <td><?php echo 'Monthly';?></td>
            <td><?php echo $data2[$i][4];?></td>
            <td class="center"><?php echo $data2[$i][5];?></td>
            <td class="center"><?php echo $data2[$i][6];?></td>
            <?php if($_SESSION['roll']!=1){?>
                <td class="center bg_ls"><a href="add_VAT.php?id=<?php echo $data[$i][0];?>" onclick="randomid();" style="color:white;">Edit</a></td>
                <script type="text/javascript">
                    function randomid(){
                        $.ajax({
                            type: "POST",
                            url: 'add_paye.php',
                            data: ({id:"test123<?php //echo $obj->encrypt($data[0]);?>"}),
                            success: function(data) {
                                //alert(data);
                            }
                        });
                    }
                </script>
            <?php }?>   
        </tr>
    <?php }?>
</tbody>

In for loop i am able to show my data from one table.Its working for me. But i want to display total from invoice_in table too. for that i am writing this query that doesn't work.

SELECT io.trad_id , io.date , io.billing_com, 
        io.total, in.trad_id, in.date, in.billing_com, in.total 
FROM invoice_out io
INNER JOIN invoice_in in
    ON invoice_in.trad_id = invoice_out.trad_id

my first image shows data from invoice_in table. my second image shows data from invoice_out table.

Can you show us your actual expected output as text? Please remove the images and replace with text. — Tim Biegeleisen, Jan 08 '18 at 08:57
In your join query, your table alias `in` for invoice_in is a [reserved word](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql). try using a different alias and see if that solves your problem. — kscherrer, Jan 08 '18 at 09:05
You are passing 2 parameters to a method that only has one parameter. How is that not generating at least an error?? — RiggsFolly, Jan 08 '18 at 09:09
`$con = $this->__construct();` ??? Why ??? Obviously a few minutes with the PHP or any OO Manual/Tutorial would be time well spent — RiggsFolly, Jan 08 '18 at 09:10
Possible duplicate of [How to join two tables to get the following result?](https://stackoverflow.com/questions/33817989/how-to-join-two-tables-to-get-the-following-result) — Nimesh Patel, Jan 08 '18 at 09:11
Code appears to be complete nonsense. You load `$data1` with the results of a method call and then loop over `$data2` _How can this code actually do what you claim_ — RiggsFolly, Jan 08 '18 at 09:15

score 1 · Accepted Answer · answered Jan 08 '18 at 09:08

1

SELECT io.trad_id , io.date , io.billing_com,
io.total, invoice.trad_id, invoice.date, invoice.billing_com, invoice.total 
FROM invoice_out io
LEFT JOIN invoice_in invoice
ON invoice_in.trad_id = invoice_out.trad_id

answered Jan 08 '18 at 09:08

Hassaan

319
2
26

2

Can you explain how this solves OPs problem? what did you change and why? – kscherrer Jan 08 '18 at 09:11
Why should the OP ___try this___? **Good answers** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. – RiggsFolly Jan 08 '18 at 09:13
He was using a reserved word as alias, So it was giving him an error – Hassaan Feb 25 '18 at 15:21

How to join 2 tables and display data in for loop in php?

1 Answers1