Long-running PHP script not completing until the end

Question

I have the following code:

<?php

set_time_limit(0);
ignore_user_abort(1);

ini_set('memory_limit', -1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

/* CONECTION */
$conn = mysqli_connect('foo','foo','foo','foo') or die(mysqli_error($conn));
mysqli_query($conn, 'SET NAMES UTF8;') or die(mysqli_error($conn));

/* GENERATE SLUG */
function _slug($string) {
    return strtolower(preg_replace(['/[[:punct:]]+/', '/[[:space:]\s]+/'], ['', '-'], trim(stripslashes(iconv('UTF-8', 'ASCII//TRANSLIT', $string)))));
}

$url = 'enderecodosistema:porta'; // url sem a barra final
$limit = 50000; // limite total
$i = 1;
$file = 1;
$query = mysqli_query($conn,"select matricula, aluno, data_matricula from escola") or die( mysqli_error($conn) );
$total = mysqli_num_rows($query);

while($asc = mysqli_fetch_assoc($query)) {
    $student = _slug($asc['aluno']);
    $enrollment = $asc['matricula'];
    $date = date("Y-m-d h:i:s", strtotime($asc['data_matricula']));
    if($i == 1) {
        $sitemap = "sitemap_{$file}.xml";
        $dom = new \DOMDocument('1.0','UTF-8');
        $dom->formatOutput = true;
        $root = $dom->appendChild($dom->createElement( "urlset" ));
        $root->setAttribute('xmlns','http://www.sitemaps.org/schemas/sitemap/0.9');
        $root->setAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
        $root->setAttribute('xsi:schemaLocation','http://www.sitemaps.org/schemas/sitemap/0.9  http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd');
        $sxe = simplexml_import_dom( $dom );
    }   
    $child = $sxe->addChild('url');
    $child->addChild('loc', $url.'/'.$student.'/'.$enrollment);
    $child->addChild('lastmod', date(DATE_ATOM, strtotime($date)));    
    if($i == $limit) {  
        $dom->save($sitemap);
        $i = 0;
        $file++;
    }    
    $i++;
}
if($limit >= $total) {
    $dom->save($sitemap);
    $i = 0;
}

if($file-1) {
    $sitemap = "sitemap.xml";
    $dom = new \DOMDocument('1.0','UTF-8');
    $dom->formatOutput = true;
    $root = $dom->appendChild($dom->createElement( "sitemapindex" ));
    $root->setAttribute('xmlns','http://www.sitemaps.org/schemas/sitemap/0.9');
    $root->setAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
    $root->setAttribute('xsi:schemaLocation','http://www.sitemaps.org/schemas/sitemap/0.9  http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd');
    $sxe = simplexml_import_dom( $dom );
    for($j = 1; $j <= $file-1; $j++) {
        $child = $sxe->addChild('sitemap');
        $child->addChild('loc', $url.'/sitemap_'.$j.'.xml');
    }
     $dom->save($sitemap);

}

?>

It creates a sitemap (with a limit of 50,000 urls per file) of students, pulling from a database.

It turns out that the database is too big and the script takes too long and does not run to the end.

It is possible to adapt the code (with any solution) so that it is "executed in parts", avoiding that it is terminated without completing?

Answer 1

You can use paging each request and give one refresh in the script every 1 second.

Take an example:

    $pg = isset($_GET['pagina']) ? (int)$_GET['pagina'] + 1 : 1;
    $limit = ($pg - 1) * 10 .",10"; // limite de 10 alunos a cada requisição

    // query com limite de resultados
    $query = mysqli_query($conn,"select matricula, aluno, data_matricula from escola LIMIT $limit") or die( mysqli_error($conn) );
    $stop = $mysqli_num_rows($query) == 0; 
    // verifica se há mais registros -- se não houver a variável $stop irá terminar o script

    while($asc = mysqli_fetch_assoc($query)) {
        # [...] faça tudo aqui [...]
    }

    if(!$stop) // se não for para PARAR... de um refresh na página após 1 segundo
    header("Refresh: 1; url = meuScript.php?pagina=$pg"); // altere a URL para aonde está este script

If you want a limit of 50 for example:

$limit = ($pg - 1) * 50 .",50";

Your script would look like this:

    set_time_limit(0);
    ignore_user_abort(1);

    ini_set('memory_limit', -1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);

    /* CONECTION */
    $conn = mysqli_connect('foo','foo','foo','foo') or die(mysqli_error($conn));
    mysqli_query($conn, 'SET NAMES UTF8;') or die(mysqli_error($conn));

    /* GENERATE SLUG */
    function _slug($string) {
        return strtolower(preg_replace(['/[[:punct:]]+/', '/[[:space:]\s]+/'], ['', '-'], trim(stripslashes(iconv('UTF-8', 'ASCII//TRANSLIT', $string)))));
    }

    // ---------------------------------------------------------------------------------------------
    # PAGINAÇÃO
    $pg = isset($_GET['pagina']) ? (int)$_GET['pagina'] + 1 : 1;
    $limite = ($pg - 1) * 10 .",10"; // limite de 10 alunos a cada requisição
    // ---------------------------------------------------------------------------------------------

    $url = 'enderecodosistema:porta'; // url sem a barra final
    $limit = 50000; // limite total
    $i = 1;
    $file = $pg;

    // INSERIDO O LIMIT NA QUERY
    $query = mysqli_query($conn,"select matricula, aluno, data_matricula from escola LIMIT $limite") or die( mysqli_error($conn) );
    $total = mysqli_num_rows($query);

    if($total == 0) // SE NÃO HOUVER MAIS REGISTROS PARE A APLICAÇÃO
    exit;

    while($asc = mysqli_fetch_assoc($query)) {
        $student = _slug($asc['aluno']);
        $enrollment = $asc['matricula'];
        $date = date("Y-m-d h:i:s", strtotime($asc['data_matricula']));
        if($i == 1) {
            $sitemap = "sitemap_{$file}.xml";
            $dom = new \DOMDocument('1.0','UTF-8');
            $dom->formatOutput = true;
            $root = $dom->appendChild($dom->createElement( "urlset" ));
            $root->setAttribute('xmlns','http://www.sitemaps.org/schemas/sitemap/0.9');
            $root->setAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
            $root->setAttribute('xsi:schemaLocation','http://www.sitemaps.org/schemas/sitemap/0.9  http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd');
            $sxe = simplexml_import_dom( $dom );
        }   
        $child = $sxe->addChild('url');
        $child->addChild('loc', $url.'/'.$student.'/'.$enrollment);
        $child->addChild('lastmod', date(DATE_ATOM, strtotime($date)));    
        if($i == $limit) {  
            $dom->save($sitemap);
            $i = 0;
            $file++;
        }    
        $i++;
    }
    if($limit >= $total) {
        $dom->save($sitemap);
        $i = 0;
    }

    if($file-1) {
        $sitemap = "sitemap.xml";
        $dom = new \DOMDocument('1.0','UTF-8');
        $dom->formatOutput = true;
        $root = $dom->appendChild($dom->createElement( "sitemapindex" ));
        $root->setAttribute('xmlns','http://www.sitemaps.org/schemas/sitemap/0.9');
        $root->setAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
        $root->setAttribute('xsi:schemaLocation','http://www.sitemaps.org/schemas/sitemap/0.9  http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd');
        $sxe = simplexml_import_dom( $dom );
        for($j = 1; $j <= $file-1; $j++) {
            $child = $sxe->addChild('sitemap');
            $child->addChild('loc', $url.'/sitemap_'.$j.'.xml');
        }
         $dom->save($sitemap);

    }

    // ----------------------------------------------------------------------------
    # R F R E S H
    header("Refresh: 1; url = meuScript.php?pagina=$pg"); 
    //                            ^ AQUI
    // ALTERE A URL = {O SEU SCRIPT} ?pagina=$pg
    // ---------------------------------------------------------------------------