Run script every 1000 queried records

Question

I need to run a script every 1000 records consulted in the database. A simple example to help me is to put a <br><br> every 1000 records.

<?php

// Conectando com o banco de dados
require_once("../conecta.php");

// Chama classe para capturar os registros
require_once("excelwriter.class.php");

// Conta a quantidade de registros
$consulta = mysql_query("SELECT nomeUser, emailUser FROM si_login WHERE nomeUser != '' AND emailUser != ''");
$contador = mysql_num_rows($consulta);

// Instrução até chegar na quantidade final de registros
while($contador = mysql_num_rows($consulta)){

    // aqui irá o script a cada 1000 registros.

}

?>

I have 13640 users and I am creating 1 email capture file for every 1000 users. With the code that was passed, it generates 13 files and has a surplus of 640 users that are left behind because it did not reach 1000 counter. So there are 13 files instead of 14, the last would save not 1000, plus the rest (640).

Answer 1

Updated to demonstrate that the solution also meets the question update

Just insert this into the while (keeping the $contador = 0 original on the outside):

if( $contador++ % 1000 == 0 ){
    echo "<br /><br />";
    // Nessa linha cria-se o próximo arquivo de captação, que pode ter 1000 emails OU MENOS.
}
// Nessa linha, grava-se cada um dos emails no arquivo criado acima

So every 1000 records he will put an extra blank line.

Edit: This solution is similar to @Lucas Henrique’s, the difference is that I used the module ( % ) to count every 1000, and he used a counter that Zera. The result should be the same.

Note that this line of the original code does not make sense, it would be the case of a fetch. See Lucas' reply:

while($contador = mysql_num_rows($consulta)){

Answer 2

I put a counter in and changed it to mysql_fetch_array

<?php

// Conectando com o banco de dados
require_once("../conecta.php");

// Chama classe para capturar os registros
require_once("excelwriter.class.php");

// Conta a quantidade de registros
$result = mysql_query("SELECT nomeUser, emailUser FROM si_login WHERE nomeUser != '' AND emailUser != ''");
$contador = 0;

// Instrução até chegar na quantidade final de registros
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $contador++;
    // aqui irá o script a cada 1000 registros.
    if($contador == 1000){
        echo "<br /><br />";
        $contador = 0;
    }
}

?>

Answer 3

None of the solutions presented meet the requirement to perform a certain routine also for records that do not reach a lot of 1000.

With PHP it is not good to just separate presentation logic, PHP from HTML, but it is good to also separate different logics amid the same code.

In your case I refer to the read loop of the resource returned by Mysql. This loop should be used to read the Mysql resource. And point!

Anything beyond that will overload the application, perhaps even preventing it from continuing properly.

If you separate the task into two different loops, even if you waste a little performance (which for 13,000 records is almost nothing), you can manipulate the structure generated by the first while:

$records = array();

while( $rows = mysql_num_rows( $query ) ) {

    // Popula $records
}

// Faz alguma coisa com $records, itera essa nova matriz e aplica sua rotina

The difference here is that $Records, now, it has all the data you need at once and now just break this large array of 13,000 records into 14 smaller array with array_chunk():

$records = array_chunk( $records, 1000, true );

Simple as that. If you debug $Records with var_dump() (or print_r) before and after having passed him by array_chunk(), you will see that what was a one-dimensional matrix (assuming you populated it correctly and one-dimensionally within the while) is now at least two-dimensional.

And you can see perfectly that all new arrays have 1000 indexes, from zero to 999, except the last one, which will have 640, which is just what you need to, in a sequential loop, apply your routine to all 13640 records.