Run script for every 1000 queried records

0

I need to run one script every 1000 records queried in the database. A simple example to help me is to put a <br><br> to 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'm 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 counter 1000. So 13 files remain instead of 14, the last one would store 1000, plus the rest (640).

    
asked by anonymous 15.05.2014 / 05:18

3 answers

1

Neither of the solutions presented meets the requirement to perform a given routine also for records that do not reach a lot of 1000.

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

In your case I mean the loop to read the resource returned by MySQL. This loop should serve to read the MySQL feature.

Anything besides 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 has all the data you need in one go and now you just have to break that large array of 13,000 records into 14 smaller array with array_chunk () :

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

Simple like that. If you debug $ records with var_dump() (or print_r ) before and after passing it by array_chunk() , you'll see that what was a one-dimensional array (assuming you have populated it correct and unidimensionally within the while ) is now one, at least, two-dimensional.

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

    
15.05.2014 / 21:47
6
  

Updated to demonstrate that the solution also answers the question update

Just insert this within the while (holding the $contador = 0 original from 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 will put one more blank line.

Edit: This solution is similar to that of @Lucas Henrique, the difference is that I used the module (%) to count every 1000, and he used a counter that zeroes. 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's answer:

while($contador = mysql_num_rows($consulta)){
    
15.05.2014 / 05:36
4

I placed a counter 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;
    }
}

?>
    
15.05.2014 / 05:35