PHP中连接invoice_in与invoice_out表并循环展示月度数据的方法

Hey there! Let's work through this problem together. You want to display monthly total sums from both invoice_in and invoice_out tables side by side, right? Your initial join query didn't work because it was trying to link individual rows instead of aggregating monthly data first. Let's fix this step by step.

First, we need to separately calculate monthly totals for each table, then combine those results so we can see both totals per month (even if one table has no data for a specific month). Here's the query:

SELECT
    COALESCE(out_data.year, in_data.year) AS year,
    COALESCE(out_data.month, in_data.month) AS month,
    COALESCE(out_data.total_out, 0) AS total_out,
    COALESCE(in_data.total_in, 0) AS total_in
FROM
    (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_out
     FROM invoice_out
     WHERE trad_id = ? -- Filter by your session trade ID
     GROUP BY YEAR(date), MONTH(date)) AS out_data
LEFT JOIN
    (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_in
     FROM invoice_in
     WHERE trad_id = ? -- Same filter here
     GROUP BY YEAR(date), MONTH(date)) AS in_data
ON out_data.year = in_data.year AND out_data.month = in_data.month
UNION
SELECT
    COALESCE(out_data.year, in_data.year) AS year,
    COALESCE(out_data.month, in_data.month) AS month,
    COALESCE(out_data.total_out, 0) AS total_out,
    COALESCE(in_data.total_in, 0) AS total_in
FROM
    (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_out
     FROM invoice_out
     WHERE trad_id = ?
     GROUP BY YEAR(date), MONTH(date)) AS out_data
RIGHT JOIN
    (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_in
     FROM invoice_in
     WHERE trad_id = ?
     GROUP BY YEAR(date), MONTH(date)) AS in_data
ON out_data.year = in_data.year AND out_data.month = in_data.month
ORDER BY year DESC, month DESC;

Key Notes: #

• We use subqueries to pre-aggregate monthly totals for each table
• COALESCE ensures we get 0 instead of NULL if a month has no data in one table
• UNION combines left and right joins to include all months from both tables
• Parameter placeholders (?) prevent SQL injection (we'll bind the trade ID in PHP)

Let's rewrite your function to use the new query, add proper parameter binding, and return structured data:

public function get_all_vat_monthly_data() {
    $con = $this->__construct(); // Assuming this returns your DB connection
    
    $sql = "
        SELECT
            COALESCE(out_data.year, in_data.year) AS year,
            COALESCE(out_data.month, in_data.month) AS month,
            COALESCE(out_data.total_out, 0) AS total_out,
            COALESCE(in_data.total_in, 0) AS total_in
        FROM
            (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_out
             FROM invoice_out
             WHERE trad_id = ?
             GROUP BY YEAR(date), MONTH(date)) AS out_data
        LEFT JOIN
            (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_in
             FROM invoice_in
             WHERE trad_id = ?
             GROUP BY YEAR(date), MONTH(date)) AS in_data
        ON out_data.year = in_data.year AND out_data.month = in_data.month
        UNION
        SELECT
            COALESCE(out_data.year, in_data.year) AS year,
            COALESCE(out_data.month, in_data.month) AS month,
            COALESCE(out_data.total_out, 0) AS total_out,
            COALESCE(in_data.total_in, 0) AS total_in
        FROM
            (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_out
             FROM invoice_out
             WHERE trad_id = ?
             GROUP BY YEAR(date), MONTH(date)) AS out_data
        RIGHT JOIN
            (SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(total) AS total_in
             FROM invoice_in
             WHERE trad_id = ?
             GROUP BY YEAR(date), MONTH(date)) AS in_data
        ON out_data.year = in_data.year AND out_data.month = in_data.month
        ORDER BY year DESC, month DESC;
    ";

    // Prepare statement for security
    $stmt = mysqli_prepare($con, $sql);
    $tradeId = $_SESSION['trade_id'];
    mysqli_stmt_bind_param($stmt, "ssss", $tradeId, $tradeId, $tradeId, $tradeId);
    
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $data = mysqli_fetch_all($result, MYSQLI_ASSOC); // Return associative array for easier use
    
    mysqli_stmt_close($stmt);
    mysqli_close($con);
    return $data;
}

Now let's adjust your HTML loop to show both monthly totals, format dates nicely, and keep your edit functionality (note: monthly aggregated data doesn't have a single ID, so we'll pass year/month instead):

<tbody>
<?php 
$monthlyData = $obj->get_all_vat_monthly_data();
foreach($monthlyData as $row){ 
    // Convert month number to readable name (e.g., 1 → January)
    $monthName = date("F", mktime(0, 0, 0, $row['month'], 1));
?>
<tr class="gradeA">
    <!-- Display Year-Month -->
    <td><?php echo $row['year'] . ' - ' . $monthName;?></td>
    <td><?php echo 'Monthly';?></td>
    <!-- Total from invoice_out -->
    <td><?php echo number_format($row['total_out'], 2);?></td>
    <!-- Total from invoice_in -->
    <td class="center"><?php echo number_format($row['total_in'], 2);?></td>
    <!-- Optional: Add a difference column if needed -->
    <td class="center"><?php echo number_format($row['total_out'] - $row['total_in'], 2);?></td>
    <?php if($_SESSION['roll'] != 1){?>
    <td class="center bg_ls">
        <a href="add_VAT.php?year=<?php echo $row['year'];?>&month=<?php echo $row['month'];?>" onclick="randomid();" style="color:white;">Edit</a>
    </td>
    <?php }?>
</tr>
<?php }?>
</tbody>

<script type="text/javascript">
function randomid(){
    $.ajax({
        type: "POST",
        url: 'add_paye.php',
        data: ({id:"test123"}), // Adjust this as per your actual needs
        success: function(data) {
            // alert(data);
        }
    });
}
</script>

内容的提问来源于stack exchange，提问作者amit sutar