Hi 
I’ve been doing basic MySQL queries for a while, and this is the first time I come across an issue like this. Using PHP I’m trying to loop through each row in a table (620+ rows), do a check, if it fails, update the fields in the current row.
The script will be executed by a Cronjob every few minutes/hours.
The only issue I’m faced with is that after an x amount of rows have been successfully updated, the rest of them fail. I tried tinkering and noticed that adding a LIMIT 500 clause inside the initial query helped. This is how the code looks:
$allowed_bandwidth = mysqli_query($conn, "SELECT username, allotted_bandwidth, ppp FROM customers "); //Add LIMIT 500 to see this work as expected
$used_bandwidth = mysqli_query($conn, "SELECT (SUM(acctinputoctets) + SUM(acctoutputoctets)) usedBandwidth FROM radius.radacct GROUP BY username");
if (mysqli_num_rows($allowed_bandwidth) > 0) {
while ($row = mysqli_fetch_array($allowed_bandwidth, MYSQLI_ASSOC)) {
$username = $row['username'];
$allotted_bandwidth = $row['allotted_bandwidth'];
$ppp = $row['ppp'];
if ($allotted_bandwidth === 0) {
//do nothing
} else {
if (mysqli_fetch_assoc($used_bandwidth)['usedBandwidth'] <= $allotted_bandwidth) {
echo $username." Overlimit";
echo "<br>";
if ($ppp !== 'off') {
mysqli_query($conn, "UPDATE customers SET ppp='off' WHERE username='$username'");
}
// echo "".mysqli_affected_rows($conn)."<br>";
} else {
echo $username." Clear";
echo "<br>";
if ($ppp !== 'on') {
mysqli_query($conn, "UPDATE customers SET ppp='on' WHERE username='$username'");
}
// echo "".mysqli_affected_rows($conn)."<br>";
}
}
}
}
What happens is that the code executes “SET ppp='on'” for the first 620 rows only. The last ±20 consecutive rows are ‘failing’, as in, the code is executing “SET ppp='off'” for them, which is not supposed to happen.
Not wanting to be too verbose, I won’t display the table design (the code gives a clear idea how the tables look).
Can you see anything apparently wrong with the code?
The workaround I’m currently using is to duplicate the entire code block above, just editing the limits to “LIMIT 501, 500” , “LIMIT 1002, 500”, etc which is really messy. It works as expected though and updates the rows accordingly.
I hope my question is okay for this forum…
