Update records by blocks

Question

Hi,

I need to make a update in a field (called block) for each given amount of records. I have 100 records, for every 10, I must enter a (automatic) number in the field. The idea is to divide the records at the time of the consultation, thus:

Block 1 - John, Mary,... until the tenth.

Block 2 - Pedro, Manoel,... until the twentieth. etc....

I used the 100 example, but I actually have 5,000 that grows every day, so when I perform a certain function - or automatically, you’ll have to start creating and inserting a new block after the 100 records and continue until I’m identified that 10 have already been updated, when there is the 111 record, will already begin to insert the 12 block, in case.

Answer 1

You can use the method COUNT MYSQL. It allows you to count the number of records in a table quickly. With the amount of records and the amount of records per block on hand, the latter is a constant, we can easily figure out the customer block. With this, you can enter this value in the database.

In the example below, we insert a customer into the bank and automatically the block designated to it is calculated (in the example below I am using PDO):

$NUMERO_DE_CLIENTES_POR_BLOCO = 10;

$numeroClientes = $pdo->query("SELECT count(*) FROM clientes")->fetchColumn();
$blocoAtual = intval($numeroClientes/$NUMERO_DE_CLIENTES_POR_BLOCO) + 1;
$cliente = [ ':nome' => 'Joao', ':bloco' => $blocoAtual ];

$inserirCliente = $pdo->prepare("INSERT INTO clientes (nome, bloco) VALUES (:nome, :bloco)");
$inserirCliente->exec($cliente);