While loop not stopping

paul007 · February 12, 2020, 3:09pm

I have below script. it does the while loop 100%. it updates the mysql database one at a time as it should. the problem I have now is that the while loop does not end and go to the next statement as it should. it keeps pollong the database. so when in back end you change to 0 it automatically updates again.

Please see if you can help me to see where I can stop this while loop when there are no more loops

// Here I select the amount of rows

$sql_query = "SELECT ae
FROM `debitorderrejectionimport`
WHERE ae = '0'";


$rowCount = mysqli_query($conn,$sql_query);
$rowCountUpdate = mysqli_num_rows($rowCount);
echo $rowCountUpdate;
while($rowCountUpdate > 0){
    $sql = "UPDATE
        `ttee`.`au1`
        INNER JOIN `ttee`.`au` 
            ON (`au1`.`id` = `au`.`id`)
        INNER JOIN `ttee`.`ae1` 
            ON (`ae1`.`idd` = `au1`.`idd`)
        INNER JOIN `ttee`.`debitorderrejectionimport` 
            ON (`debitorderrejectionimport`.`nr` = `ae1`.`id`)
            SET `au`.`amount` = `ae1`.`amount` + `au1`.`amount`,
            `debitorderrejectionimport`.`ae` = au.id
            ;";
    $result = mysqli_query($conn, $sql);
    $updated = mysqli_affected_rows($conn);
$rowCountUpdate - ($updated);}

// if it finished updating and there is no more rows it must continue with below query

mysqli_query($conn, " 
    INSERT INTO `sataxicrm754`.`debitorderrejectionimport_back` (
      `Outbound`,
      `Allocation`,
      `AccountName`,
      `QueryComplaintType`,
      `QueryStatus`,
      `Querytypeoption`,
      `Description`,
      `DealID`,
      `Deals`,
      `Assignedusername`,
      `Teams`,
      `CampaignName`,
      `CampaignID`,
      `inserted`,
      `idnumber`,
      `nr`,
      `datew`,
      `premium`,
      `policynumber`,
      `ContactNumber`,
      `CollectionType`,
      `OpportunityAmount`,
      `Broker`,
      `impref`,
      `id`
    ) 

    SELECT 
      `Outbound`,
      `Allocation`,
      `AccountName`,
      `QueryComplaintType`,
      `QueryStatus`,
      `Querytypeoption`,
      `Description`,
      `DealID`,
      `Deals`,
      `Assignedusername`,
      `Teams`,
      `CampaignName`,
      `CampaignID`,
      `inserted`,
      `idnumber`,
      `nr`,
      `datew`,
      `premium`,
      `policynumber`,
      `ContactNumber`,
      `CollectionType`,
      `OpportunityAmount`,
      `Broker`,
      `impref`,
      `id` 
    FROM
      `sataxicrm754`.`debitorderrejectionimport` 
      WHERE QueryComplaintType <> 'QueryComplaintType'
    ");

Mchojrin · February 12, 2020, 5:37pm

The problem is probably here:

$rowCountUpdate - ($updated);

If there’s nothing to be updated (for instance if the values in the db are identical to the ones you’re trying to push in), there won’t be any updated record, and thus your rowCountUpdate won’t change (since you’re subtracting 0 from it), which means that, should the while start, it will never reach 0 and finish.

astonecipher · February 12, 2020, 7:33pm

As mentioned, you will probably want to do this instead.

$rowCountUpdate -= $updated;

Mchojrin · February 12, 2020, 10:04pm

Oh, I hadn’t noticed there was no assignment! (Shame on me ). Still… I don’t think that’ll fix the issue… you probably want to put a second condition on the while… something like:

while($rowCountUpdate > 0 && $updated){

astonecipher · February 13, 2020, 12:34am

Looking at this again, there is no reason for the loop. Sql is declarative, so a single statement would handle it without looping.