0

I try to use num_rows with prepared statment but always the result is num_rows 0

DB:

============
name | code
============
test | 1

Php:

function Add(){
    $global $name;
    $name = $_POST['name'];
    $stmt = $mysqli->prepare("SELECT * FROM table WHERE name=?");
    $stmt->bind_param('s', $name);
    $stmt->execute();
    $stmt->get_result();

    if ( $stmt->num_rows > 0 ) {
        echo "Exist name";
        $stmt->close();
        $mysqli->close();
    }else {
    //OTHER CODE
    }
}

When I try to print num_rows it returns 0 but the result should be 1.

What is the problem ?

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
Simone Rossaini
  • 8,115
  • 1
  • 13
  • 34

1 Answers1

1

Because you use mysqli_stmt::get_result() to convert the result-set to a MySQLi result object, the results from the statement is exhausted - and you can now check the num_rows property of the resulting object instead.

function Add(){
    global $name;
    $name = $_POST['name'];
    $stmt = $mysqli->prepare("SELECT * FROM table WHERE name=?");
    $stmt->bind_param('s', $name);
    $stmt->execute();
    $result = $stmt->get_result(); // Assign the result to a variable

    if ($result->num_rows > 0) { // Check the number of rows in the MySQLi result object
        echo "Exist name";
        $stmt->close();
        $mysqli->close();
    } else {
        //OTHER CODE
    }
}

Alternatively, drop the get_result() method if you don't intend to use the result (if you just want to check for the number of rows). 

function Add(){
    global $name;
    $name = $_POST['name'];
    $stmt = $mysqli->prepare("SELECT * FROM table WHERE name=?");
    $stmt->bind_param('s', $name);
    $stmt->execute();
    $stmt->store_result();

    if ($stmt->num_rows > 0) {
        echo "Exist name";
        $stmt->close();
        $mysqli->close();
    } else {
        //OTHER CODE
    }
}

Yet another way is to check if the $stmt->fetch() returned a truthy value, which will indicate that at least 1 row exists (you need to remove the get_result() method for this as well).

This would be my preferred approach to see if there was any results returned.

if ($stmt->fetch()) {
    echo "Exist name";
}
Qirel
  • 25,449
  • 7
  • 45
  • 62