Retry on database exception using php

Question

I have been working on exceptions thrown by php - mysqli & created this snippet below that tries to check a few times to see if a temp glitch has caused the database to fail. if so, it tries to recover. If it cannot then it quits/ ends.

Now while the logic is fine, I cannot find out why the echo statements do not occur at regular intervals as they should. Only towards the end (depending upon how the exception is thrown in the findMail() function), the output is spewed & correctly so. The throw new exception line can be changed to fail at all times or to pass the last time around. It is set to pass the last time round in the snippet. Would be great if someone can point to me why the echo is not displaying the output as it should at intervals.

Further I am not sure if this is a good or a bad idea in production. Any feedback on this is very welcome. Thanks to all.

The code Snippet:

<?php
$host        = 'localhost';
$user        = 'root';
$password    = '';
$database    = 'test';

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB

try
{
    $con = new mysqli($host,$user,$password,$database);
    if($con->connect_error)
    {
        throw new Exception("Goof Up");
    }   
    mysqli_set_charset($con, "utf8"); 
}
catch(Exception $e)
{
    echo $e->getMessage();
    exit;
}

// All well so far 

$cnt = 0;
if(findMail($con,$cnt)) echo "<br> Hurray !!";

function findMail($con,$cnt)
{
    try
    {   
        echo $cnt++."<br>";
        $query = "SELECT name, email from users";
        $stmt=$con->prepare($query);
        if($cnt < 3 ) throw new exception($cnt);
        if($stmt->execute())    
        {   
            $stmt->bind_result($name, $email);
            while ($stmt->fetch())
                {
                    $email = htmlspecialchars($email);
                    $name = htmlspecialchars($name);
                    echo $name.' ---- '.$email.'<br>'; 
                }       
        }
    }
    catch(Exception $e)
    {
        $cnt = (int)($e->getMessage());
//      echo $cnt;
        if($cnt === 4) { echo "Ending"; exit();} 
        sleep(5);
        findMail($con,$cnt);
    }   
    return true;
}
?>

Answer 1

It must have something to do with browser rendering while using ob_start and ob_flush under certain conditions.

I noticed when i set php header("Content-Encoding: none") to disable gzip compression:

UPDATE - added an example

<?php 
header("Content-Encoding: none"); 

ob_end_clean();
ob_start(); 

for($i=0;$i<5;$i++) 
{ 
    echo 'No. '.$i."<br>";
    ob_flush();
    flush();
    sleep(2);
}
?>

it flushes properly. Check out more details on the below links

PHP Streaming/Output Buffering no longer working

For php flush - how to disable gzip for specific file?

Answer 2

Using this PHP script with sleep(X) can cause the user to wait until its complete (successfully or not).

I recommend to call the script through AJAX (E.g. jQuery.ajax([...])), so the frontend is not blocked.

UPDATED - Example of using ajax instead

File: test.php

<?php
// verify if this is an ajax request (if, so exit the script before out)
if(isset($_GET['ajax'])) {

    $host        = 'localhost';
    $user        = 'root';
    $password    = '';
    $database    = 'test';

    // return result being parsed by JS through json_encode
    $result = array('data' => null, "error" => false);

   /**
    * function to return name and emails as array list
    */
   function findMail($con)
   {
        $data = null;
        $query = "SELECT username, uid from tbl_user";
        $stmt=$con->prepare($query);
        if($stmt->execute())
        {      
                $data = [];
                $stmt->bind_result($name, $email);
                while ($stmt->fetch())
                {
                        $email = htmlspecialchars($email);
                        $name = htmlspecialchars($name);
                        $data[] = array('name' => $name, 'email' => $email);
                }
        }
        return $data;
    }

    try
    {
        $con = new mysqli($host,$user,$password,$database);
        if($con->connect_error)
        {
                throw new Exception("Goof Up");
        }      
        mysqli_set_charset($con, "utf8");

        $result['data'] = findMail($con);
    }
    catch(Exception $e){
        $result['error'] = true;
    }

    echo json_encode($result);
    exit;
}
?>
<!DOCTYPE>
<head>
<title>TEST 1</title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
    <script type="text/javascript">

        $(function(){
                var retryCounter = 0;
                var repeatInterval = 3000;
                var repeatCall = function(){
                        if(retryCounter >= 3) {
                                console.log('max retries reached');
                                return;
                        }
                        console.log('Calling...');

                        $.ajax({
                                url: "test.php?ajax=1",
                                dataType: "json"
                        })
                        .done(function( result, textStatus, xhr ) {
                            // data = the json encoded result from errScript.php
                                console.log("received data from errScript.php");

                                if(!result) {
                                        console.log('STOPPED due to invalid/empty data');
                                        return;
                                }

                                // on error, retry and increase counter
                                if(result.error)
                                {
                                        setTimeout(repeatCall, repeatInterval);
                                        retryCounter++;
                                        return;
                                }

                                $.each(result.data, function(k, v){
                                        document.write(v.name + " --- " + v.email + "<br />");
                                });
                   }).fail(function(xhr, textStatus, err) { console.log('error'); });
                };

                setTimeout(function() { repeatCall(); }, repeatInterval);      
        });

   </script>

</head>
<body>
        <h1> TEST 1 </h1>
</body>  
</html>