user2112618 user2112618 - 6 months ago 20
PHP Question

mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement (one function works other does not)

This question has been answered before but not in the same format.

I am trying to create an object method to grab a database row. I keep getting the titled error. The odd thing is that if i call the same code of the function directly in the parent class method it works. Here are the two examples.

Example 1: works
Function called from seperate PHP page like:
$email = trim($_POST['email']);
$password = trim($_POST['password']);

$found_user = User::authenticate($email, $password);


Here is the function that is working. (I setup the function as I would have if I were making a reusable function to test if my code was bad)

public static function authenticate($username="", $password="") {

$db = Database::getInstance();
$mysqli = $db->getConnection();

$field = 'email';

$sql_query = "SELECT * ";
$sql_query .= "FROM `".static::$table_name."`";
$sql_query .= "WHERE {$field}=? ";
$sql_query .= "LIMIT 1";

$stmt = $mysqli->stmt_init();

if (!$stmt->prepare($sql_query)) {
$error = "Errormessage: " . $mysqli->error;
return $error;
}

if (!$stmt->prepare($sql_query)) {
$error = "Errormessage: " . $stmt->error;
return $error;
} else {
$stmt->prepare($sql_query);

$valueType = gettype($username);

if ($valueType == 'string') {
$stmt->bind_param('s', $username);
} else if ($valueType == 'integer') {
$stmt->bind_param('i', $username);
} else if ($valueType == 'double') {
$stmt->bind_param('d', $username);
} else {
$stmt->bind_param('b', $username);
}

$stmt->execute();
$result = $stmt->get_result();

return $result->fetch_object();
}
}


Now, if I call the function in the same method but from an extended class (parent class is User which extends DatabaseOptions). The functions are written as such:

public static function authenticate($username="", $password="") {
$dbObject = static::get_row('email', $username);

if (isset($dbObject)) {
return $dbObject;
}
}


The function within the DatabaseObject:

public function get_row($field, $value) {
$db = Database::getInstance();
$mysqli = $db->getConnection();

$sql_query = "SELECT * ";
$sql_query .= "FROM `".static::$table_name."`";
$sql_query .= "WHERE {$field}=? ";
$sql_query .= "LIMIT 1";

$stmt = $mysqli->stmt_init();

if (!$stmt->prepare($sql_query)) {
$error = "Errormessage: " . $stmt->error;
return $error;
} else {
$stmt->prepare($sql_query);

$valueType = gettype($value);

if ($valueType == 'string') {
$stmt->bind_param('s', $value);
} else if ($valueType == 'integer') {
$stmt->bind_param('i', $value);
} else if ($valueType == 'double') {
$stmt->bind_param('d', $value);
} else {
$stmt->bind_param('b', $value);
}

$stmt->execute();
$result = $stmt->get_result();

return $result->fetch_object();
}
}

Answer
public function get_row($field, $value) {
    $db = Database::getInstance();
    $mysqli = $db->getConnection();

    $sql_query  = "SELECT * ";
    $sql_query .= "FROM `".static::$table_name."`";
    $sql_query .= "WHERE {$field}=? ";
    $sql_query .= "LIMIT 1";

    if ($stmt = $mysqli->prepare($sql_query)) {
        $valueType = gettype($value);

        if ($valueType == 'string') {
            $stmt->bind_param('s', $value); 
        } else if ($valueType == 'integer') {
            $stmt->bind_param('i', $value); 
        } else if ($valueType == 'double') {
            $stmt->bind_param('d', $value); 
        } else {
            $stmt->bind_param('b', $value); 
        }

        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_object();
    } else {
        $error = "Errormessage: " . $stmt->error;
        return $error;
    }           
}

The issue with the original code was the fact that I was using bind_result($row), where the row that i was pulling from in the DB has multiple values that would need to be bound. As $result = $stmt->get_result and then returned a fetch_object();.

Comments