On Which Line To Check If Data Fetching Successful Or Not When Using mysqli_stmt_bind_result?

Question

I need to show website visitor that something went wrong should him making queries to my database fails technically. Want to get the php code to echo "Sorry! Something went wrong!" if for some reason data fetching failed.

Following are some ways I am trying to accomplish this. 3 samples. They result in neverending loops thus crashing my browser. (NOTE the IFs on each sample. That is where the 3 samples differ).

I ranked them according to favourite .... How to fix this to bare minimum to achieve my purpose ? Would appreciate codes samples. I know how to achieve this with mysqli_stmt_get_result() but need to learn with the mysqli_stmt_bind_result() in procedural style programming. Not into oop yet. Nor pdo.

1.

<?php

//LOOPS NEVERENDING

$server = 'localhost';
$user = 'root';
$password = '';
$database = 'brute';

$conn = mysqli_connect("$server","$user","$password","$database");

$keywords = 'keyword';

$query = 'SELECT id,domain from links WHERE keywords = ?';
$stmt = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keywords);
    if(mysqli_stmt_execute($stmt))
    {
        while($result = mysqli_stmt_bind_result($stmt,$id,$domain))
        {
            mysqli_stmt_fetch($stmt);
            
            echo 'Id: ' .$id; echo '<br>';
            echo 'Domain: ' .$domain; echo '<br>';
            
            if(!$result)
            {
                echo 'Sorry! Something went wrong. Try again later.';
            }
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);
}

?>

<?php

//LOOPS NEVERENDING

$server = 'localhost';
$user = 'root';
$password = '';
$database = 'brute';

$conn = mysqli_connect("$server","$user","$password","$database");

$keywords = 'keyword';

$query = 'SELECT id,domain from links WHERE keywords = ?';
$stmt = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keywords);
    mysqli_stmt_execute($stmt);
    
    while(mysqli_stmt_bind_result($stmt,$id,$domain))
    {
        if(mysqli_stmt_fetch($stmt)) //If 'Rows Fetching' were successful.
        {
            echo 'Id: ' .$id; echo '<br>';
            echo 'Domain: ' .$domain; echo '<br>';
        }
        else //If 'Rows Fetching' failed.
        {
            echo 'Sorry! Something went wrong. Try again later.';
        }
    }       
    mysqli_stmt_close($stmt);
    mysqli_close($conn);
}

?>

<?php

//LOOPS NEVERENDING

$server = 'localhost';
$user = 'root';
$password = '';
$database = 'brute';

$conn = mysqli_connect("$server","$user","$password","$database");

$keywords = 'keyword';

$query = 'SELECT id,domain from links WHERE keywords = ?';
$stmt = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keywords);
    if(mysqli_stmt_execute($stmt)) //If 'Query Execution' was successful.
    {
        while(mysqli_stmt_bind_result($stmt,$id,$domain))
        {
            mysqli_stmt_fetch($stmt);
            
            echo 'Id: ' .$id; echo '<br>';
            echo 'Domain: ' .$domain; echo '<br>';
        }       
    }
    else //If 'Query Execution' failed.
    {
        echo 'Sorry! Something went wrong. Try again later.';
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);
}

?>

Answer 1

Basically, all of the approaches are wrong. If the query fails then there will be an error triggered automatically by PHP as long as you have error reporting enabled, see How to get the error message in MySQLi?. You should not be checking for it manually. Your code is way longer than it needs to be. Consider how it should be done properly:

<?php

$server = 'localhost';
$user = 'root';
$password = '';
$database = 'brute';

// enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($server, $user, $password, $database);
mysqli_set_charset($conn, 'utf8mb4');

$keywords = 'keyword';

$query = 'SELECT id,domain from links WHERE keywords = ?';
$stmt = mysqli_prepare($conn, $query);
mysqli_stmt_bind_param($stmt, 's', $keywords);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $domain);

// This loop will keep on going as long as fetch() returns true.
// It will return false when it reaches the end
while (mysqli_stmt_fetch($stmt)) {
    echo 'Id: ' .$id;
    echo '<br>';
    echo 'Domain: ' .$domain;
    echo '<br>';
}

When you enable mysqli error reporting, your code becomes much simpler. There's no need for any special message to the user. When a query fails then an error will be triggered just like any other PHP error and handled the same way. If you want you can then customize the error page, but that is a completely separate topic.

The loop on mysqli_stmt_fetch() is used to fetch the data from the server. The data will be read row by row, and when there are no more rows mysqli_stmt_fetch() will return false.

mysqli_stmt_bind_result() needs to be called only once. Its purpose is to provide variable placeholders into which the data will be populated.

Answer 2

Their is a mistake in your code that make your code loop forever. In such cases, a while loop in your code is one of the most common cause of this kind of problem.

When you are using any function you should consider to read the documentation of this function and check what kind of errors and what kind of results (returns) it can give you. Functions can throw errors, warning, Exceptions, or return error codes. Also dont underestimate examples on the PHP documentation of each function. It give you a good idea of how things works.

When you are calling a function it's generally good to check the result returned. I will not rewrite all the documentation here but here is an example for mysqli_stmt_bind_result :

https://www.php.net/manual/en/mysqli-stmt.bind-result.php

This part of the procedural example is important for you :

/* bind variables to prepared statement */
mysqli_stmt_bind_result($stmt, $col1, $col2);

/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
    printf("%s %s\n", $col1, $col2);
}

Here you can see how mysqli_stmt_bind_result and mysqli_stmt_fetch can be used together to loop through your results. But this is not perfect for error checking. The documentation of mysqli_stmt_bind_result says in section Return values :

Returns true on success or false on failure.

So in case of failure of this function you can check for errors this way :

if (!mysqli_stmt_bind_result($stmt, $id, $domain)) {
    die("mysqli_stmt_bind_result has failed !"); // of course you can use something more sophisticated than dying...
}

In case of success, and the source of your infinite loop is here, it returns true. So doing while(mysqli_stmt_bind_result($stmt, $id, $domain)) is a mistake, first because you dont have to loop on this function (it's a job for mysqli_stmt_fetch), secondly because mysqli_stmt_bind_result will ever returns true in your case and your while loop will never end.

For mysqli_stmt_fetch now, there is a subtle difference. Check the return values : https://www.php.net/manual/en/mysqli-stmt.fetch.php

Return Value	Description
true	Success. Data has been fetched
false	Error occurred
null	No more rows/data exists or data truncation occurred

Here you have to check for 3 different values and dont forget that null and false can both be evaluated as falsy if you dont take care.

This doesnt allow to display an error :

while (mysqli_stmt_fetch($stmt)) { // is result false or null ? we dont know
    printf ("%s (%s)\n", $id, $domain);
}

This is more complete :

$fetchResult = null;
while ($fetchResult = mysqli_stmt_fetch($stmt)) {
    printf ("%s (%s)\n", $id, $domain);
}
/* dont forget to use 3 equal signs to also compare variable type */
/* null == false   values are considered the same */
/* null === false   this also compare variable types, here types are not the same */
if ($fetchResult === false) {
    die("mysqli_stmt_fetch failed !");
}

Now you are free to read the documentation of all called functions to do your errors checks.

Also note that another error management (less verbose and error prone) is possible using Exceptions objects like explained by Dharman answer