duanjitong7226 2017-04-04 09:42
浏览 97
已采纳

PHP和MySQL:While Loop

first, i am pretty new to PHP and MySQL, so i still code precedurally.

I am working on an application that takes transactions and pays out a due amount at a certain maturity date to users who have previously made a donation. i have a function knapSolveFast2 that solves the knapsack problem (where a set of transaction amounts in a database adds up to a due amount for a users who's maturity date is up). currently, my demo database looks like this:

enter image description here

if my current date (now) = 2017-04-03 11:36:03 = CAST(NOW() AS DATETIME), my application is meant to loop through the database, fetch users whose maturity_date is >= 1 month from tran_date (i.e. WHERE maturity_date <= CAST(NOW() AS DATETIME) ). Take each user found and pair them for payment in a while loop to other users tran_amt in the database whose tran_amt sums up to the maturity users found due_amount using the knapsack function knapSolveFast2.

Question: after finding the user with maturity date due for payment (2 users) with the first while loop, i am trying to run an inner while loop to pair each user to other users whose tran_amt sums up to the fetched user's due amount. the problem here is, the inner while loop only runs for the first user found an not for thesecond user.

The code

<?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>";


                }
            }
            ?>

the result of the first while loop that finds user with the proper maturity date criteria is:

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

this means 2 users are due. but on trying to loop these users. the match for the second user is never found... only that of the first user is.

    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)

Please help me figure out what i am missing. Thaaaaank you :)

  • 写回答

2条回答 默认 最新

  • douxu0550 2017-04-04 09:55
    关注

    Your problem is that you call the variables the same thing.

    If you look at :

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

    Inside that loop you have another

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

    So the variables inside the external loop, you are using for the internal loop are essentially overwriting the External loops variables, and thus when it is time for the second run of your External loop, the code think it is done, since it is refering to the internal loops variable

    To fix this, you must rename the variables you use for the internal loop

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面