PHP & MySQL: While循环

Question

首先，我对PHP和MySQL非常陌生，所以我仍然按照惯例编写代码。

我正在开发一个应用程序，该应用程序接受交易，并在某个到期日期向先前捐款的用户支付到期金额。我有一个knapSolveFast2，它解决了背包问题(数据库中的一组事务数量加在一起，对于一个到期的用户来说，就等于到期了)。目前，我的演示数据库如下所示：

​

​

如果我的当前date (now) = 2017-04-03 11:36:03 = CAST(NOW() AS DATETIME)，我的应用程序将循环遍历数据库，从tran_date (即WHERE maturity_date <= CAST(NOW() AS DATETIME) )中获取maturity_date为>= 1 month的用户。将找到的每个用户放在一起，并在一个each循环中对它们进行配对，使其与数据库中的其他用户( tran_amt )一起使用背包函数knapSolveFast2将tran_amt之和总结到用户发现due_amount的成熟度。

问题：在使用第一个while loop找到到期支付日期(2个用户)的用户之后，我尝试运行一个内部while loop循环，将每个用户与其他用户配对，其他用户的tran_amt与获取的用户的应付金额之和。这里的问题是，内部while循环只为第一个用户运行，而不是为第二个用户运行。

代码

<?php
            $servername = "localhost";
            $username = "root";
            $password = "";
            $dbname = "test";

            $connect = @mysqli_connect($servername, $username, $password, $dbname);

            if (mysqli_connect_errno()) {

              die("<pre><h1>Sorry, we are experiencing a little Downtime!</h1></pre>");

            } 

            //include the match controller containing the knapSolveFast2 function
            include('controller/match.php');

            //UPDATE `pendingpair`SET `maturity_date`= DATE_ADD(`tran_date`, INTERVAL 1 MONTH)

            //select user to be paid
            $sql = "SELECT `user_id`, `due_payment` FROM `pendingpair` where `maturity_date` <= CAST(NOW() AS DATETIME) ORDER BY `id` ASC";

            $queryRun = mysqli_query($connect, $sql);
            $num_rows = mysqli_num_rows($queryRun);

            if ($num_rows > 0) {

                while ($row = mysqli_fetch_assoc($queryRun)) {

                    $user_id_due = $row['user_id'];
                    $user_amt_due = $row['due_payment'];

                    print_r($row);

                    /* Perform queries to select users to pay $user_id_due the sum of $user_amt_due; Where:
                    - user to be paid, $user_id_due, is not included in the pairing logic
                    - transacton payment to be chosen, ph_conf = 1,  has been confirmed
                    - transaction has not yet been paired for payment, tran_paired_status = 0
                    - transactions have not been flaged for fake POP (proof of Payment),  `ph_denied_fpop`= 0

                    */

                    $fetchQuery = "SELECT `tran_inv`, `tran_amt`, `user_id` FROM `pendingpair`WHERE `tran_amt` <= {$user_amt_due} && `user_id` != {$user_id_due} && `ph_conf`=1 && `tran_paired_status` = 0 && `ph_denied_fpop`=0 ORDER BY `id`";

                    $m = array(); // Match Memo items array
                    $picked_trans = array();
                    $numcalls = 0; // number of calls made to get Match        
                    $tran_inv = array();
                    $tran_amt = array();
                    $user_id = array();

                    //run query and throw users that fit the criteria into an array
                    if ($queryRun = mysqli_query($connect, $fetchQuery)) {

                         //check if data was pulled
                         if (mysqli_num_rows($queryRun) != NULL) {

                            //grab data from array and insert it into an array
                            while ($row = mysqli_fetch_assoc($queryRun)) {

                                //Populate Arrays to be used
                                $tran_amt[] = $row['tran_amt'];
                                $tran_inv[] = $row['tran_inv'];
                                $user_id[] = $row['user_id'];

                            }
                        }
                    }

                    ## Solve
                    list ($m4,$pickedItems) = knapSolveFast2($tran_amt, $tran_amt, sizeof($tran_amt) -1, $user_amt_due, $m);

                    # Display Result 
                    echo "<b><br><br>Invoice:</b><br>".join(", ",$tran_inv)."<br>";
                    echo "<b>Tran Amt:</b><br>".join(", ",$tran_amt)."<br>";
                    echo "<b>User_id:</b><br>".join(", ",$user_id)."<br>";
                    echo "<b>Max Value Found:</b><br>$m4 (in $numcalls calls)<br>";


                }
            }
            ?>

找到具有适当成熟度条件的用户的第一个while循环的结果是：

            Array
            (
                [user_id] => 9
                [due_payment] => 150
            )
            Array
            (
                [user_id] => 2
                [due_payment] => 150
            )

这意味着两个用户是到期的。但是在试图循环这些用户时。第二个用户的匹配永远找不到.只有第一个用户是。

    Array
    (
        [user_id] => 9
        [due_payment] => 150
    )


    Invoice:
    1102, 9022, 9113, 9029, 9116
    Tran Amt:
    100, 50, 100, 50, 50
    User_id:
    2, 5, 8, 5, 7
    Max Value Found:
    150 (in 19 calls)

请帮我找出我错过了什么。()

Answer 1

你的问题是，你把变量称为同一件事。

如果你看：

while ($row = mysqli_fetch_assoc($queryRun))  //External loop

在那个循环中，你有另一个

while ($row = mysqli_fetch_assoc($queryRun))   //Internal loop

因此，用于内部循环的外部循环中的变量实质上是覆盖外部循环变量，因此当第二次运行外部循环时，代码认为已经完成了，因为它引用的是内部循环变量。

要解决这个问题，必须重命名用于内部循环的变量。

Answer 2

注意，对于SECOND_和行，请尝试如下：

if ($SECOND_queryRun = mysqli_query($connect, $fetchQuery)) {

                     //check if data was pulled
                     if (mysqli_num_rows($SECOND_queryRun) != NULL) {

                        //grab data from array and insert it into an array
                        while ($SECOND_row = mysqli_fetch_assoc($SECOND_queryRun)) {

                            //Populate Arrays to be used
                            $tran_amt[] = $SECOND_row['tran_amt'];
                            $tran_inv[] = $SECOND_row['tran_inv'];
                            $user_id[] = $SECOND_row['user_id'];

                        }
                    }
                }