how to check if mysql query return no result(record not found) using php?

how to check if mysql query return no result(record not found) using php?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Detecting Missing Records in MySQL Queries from PHP

A common pattern in backend development involves bulk processing user input against a database schema. Imagine a script that accepts a list of image filenames via a textarea, iterates through them, and fetches metadata from a testdb table. The goal is often to display found records while also flagging entries that do not exist in the database. Based on the code provided, the logic currently correctly outputs information for existing images but fails to report missing ones. The root cause lies in how the result set is evaluated before the iteration begins.

The snippet checks if($result == 0) immediately after executing the query. This condition assumes that a valid mysqli_query return value can be compared directly to an integer zero to signify an empty result set. In reality, this approach is flawed for two distinct reasons. First, mysqli_query returns a boolean resource if the query succeeds, regardless of whether rows were actually found. A successful query with zero matching rows returns a "falsy" resource object, not the integer 0. Second, in PHP versions prior to 7.0, boolean false is often displayed as an empty string when echoed, but it is never strictly equal to the integer 0 unless explicitly cast or compared using loose equality with specific context.

To reliably detect if a query returned no rows, you must distinguish between a query execution failure and a query that simply matched nothing. The provided code uses if (!$result) which would trigger on both an SQL syntax error and an empty result set. However, since the subsequent logic attempts to fetch rows only when the condition is false (implied by the structure), the specific check for "no rows" requires verifying that a resource was returned and that no data exists within it.

The correct implementation involves checking if the result object is not false first. If the query executed successfully, you must then explicitly verify that the row count is zero. You can do this by calling mysqli_num_rows() on the result object before entering the while loop. This function returns an integer representing the number of rows in the result set.

Here is the corrected logic flow:

foreach ($lines as $line) {
    $line = rtrim($line);
    
    // Ensure we don't process empty lines
    if (empty($line)) continue;

    $result = mysqli_query($con, "SELECT ID, name, imgUrl, imgPURL FROM testdb WHERE imgUrl LIKE '%$line'");

    // Always check for query execution errors first
    if ($result === false) {
        // Handle SQL syntax errors or connection issues
        error_log("Query failed: " . mysqli_error($con));
        continue; 
    }

    // Check specifically for an empty result set using num_rows
    if (mysqli_num_rows($result) === 0) {
        echo "Not Found Image: {$line}<br>";
    }

    // Process found records
    while ($row = mysqli_fetch_array($result)) {
        $totalRows++;
        echo "<tr>";
        echo "<td>" . $row['ID'] ."(".$totalRows. ")</td>";
        echo "<td>" . $row['name'] . "</td>";
        echo "<td>" . $row['imgPURL'] . "</td>";
        echo "<td>" . $row['imgUrl'] . "</td>";  echo "</tr>";
    }
}

A critical consideration in this scenario is the use of the LIKE '%$line%' operator. This allows for partial string matching, which can lead to false positives if filenames contain common substrings or special characters. For instance, searching for "cat" might inadvertently match a file named "catalog". Furthermore, using wildcards (%) on both sides of the variable is generally slower than an exact match because it prevents the database from utilizing indexes effectively.

If your application uses Laravel, consider leveraging its query builder for cleaner syntax and better safety against SQL injection:

$found = DB::table('testdb')->where('imgUrl', 'like', "%{$line}%")->get();
if ($found->isEmpty()) {
    echo "Not Found Image: {$line}<br>";
}

When optimizing this workflow, ensure that the imgUrl column in your database table is indexed. Without an index, every search operation requires a full table scan, which becomes prohibitively expensive as the dataset grows. Additionally, if exact matches are sufficient for your use case, removing the wildcards will significantly improve query performance and reduce ambiguity in matching logic.

Our network