1

I am trying (and not succeeding) to run a while loop with a query that gets the last row in a table, uses that data then create a new row then selects the last row in the table again (which should be the row just created) then uses that data and creates a new row. This should repeat until the while loop is no longer true.

The problem I have is the while loop runs but always uses the row it selected the first time round. So the same row get inserted over and over until the while loop is false.

My question is how can I get the query to refresh when the while loop starts a new loop?

I have tried unset() but this did not work and am out of ideas. Here the while loop of my code (which is still in progress - still so much to add):-

while ($total_debt > 0) {
    $get_dp_accounts_list_query = "SELECT account_id
        FROM money_debt_planner
        WHERE customer_id = '".$_SESSION['customer_id']."'
        GROUP BY account_id";
    $get_dp_accounts_list = $db->Execute($get_dp_accounts_list_query);
    while (!$get_dp_accounts_list->EOF) {
        $get_dp_accounts_listArray[] = array('account_id'=>$get_dp_accounts_list->fields['account_id']);
        $get_dp_accounts_list->MoveNext();
    }
    foreach($get_dp_accounts_listArray as $acc_list) {
        $get_last_dp_entry_query = "SELECT *
            FROM money_debt_planner
            WHERE customer_id = '".$_SESSION['customer_id']."'
            AND account_id = '".$acc_list['account_id']."'
            ORDER BY line_id DESC";
        $get_last_dp_entry = $db->Execute($get_last_dp_entry_query);

        if($get_last_dp_entry->fields['end_debt_balance'] <> 0) {
            // calculate the interest this period
            $accounts_balance = $get_last_dp_entry->fields['end_debt_balance'] + $get_last_dp_entry->fields['estimated_spending'];

            $min_pay = $get_last_dp_entry->fields['min_pay_amount'];
            $min_pay_rate = $get_last_dp_entry->fields['min_pay_rate'];
            $interest_rate = $get_last_dp_entry->fields['interest_rate'];

            $int_rate = $interest_rate /100;
            $int_rate_a = $int_rate + 1;
            $int_value_b = $accounts_balance ;
            $int_value_c = ($int_rate_a * $int_value_b) - $int_value_b . '       ';
            $int_value_d = $int_value_c / 12;

            $statement_balance = $accounts_balance + $int_value_d;

            $min_pay_rate = ($statement_balance) * $min_pay_rate / 100;

            if($min_pay_rate < $min_pay) {
                $new_bill_amount = $min_pay;
            } else { 
                $new_bill_amount = $min_pay_rate;
            }
            if(($statement_balance) <= ($min_pay_rate) && ($statement_balance) <= $min_pay) { 
                $new_bill_amount = $statement_balance;
            }

            // next pay date 
            $next_due_day1 = date('d', strtotime ( $get_last_dp_entry->fields['date'] ));
            $next_due_year_month1 = date('Y-m', strtotime ( $get_last_dp_entry->fields['date'] ));

            $next_due_month_a_1 = strtotime ( "+ 1 month" , strtotime ( $next_due_year_month1 ) ) ;
            $next_due_month_b_1 = date ( 'Y-m' , $next_due_month_a_1);
            $total_days_in_this_month = date('t', strtotime($next_due_month_b_1) );

            if($next_due_day1 >= $total_days_in_this_month) {
                $next_due_date_a_1 = $total_days_in_this_month;
            } else { 
                $next_due_date_a_1 = $next_due_day1;
            }

            $payment_date_from_account1 = $next_due_month_b_1 .'-' . $next_due_date_a_1;
            $next_due1a = strtotime ( $payment_date_from_account1 ) ;
            $next_due1 = date ( 'Y-m-d' , $next_due1a );

            $dp_pay_date = $next_due1; // this will be 1 month from last entry
            $interest_this_period = $int_value_d; // this will be interest on last end balance + est spending
            $payment_this_period = $new_bill_amount; // this will be the min payment allowed including any over credit amount
            $end_balance = ''; // this will be current open balance + spending + interest - payment

            $sql = "INSERT INTO `money_debt_planner` (`customer_id`, `account_id`, `date`, `open_debt_balance`, `interest_rate`, `min_pay_rate`, `min_pay_amount`, `credit_limit`, `estimated_spending`, `interest_this_period`, `payment_this_period`, `end_debt_balance`)
            VALUES ('".$_SESSION['customer_id']."', '".$acc_list['account_id']."', '".$dp_pay_date."', '".$get_last_dp_entry->fields['end_debt_balance']."', '".$get_last_dp_entry->fields['interest_rate']."', '".$get_last_dp_entry->fields['min_pay_rate']."', '".$get_last_dp_entry->fields['min_pay_amount']."', '".$get_last_dp_entry->fields['credit_limit']."', '".$get_last_dp_entry->fields['estimated_spending']."', '".$interest_this_period."', '".$payment_this_period."', '".$end_balance."')";
            $db->Execute($sql);
        } // end if balance zero
    } // end account list

    // to do this will be total debt balance utstanding
    $total_debt = $total_debt - 1000;
}

Any help you can offer would be great :o)

miken32
  • 42,008
  • 16
  • 111
  • 154
DH010010
  • 23
  • 4
  • Perhaps you need `$mysqli->close();` before making a new query http://php.net/manual/en/mysqli.query.php or clear cache - not sure if this is relevant but could not find a mysqli version: http://stackoverflow.com/questions/5231678/clear-mysql-query-cache-without-restarting-server – Steve Jan 16 '16 at 03:20
  • You should be using prepared statements. Prepare the statement outside the loop, execute it inside the loop. – miken32 Jan 16 '16 at 04:18
  • I am using zen cart if that makes any difference...... I tried the `$mysqli->close()` and clear cache as you suggested but this did not work. I have no idea how to prepare the statements outside the loop and then include them, are you able to provide an example using my code? I'm not the best with all of this :o(n Thanks for you help, its very much appreciated. – DH010010 Jan 16 '16 at 11:30

0 Answers0