MySQLi Query return value in case of Select with no matching rows

Question

I have a table bank as follows:

|name|day|time|
|jack| 1 |  2 |

I need to check for name, day and time. Now, even if I change values of WHERE condition parameters, such that no matching rows are found, it still prints "success". What could be wrong here ? Below if my code attempt:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM `bank` WHERE name='jack' AND day='1' AND time='2'";
$result = $conn->query($sql);

if ($result) 
{
        echo "success";
} 
else 
{
    echo "0 results";
}
$conn->close();
?>

Because your query is still successful, despite not giving any rows. So $query object is a mysqli_result object — Madhur Bhaiya, Sep 13 '18 at 04:17
check my updated answer https://stackoverflow.com/a/52306607/2469308 It should help you learn better! :) — Madhur Bhaiya, Sep 13 '18 at 05:03

Madhur Bhaiya · Accepted Answer · 2018-09-13T05:02:43.273

From MySQLi documentation:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE

So basically, even if query does not return any rows, it is still a successful query. You should rather check for number of rows returned. Change your if condition to:

If ($result->num_rows) {

Sidenote:

Now is the right time to take correct initial steps in working with PHP-MySQL. Instead of using query function, you should rather use Prepared Statements.
Always use Exception handling (try-catch), to catch other errors during query execution.

Here is the equivalent code using prepared statements and exception handling:

try {

    // Prepare the query
    $stmt = "SELECT * FROM bank 
             WHERE name = ? 
               AND day = ? 
               AND time = ?";

    // Bind the parameters
    // assuming that your day and time are integer values
    $stmt->bind_param("sii", 'jack', '1', '2');

    // execute the query
    $stmt->execute();

    // Getting results:
    $result = $stmt->get_result();

    if ($result->num_rows === 0) {
        echo "0 results";
    } else {
        echo "success";

        // reading results
        while($row = $result->fetch_assoc()) {
            $name = $row['name'];
            $day = $row['day'];
            $time = $row['time'];
        }
    }

} catch (Exception $e) {

     // your code to handle in case of exceptions here
     // generally you log error details, 
     //and send out specific error message alerts
}

score -1 · Answer 2 · answered Sep 13 '18 at 04:24

-1

insted of

    if ($result) 
{
        echo "success";
} 
else 
{
    echo "0 results";
}

can you try "mysql_num_rows($result) or mysqli_num_rows($result)"

we need to check if there is any rows satisfy the condition or not for that we need to use num rows. now you are checking if the query run or not even when the condition is not correct the query will run that is why you are getting success.

answered Sep 13 '18 at 04:24

VISHNU

948
8
15

Using procedural style is outdated (PHP4 era remnants). Always use object oriented approach using $result as object. Also, suggesting `mysql_*` extension based result is not good at all. It has been removed completely from PHP 7 – Madhur Bhaiya Sep 13 '18 at 05:23

MySQLi Query return value in case of Select with no matching rows

2 Answers2

Linked