PHP for-loop with MySQL rows depending on the MySQL query result

Question

I can't figure out if there's a way to do something like this:

1 - Get user input (any signed/positive integer)

2 - Iterate through each number (e.g. user input a positive integer like 10, so loop goes through 10 iterations)

3 - PHP code loops through MySQL table in Database. MySQL table looks like this:

+----+------+------+---------+
| ID | ITEM | USER | ACTIONS |
+----+------+------+---------+
|  1 | 1234 |    5 |       0 |
|  2 | 1234 |    3 |       0 |
|  3 | 1234 |    1 |       0 |
|  4 | 1234 |    7 |       0 |
+----+------+------+---------+

4 - PHP code should loop through each of those 4 ACTIONS rows to find the lowest number, and start updating the ACTIONS field until there is a total number of 10 'actions' in the table. So after the user had input a value of 10, this is how the above table would look, if we start from the lowest number:

+----+------+------+---------+
| ID | ITEM | USER | ACTIONS |
+----+------+------+---------+
|  1 | 1234 |    5 |       3 |
|  2 | 1234 |    3 |       3 |
|  3 | 1234 |    1 |       2 |
|  4 | 1234 |    7 |       2 |
+----+------+------+---------+

5 - If another user inputs 10, this is how the table would look: (note that the starting point for this user would be the 3rd row i.e. the integer 2, since its the first occurrence of the smallest number):

+----+------+------+---------+
| ID | ITEM | USER | ACTIONS |
+----+------+------+---------+
|  1 | 1234 |    5 |       5 |
|  2 | 1234 |    3 |       5 |
|  3 | 1234 |    1 |       5 |
|  4 | 1234 |    7 |       5 |
+----+------+------+---------+

Please advise how this can be done, I know sufficient PHP and MySQL code so you can suggest advanced syntax, but I can't figure how it should be done. I don't know where to start. Besides iterating through the MySQL rows and using a for loop to iterate through each number the user input, how should I go about getting the expected result?

Answer 1

If concurrency or table locks would not be a problem, you could achieve with two query, a SELECT and then a single UPDATE, so you could calculate the final result and modify the value just one time per row.

Increasing the numerosity of records, performance may start to become a problem, so it would worth limiting the SELECT to the first n (which is the positive integer) records, and to do so, be sure to have added an index to the primary incrementing field.

The loop you're asking in point two, would operate on the resultset data, to produce the elaborated result, and the to execute the query update.

$positiveInteger = $_REQUEST['iterations'];
//sanitization...

$sql = 'SELECT id, actions FROM item_counters LIMIT :limit';
$db->connect();
$db->prepare($sql);
$db->bindInteger(':limit',$positiveInteger);
$db->execute();
$rows = $db->fetchAll();

$totalRecords = count($rows);

$incrementValue = intval($positiveInteger / $totalRecords);
$maxIncrementLimit = $positiveInteger % $totalRecords;

$currentRecord = 1;
$results = [];
foreach($rows as $row){
    if($currentRecord <= $maxIncrementLimit){
        $addition = $incrementValue + 1;
    }else{
        $addition = $incrementValue;
    }
    $results[$row['id']] = $row['action'] + $addition;
    $currentRecord++;
}

//then build the query (a little hacky: https://stackoverflow.com/a/27593831/2622455)

$sql = "...";
$db->query($sql);
$db->close();

Answer 2

As far as I can tell, there is a plethora of possibilities, all of which are stupid in one way or another. (I assume PDO, because mysqli is more inconvenient). However, I assume Giacomos solution is among the best solutions, since it's correct, intelligent ... and boring (no offense!). So I provide some other solutions, which are most likely less performant ...

querying and updating like a mad man.

$increments = 10; // or whatever
$stmt = $db->prepare('UPDATE table SET action = action+1 WHERE id=:id');
while($increments-- > 0) {
    // select the first row with the lowest action value
    // now, this query - apart from the limit - is helpful for all approaches
    $row = $db->query('SELECT id FROM table ORDER BY action ASC, id ASC LIMIT 1')->fetch();
    $stmt->bindValue(':id', $row['id'], PDO::PARAM_INT);
    $stmt->execute();
}

this solution is particularly stupid, because it needs sooo many queries. but it's probably correct (minus typos). also, execution time could be increased by not using the same prepared statement over and over again.

stored procedures

there's probably a solution with stored procedures or something. tbh i never warmed up to stored procedures, so this is essentially a placeholder.

less queries, but still stupid

as long as there are more rows than increments, just increment all rows by 1, then increment the rest.

$increments = 10;
$rowCount = $db->query('SELECT count(*) FROM table')->fetch()[0];
while($increments >= $rowCount) {
    $db->query('UPDATE table SET action=action+1');
    $increments -= $rowCount;
}
$stmt = $db->prepare('SELECT id FROM table ORDER BY action ASC, id ASC LIMIT :limit');
$stmt->bindValue(':limit', $increments, PDO::PARAM_INT);
$stmt->execute();
// now increase every row's action, from the result set
// yeah, I'm lazy.

even less queries, but still stupid

do the same as before, but add more than just +1, but instead ... +floor(increments/rowcount). this is essentially, what the next solution does, but it does so more efficiently ...

pre-compute target values

see Giacomos solution above ... or below, wherever it will be.

be slightly smarter

with the query I provided before (ordering by action, then by id) you could probably write foreach($rows as $currentRecord => $row){, and the if clause would say < instead of <=.

compute and find the one id, where the value "flips"

(I assume, this solution is actually the fastest, if done correctly. If really done correctly, this can probably be done in the database alone ... depending on the database and database-foo)

your table has one invariant (as far as i can tell, if it doesn't hold, this solution becomes problematic and very confusing):

there is an id, for which every row with a smaller id has action value x+1, and every id larger or equal has action value x.

SELECT COUNT(*), action FROM table GROUP BY action

(this should return AT MOST two rows, one for x, one for x+1, with their respective counts, or just one for x or x+1, depending on how you interpret the invariant)

now, you count how many rows have value x and how many have value x+1, then you compute how many y+1 and y you will have at the end. now, if your ids are the perfect sequence starting with 1, 2, 3, ... you don't even have to query for the id, because the id == count(y+1).

UPDATE table SET action = IF(id >= :flipid, :y_plus_one, :y)

so this is probably the solution with the least amount of data exchanged between database and php.

now, if you're really smart, instead of the first select you can use this instead:

SELECT COUNT(*), SUM(action) FROM table

then add onto the sum, modulo with the count, and the rest is almost as before ^^

do something more confusing

do the same as before, but try to use relative amounts (SET action = action + IF(...)). you'll notice, that you now need up to 2 pivot ids. and that's bat-shit crazy. don't do it. (disclaimer: i can't prove, there's no reason to actually do this, maybe there is...)

wrap it all up in transactions, because race conditions will ruin your day

the problem in and of itself is not really complicated, when you have no simultaneously acting users. With concurrent users, you have to wrap it into some transactions, lock some tables to prevent lost updates. (Some databases do this by default...)

Answer 3

1. build a query (A) to choose the first row to update (the hard work is here)
2. build a query (B) to update a row
3. build a query (C) by adding the where clause of the query (A) to the query (B)
4. loop over query (C) has necessary