How to process rows from the database?

Question

PHP

The task is to get rows from a large table, change each row according to certain rules and insert it into another table.
For example, at the input there is a line:
id = 17
name = victor
role = admin
We change it according to the rules, for example id = id + 1000; name is translated into Russian, role needs to start with a capital letter.
We must add
1017, Victor, Admin to another table.
I would like to solve the problem as correctly as possible.
There is a Database class, singleton pattern.
It creates a database connection and has methods for inserting, updating, and getting rows. For example:

function getRecords() {
    $info= array();
    $res = $this->connection->query("SELECT * FROM `table`");
    while ($row = $res->fetch_assoc()) {
       $info[] = $row;
    }
    return $info; 
}

This approach will not work for a large number of lines, can it be rewritten like this?

public function getRecords() {
    $info= array();
    return $this->connection->query("SELECT * FROM `table`"); 
}

And then it’s not entirely clear what to do, such a solution comes to mind, but it seems to me that it’s a little bad.

$db = new Database();
foreach ($db->getRecords() as $arr) {
    //класс Row тут не знаю, нужен или нет. 
    //если делать в нем public переменные id, role, name, то это аналог массива получается. 
    //создаем класс на основе строки из бд
    $row = new Row($arr);

    //передаем его классу, который будет улучшать поля
    $improve = new Improve($row);

    $improve->translateName();
    $improve->fixId();
    $improve->fixRole();

    //передаем массив функции, которая добавит в бд обработанную строку
    $db->addRow($row->returnArray());
}

In short:
1) Is the getRecords() method rewritten correctly? If not, how is this solved
2) How to properly design the string processing? I think the current version is bad.
Thank you!

Answer 1

Having done
, you returned from the method getRecords()not an array, but ... I'm not sure what it's called, but the loop will definitely not work. You can do something like this instead of this loop:

$results = $db->getRecords();
while ($row = $results->fetch_assoc())
{
    // Здесь $row - массив с индексами id, name, role
    // Изменяете его и заносите в новую таблицу сразу
}

Answer 2

The most important thing to consider in this task is that if this is live updated data, then receiving and updating should be within the same transaction.
Doing SELECT * FROM `table`and then updating records in a loop - you have a huge chance that during this time another thread will somehow update the table, and your code will insert the old value.
Well, I personally would not bother with the Improve class for the sake of a one-time task, it's easier to describe everything in a function.
It is difficult to advise something else without knowing the real conditions (how much data, how it is updated, supplemented, what are the requirements for availability at the time of the update) - this is all a delicate topic, many different hacks can be invented.