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:
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 :)
