2

I am beginner in php learning procedural style. Learning prepared Statements and Mysqli. I am trying to learn how to check if fetching rows was successful or problematic. Note: I am not trying to check if there are matching rows or not after querying my mysql database for a keyword. No. To search for search results you usually use the mysqli_stmt_num_rows(). But I use the COUNT function. Like so:

$query = "SELECT COUNT(id) From links WHERE keyword = ?";
$stmt = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keyword);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt,$row_count);
    if($result = mysqli_stmt_fetch($stmt))
    {
        echo 'Row Count: ' .$row_count; echo '<br>';    
    }
    else
    {
        echo 'Record fetching failed!';
        echo 'Error: ' .mysqli_stmt_error($conn);
        echo 'Error: ' .mysqli_stmt_errno($conn);
    }
    mysqli_stmt_close($stmt);
}
else
{
    echo 'Search Preparation Failed!';
}
mysqli_close($conn);

I just need to check if the rows were fetched or not without any issue. need to know whether there were any trouble or not when trying fetching them. And if there are any troubles, then what the trouble is. I need php to tell me this. Here is my attempt:

<?php
//5.
//MULTIPLE RECORDS FETCHING - mysqli_stmt_get_result(): mysqli_fetch_array().

//ERROR REPORTING.
ini_set('display_errors','1');
ini_set('display_startup_errors','1');
error_reporting(E_ALL);

//MYSQLI CONNECTION.
//BAREBONE TEMPLATE.
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

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

if(!$conn = mysqli_connect("$server","$user","$password","$database"))
{
    echo 'Mysqli Connection Error' .mysqli_connect_error($conn);
    echo 'Mysqli Connection Error Number' .mysqli_connect_errno($conn);
}

mysqli_set_charset($conn,'utf8mb4');

//QUERY DATABASE.
$keyword = 'keywords';

$query = "SELECT id,date_and_time,domain,domain_email,ip,url,anchor,title,description,keyword,keyphrase From links WHERE keyword = ?";
$stmt = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keyword);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    while($col = mysqli_fetch_array($result,MYSQLI_NUM))
    {
        $id = $col['0'];
        $url = $col['5'];
        $title = $col['7'];
        $description = $col['8'];
        $keyword = $col['9'];
        
    echo 'Id: ' .$id; echo '<br>';
    echo 'Url: ' .$url; echo '<br>';
    echo 'Title: ' .$title; echo '<br>';
    echo 'Description: ' .$description; echo '<br>';
    echo 'Keyword: ' .$keyword; echo '<br>';
    }
//WHICH ONE OF THESE 15 IFs BELOW ARE VALID ?
//if($col == FALSE) //Output: Record fetching failed!Error: Error: 0
    //if($col === FALSE) //Output: Blank
    //if(!$col) //Output: Record fetching failed!Error: Error: 0
    //if(!$col = mysqli_fetch_array($result)) //Output: Record fetching failed!Error: Error: 0
    //if(!$col == mysqli_fetch_array($result)) //Output: Blank
    //if(!$col === mysqli_fetch_array($result)) //Output: Blank
    //if($col != mysqli_fetch_array($result)) //Output: Blank
    //if($col !== mysqli_fetch_array($result)) //Output: Blank
    //if($col !=== mysqli_fetch_array($result)) //Output: Parse error: syntax error, unexpected '=' in ...
    //if(mysqli_fetch_array($result) == FALSE)//Output: Record fetching failed!Error: Error: 0
    //if(mysqli_fetch_array($result) === FALSE) //Output: Blank
    
    if(!mysqli_fetch_array($result))//Output: Record fetching failed!Error: Error: 0
    //if(mysqli_fetch_array($result) != FALSE) //Output: Blank
    //if(mysqli_fetch_array($result) !== FALSE) //Output: Record fetching failed!Error: Error: 0---
    //if(mysqli_fetch_array($result) !=== FALSE) //Output: Parse error: syntax error, unexpected '=' in ...
    
{
    echo 'Record fetching failed!';
    echo 'Error: ' .mysqli_stmt_error($stmt);
    echo 'Error: ' .mysqli_stmt_errno($stmt);
}
mysqli_stmt_close($stmt);
}
mysqli_close($conn);
?>

I know there are more than one way of checking if fetching data was successful or not using mysqli. And I was experimenting with 15 different ways to see if "mysqli_fetch_array($result,MYSQLI_NUM)" worked or not. I wrote down my experiment results on the comments of each of the 15 lines that I tested. Kindly note the 15 IFs near the bottom of my code and point-out to me all the valid ones out of those 15. That way, I learn more than one way of coding. And as a bonus, do not mind showing me any better ways of checking than my 15 IFs, using mysqli and prepared statements.

Thank You.

EDIT: After reading responses to my questions, I realise, especially from Dharman's response, that it is not necessary to check if anything went wrong with mysqli_fetch_array() or not. And so, when I asked you people which of the following IFs are valid, I now realise none of them are valid:

//if($col == FALSE) //Output: Record fetching failed!Error: Error: 0
    //if($col === FALSE) //Output: Blank
    //if(!$col) //Output: Record fetching failed!Error: Error: 0
    //if(!$col = mysqli_fetch_array($result)) //Output: Record fetching failed!Error: Error: 0
    //if(!$col == mysqli_fetch_array($result)) //Output: Blank
    //if(!$col === mysqli_fetch_array($result)) //Output: Blank
    //if($col != mysqli_fetch_array($result)) //Output: Blank
    //if($col !== mysqli_fetch_array($result)) //Output: Blank
    //if($col !=== mysqli_fetch_array($result)) //Output: Parse error: syntax error, unexpected '=' in ...
    //if(mysqli_fetch_array($result) == FALSE)//Output: Record fetching failed!Error: Error: 0
    //if(mysqli_fetch_array($result) === FALSE) //Output: Blank
    
    if(!mysqli_fetch_array($result))//Output: Record fetching failed!Error: Error: 0
    //if(mysqli_fetch_array($result) != FALSE) //Output: Blank
    //if(mysqli_fetch_array($result) !== FALSE) //Output: Record fetching failed!Error: Error: 0---
    //if(mysqli_fetch_array($result) !=== FALSE) //Output: Parse error: syntax error, unexpected '=' in ...

I have checked many tutorials. They usually show something like this (which Dharman too demonstrated):

if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keyword);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt))
    while($col = mysqli_fetch_array($result,MYSQLI_NUM))
    {
        $id = $col['0'];
        $url = $col['5'];
        $description = $col['8'];
        $keyword = $col['9'];
            
        echo 'Id: ' .$id; echo '<br>';
        echo 'Url: ' .$url; echo '<br>';
        echo 'Description: ' .$description; echo '<br>';
        echo 'Keyword: ' .$keyword; echo '<br>';
    }
    mysqli_stmt_close($stmt);
}
mysqli_close($conn);

Note that on tutorials, they do not have anything like the following to check if mysqli_stmt_get_result() or mysqli_fetch_array() failed or not.

1.

if(!$result = mysqli_stmt_get_result($stmt))

if(!mysqli_fetch_array($result))

But I still wanted to check if anything goes wrong on these two lines or not. Since you people have told me that, nothing is likely to go wrong on the first one. I now still have remaining to question you about the second one. Hence, I ask: Is there anything likely to go wrong when trying to get result with mysqli_stmt_get_result() ? If so, then how to check this ? In short, which of the following new IFs are valid, if any ?

//if($result == FALSE) //Output: Blank
    //if($result) === FALSE //Output: Parse error: syntax error, unexpected '===' (T_IS_IDENTICAL) in ..
    //if(!$result) //Output: Blank
    //if(!$result = mysqli_stmt_get_result($stmt)) //Output: Record fetching failed!Error: Error: 0
    //if(!$result == mysqli_stmt_get_result($stmt)) //Output: Record fetching failed!Error: Error: 0
    //if(!$result === mysqli_stmt_get_result($stmt)) //Output: Record fetching failed!Error: Error: 0
    //if($result != mysqli_stmt_get_result($stmt)) //Output: Blank
    //if($result !== mysqli_stmt_get_result($stmt)) //Output: Blank
    //if($result !=== mysqli_stmt_get_result($stmt)) //Output: Parse error: syntax error, unexpected '=' in ...
    //if(mysqli_stmt_get_result($stmt) == FALSE)//Output: Record fetching failed!Error: Error: 0
    //if(mysqli_stmt_get_result($stmt) === FALSE) //Output: Record fetching failed!Error: Error: 0

Here is the new context of the script:

<?php
//5.
//MULTIPLE RECORDS FETCHING - mysqli_stmt_get_result(): mysqli_fetch_array().

//ERROR REPORTING.
ini_set('display_errors','1');
ini_set('display_startup_errors','1');
error_reporting(E_ALL);

//MYSQLI CONNECTION.
//BAREBONE TEMPLATE.
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

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

if(!$conn = mysqli_connect("$server","$user","$password","$database"))
{
    echo 'Mysqli Connection Error' .mysqli_connect_error($conn);
    echo 'Mysqli Connection Error Number' .mysqli_connect_errno($conn);
}

mysqli_set_charset($conn,'utf8mb4');

//QUERY DATABASE.
$keyword = 'keywordsd';

$query = "SELECT id,date_and_time,domain,domain_email,ip,url,anchor,title,description,keyword,keyphrase From links WHERE keyword = ?";
$stmt = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt,$query))
{
    mysqli_stmt_bind_param($stmt,'s',$keyword);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    //WHICH OF THESE FOLLOWING IFs ARE VALID ?
    //if($result == FALSE) //Output: Blank
    //if($result) === FALSE //Output: Parse error: syntax error, unexpected '===' (T_IS_IDENTICAL) in ..
    //if(!$result) //Output: Blank
    //if(!$result = mysqli_stmt_get_result($stmt)) //Output: Record fetching failed!Error: Error: 0
    //if(!$result == mysqli_stmt_get_result($stmt)) //Output: Record fetching failed!Error: Error: 0
    //if(!$result === mysqli_stmt_get_result($stmt)) //Output: Record fetching failed!Error: Error: 0
    //if($result != mysqli_stmt_get_result($stmt)) //Output: Blank
    //if($result !== mysqli_stmt_get_result($stmt)) //Output: Blank
    //if($result !=== mysqli_stmt_get_result($stmt)) //Output: Parse error: syntax error, unexpected '=' in ...
    //if(mysqli_stmt_get_result($stmt) == FALSE)//Output: Record fetching failed!Error: Error: 0
    //if(mysqli_stmt_get_result($stmt) === FALSE) //Output: Record fetching failed!Error: Error: 0
    {
        die('mysqli_stmt_get_result() Failed!);
    }
    else
    {
        while($col = mysqli_fetch_array($result,MYSQLI_NUM))
        {
            $id = $col['0'];
            $url = $col['5'];
            $description = $col['8'];
            $keyword = $col['9'];
            
            echo 'Id: ' .$id; echo '<br>';
            echo 'Url: ' .$url; echo '<br>';
            echo 'Description: ' .$description; echo '<br>';
            echo 'Keyword: ' .$keyword; echo '<br>';
        }
    mysqli_stmt_close($stmt);
    }
}
mysqli_close($conn);
echo '<b>'; echo __LINE__; echo '</b>'; echo '<br>';
?>
  • 2
    First you find the manual page for [mysqli_fetch_array()](https://www.php.net/manual/en/mysqli-result.fetch-array.php) then you look at the **Return Values** Section, and then you test the return value accordingly – RiggsFolly May 12 '21 at 11:29
  • 1
    Your `while` loop already does the job. It rows exist it will output them. When it runs out of rows (or none were returned to start with), it will stop outputting (or just never start). Are you really asking how to check if the query failed entirely, and crashed? It's a bit unclear what your concern is. – ADyson May 12 '21 at 11:31
  • 2
    The place where you are coding those IF's is not valid as that is only reached when the WHILE loop has consumed all the resultset, so at that point $row will only ever be set to NULL – RiggsFolly May 12 '21 at 11:31
  • @ADyson, Asking two things: 1). Did mysqli_fetch_array() fail for some reason. Like technical reason or my coding fault. 2). Did fetching data fail for any other reason other than there are no matching rows to be fetched. Imagine there are matching rows but no rows are getting fetched. Now what can go wrong in the fetching ? Need php to check that out. –  May 12 '21 at 11:35
  • @RiggsFolly, I was taking into account that if mysqli_stmt_fetch_array() fails then the WHILE loop would fail. Now need my php script to check if mysqli_stmt_fetch() failed or not. That is the simplest way of putting what I want checked. –  May 12 '21 at 11:38
  • 2
    https://stackoverflow.com/a/22662582/5947043 will help you configure PHP to display errors whenever any problem with mysqli occurs. Checking the return value of `mysqli_fetch_array` doesn't help you deal with technical errors - it simply returns `null` when there are no more rows left to read in the result set. That bit can't really go wrong, but problems can occur earlier on when trying to connect to the database, prepare a statement and execute the query. It looks like you have configured all the error reporting stuff anyway though (in the second script at least), so you should be ok. – ADyson May 12 '21 at 11:40
  • 2
    While loops wont fail, if the fetch reports anything other than a good fetch (i.e. null) the while just completes normally,, which is how it is terminated when you get to the end of the resultset – RiggsFolly May 12 '21 at 11:41
  • 1
    P.S. `Imagine there are matching rows but no rows are getting fetched`...could only happen if the query crashes - the error report would cover that for you. – ADyson May 12 '21 at 11:43
  • @RiggsFolly, You state that my IFs are not valid because they are after the WHILE loop. Originally, I did not code it like this but I saw fdglefevre doing it here: https://stackoverflow.com/questions/67252495/on-which-line-to-check-if-data-fetching-successful-or-not-when-using-mysqli-stmt –  May 13 '21 at 10:55
  • @RiggsFolly,, do you mind checking my original post, the bit after the EDIT heading and responding to it ? –  May 13 '21 at 10:58
  • @Dharman, do you mind checking my original post, the bit after the EDIT heading and responding to it ? –  May 13 '21 at 10:58
  • @ADyson, do you mind checking my original post, the bit after the EDIT heading and responding to it ? –  May 13 '21 at 10:58
  • 1
    Check the [PHP Manual for mysqli_stmt_get_result](https://www.php.net/manual/en/mysqli-stmt.get-result.php) **Specifically** the section about the **Return Values** That will explain what can be checked for in which situation – RiggsFolly May 13 '21 at 11:07
  • 1
    Agreed, the manual already answers your latest question. It both tells you what value is returned when an error occurs, and how to get the error message (which may depend on your PHP version - but again that's explained). – ADyson May 13 '21 at 11:18
  • I want to point out that you don't need any `if` statement for `mysqli_stmt_get_result`. PHP will throw an error when it happens from that function. If it still fails, then it means it's a bug in PHP and you should report it. – Dharman May 13 '21 at 11:29

1 Answers1

2

Your question is not as stupid as one might think. However, it's extremely unlikely that fetching the results would fail. I have tried to crash this function myself, but I was never able to achieve it.

This is so unlikely that I would advise to stop worrying about it. As long as you write the whole thing properly, the code should not fail. If you want to be thorough then you can call mysqli_error() right after calling mysqli_fetch_array to see if there's an error. Automatic error reporting doesn't even cover this scenario, but just in case there was a problem PHP will display a warning. If you ever see that warning "Error while reading a row" then it's likely you found a yet undiscovered bug in PHP rather than anything else.

So, stop worrying about this function.

P.S. Your code is full of cargo cult practices. As a rule of thumb, if you have enabled mysqli error reporting, then you don't need to check for any function's return value. PHP will automatically trigger an error if it happens. There's still a remote chance that a function call will fail without an error, but it's so unlikely that you should not worry about it in your code.

Here it is the same thing, but cleaned up without any redundant code:

<?php

//5.
//MULTIPLE RECORDS FETCHING - mysqli_stmt_get_result(): mysqli_fetch_array().

//ERROR REPORTING.
ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);

//MYSQLI CONNECTION.
//BAREBONE TEMPLATE.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

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

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

mysqli_set_charset($conn, 'utf8mb4');

//QUERY DATABASE.
$keyword = 'keywords';

$query = "SELECT id,date_and_time,domain,domain_email,ip,url,anchor,title,description,keyword,keyphrase From links WHERE keyword = ?";

$stmt = mysqli_prepare($conn, $query);
mysqli_stmt_bind_param($stmt, 's', $keyword);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($col = mysqli_fetch_row($result)) {
    $id = $col[0];
    $url = $col[5];
    $title = $col[7];
    $description = $col[8];
    $keyword = $col[9];

    echo 'Id: ' .$id;
    echo '<br>';
    echo 'Url: ' .$url;
    echo '<br>';
    echo 'Title: ' .$title;
    echo '<br>';
    echo 'Description: ' .$description;
    echo '<br>';
    echo 'Keyword: ' .$keyword;
    echo '<br>';
}
Dharman
  • 30,962
  • 25
  • 85
  • 135