更改一个复杂的INNER JOIN查询并用PHP显示它 [英] Changing a complex INNER JOIN query and displaying it with PHP

问题描述

我有这个SQL查询，在其中列出了本月付款的人和没有付款的人:

I have this SQL query where I make a list of people who paid this month, and who didn't:

SELECT  main.* 
FROM    (SELECT    t1.client_name, t1.total_debts, t2.client_id, sum(payment) payments , t2.date_now ,
                    CASE MONTH(t2.date_now)  
                       WHEN MONTH(CURDATE()) THEN 'Paid'
                       ELSE 'Not Paid'
                    END current_month
        FROM client_debts t1  INNER JOIN client_details t2  ON t1.id = t2.client_id 
        GROUP BY t2.client_id) main
WHERE   main.total_debts<> main.payments;

所以我得到了这个结果:

So I've got this result:

如图所示，我有同一位客户，本月为一件商品付款，但他没有为另一件商品付款，因此实际上，他来到我的商店给了我钱，所以我需要从列表中将他的名字删除为Not Paid，并在其上显示Paid的行.

As we see in the picture, I have the same client, pays for one item this month, but he didn't pays for the other item, so practically, he came to my store and gave me money, so I need to remove his name from the list as Not Paid and keep the row where it said Paid.

如何在PHP中运行此查询，以便将其放在表中进行显示. 我在此链接中尝试了答案

And how can I run this query in PHP so I can put it inside a table for displaying. I tried the answer here in this link

    $sql = "SELECT  main.* 
FROM    (SELECT    t1.client_name, t1.total_debts, t2.client_id, sum(payment) payments , t2.date_now ,
                    CASE MONTH(t2.date_now)  
                       WHEN MONTH(CURDATE()) THEN 'Paid'
                       ELSE 'Not Paid'
                    END current_month
        FROM client_debts t1  INNER JOIN client_details t2  ON t1.id = t2.client_id 
        GROUP BY t2.client_id) main
WHERE   main.total_debts<> main.payments;";
    $stmt = $conn->prepare($sql);
    $exec = $stmt->execute();
    $res = $stmt->fetchAll();

然后是HTML:

<div class="col-lg-6">
        <table>
        <?php foreach($res as $row){ ?>
        <tr>
        <td><?php echo $row['t1.client_name']; ?></td>
        </tr>
        <?php } ?>
        </table>
        </div>

但是它一直告诉我:

注意:未定义索引:C:\ wamp \ www ...中的ID ...

Notice: Undefined index: id in C:\wamp\www...

推荐答案

可以通过在where子句的末尾添加and条件来处理您的SQL问题.假设所有行都有权付款.如果数据跨越多个月，那么我们需要另一个查询.

Your SQL issue can be handled by adding an and condition in where clause at end. This is assuming that all rows are entitled for that duration of payment. In case the data is spanned across multiple months, then we need another query.

SELECT  main.* 
FROM    (SELECT    t1.client_name, t1.total_debts, t2.client_id, sum(payment) payments , t2.date_now ,
                    CASE MONTH(t2.date_now)  
                       WHEN MONTH(CURDATE()) THEN 'Paid'
                       ELSE 'Not Paid'
                    END current_month
        FROM client_debts t1  INNER JOIN client_details t2  ON t1.id = t2.client_id 
        GROUP BY t2.client_id
        ) main
WHERE   main.total_debts<> main.payments
and current_month='Not Paid'
and client_name not in 
(select client_name from main 
where current_month='Paid'
);

这篇关于更改一个复杂的INNER JOIN查询并用PHP显示它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！