doujiu6976
doujiu6976
2018-01-08 08:54
浏览 78
已采纳

如何在php中加入2个表并在for循环中显示数据?

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

enter image description here

enter image description here

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doujin4031
    doujin4031 2018-01-08 09:08
    已采纳
    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
    
    点赞 评论

相关推荐