使用php批量更新记录

Question

I'm aware this question has been asked severally but i am unable to find the solution I'm looking for. I have managed to upload CSV files to my database and managed to update them, however there is a limit to number of records i get to update which is 90 rows at most. I've tried uploading records with 400 records but it is unable to update this records. Size wise in the database it has consumed 180kb.

If the query is the issue please guide as any assistance will be appreciated

  echo  $query="SELECT  bankstatement.referenceno,bankstatement.debit,bankstatement.credit,bankstatement.status,cashbook.referenceno,
            cashbook.debit,cashbook.credit,cashbook.status
                FROM   bankstatement CROSS JOIN
                             cashbook
                            where '$bank' = '$cash' and cashbook.credit = bankstatement.debit and cashbook.debit = bankstatement.credit and bankstatement.status = '0' and cashbook.status = '0' ";

     echo   $res= mysqli_query($db,$query) or trigger_error("Query Failed! SQL: $query - Error: ".mysqli_error(),E_USER_ERROR);


        if($res){
        echo $change = "update bankstatement set status='1' where referenceno in($bank)";
        echo $change1 = "update cashbook set status='1' where referenceno in($cash)";


        echo mysqli_query($db,$change);
        echo mysqli_query($db,$change1);

echo "<script>
        alert('Success in Reconciling Process!!!');
        window.location.href='viewreconcile.php';
        </script>
        ";
    }else{
        echo "<script>
        alert('Error in Reconciling Process!!!');
        window.location.href='managereconcile.php';
        </script>
        ";
    }
    }
 }
}

Answer 1

You can just skip the first steps to check if there's a back account that hasn't had it's switches set, because your update statement will just update everything in the where in array.

I added the where status='0' statement to your update query to limit the update to the rows that actually need the update.

// The back account numbers to update
$bank_accounts= [000001, 0000002, 000003];
// adding fix from https://stackoverflow.com/questions/5527202/mysqli-stmtbind-paramnumber-of-elements-in-type-definition-string-doesnt-ma
$types = implode('', array_fill(0, count($bank_accounts), 's'));
$bank_accounts = array_merge([$types], $bank_accounts);

$to_prepare = [];
foreach($bank_accounts as $key => $value) {
   $to_prepare[$key] = &$bank_accounts[$key];
}

// Preparing the parameters for the prepared statement
$clause = implode(',', array_fill(0, count($bank_accounts), '?'));


// Preparing the statement
$stmt = $db->prepare("update bankstatement 
                     set status='1' 
                     where 
                        status='0' 
                     and 
                        referenceno in($clause)
                     ");

// always check whether the prepare() succeeded 
if ($stmt === false) {
  trigger_error($db->error, E_USER_ERROR);
  return;
}

// bind the bank accounts to the parameters
call_user_func_array(array($stmt, 'bind_param'), $to_prepare );

// Update all the rows
$stmt->execute();

And this would work the same way for the cash query.