I’ve been trying to code a login form in PHP using a prepared statement but every time I try to log in I get the following error:
mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement
Here is my code:
<?php
$mysqli = new mysqli("localhost", "root" , "" , "security");
$stmt = $mysqli->prepare("SELECT username AND password FROM users WHERE username = ?");
$username = $_POST['name'];
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->bind_result($password, $username);
$stmt->fetch();
Can someone tell me why this is happening?
$mysqli->prepare("SELECT username, password FROM users WHERE username = ?");
$username = $_POST['name'];
$stmt->bind_param('s' ,$username);
$stmt->execute();
$stmt->bind_result($username, $password);
Your select syntax was wrong, the correct syntax is
SELECT field1, field2, field3 FROM TABLE WHERE field1 = ? AND field2 = ?
To select more fields simply separate them by a comma and not an AND
The error message clearly states that the number of columns you are SELECT
ing does not match the number of variables you provided to mysqli_stmt::bind_result()
. They need to match exactly.
For example:
-- ↓ 1 ↓ 2 ↓ 3
SELECT col1, col2, col3 FROM tableA
There are 3 columns being fetched, so you need to provide 3 variables.
$stmt->bind_result($var1, $var2, $var3);
There could be a number of reasons why the column count doesn't match variable count.
The simplest cause is that you made a mistake in the count. Do a recount of both. Maybe you changed the SQL but forgot to adjust bind_result()
?
Using SELECT *
is not recommended with bind_result()
. The number of columns in the table could change as a result of schema changes or joins and will break your application. Always list all the columns explicitly!
The code from the question contains a logical mistake. SELECT username AND password
produces a single column in the result. The AND
keyword evaluates to a boolean expression. To select multiple columns you must use ,
. Maybe there is another logical error in the query that causes the SQL to produce a different number of columns than you expected?
DML statements such as INSERT
, UPDATE
and DELETE
do not produce result sets. You can't bind variables to such prepared statement. You need to execute another SELECT statement to fetch the data.
The return value of mysqli_stmt::bind_result()
is not an array, it's just a boolean. If you expected this function to return an array, then you are probably looking for get_result()
with fetch_all()
instead.
To select an array you need to get the mysqli_result
object first.
$stmt = $mysqli->prepare("SELECT username AND password FROM users WHERE username = ?");
$stmt->bind_param('s', $username);
$stmt->execute();
$mysqli_result = $stmt->get_result();
// The object can then be iterated or used with fetch_* methods
foreach($mysqli_result as $row) {
}
// or
$arrayRow = $mysqli_result->fetch_assoc();
If this function doesn't exist in your PHP installation, then it means you have PHP not installed properly. You need to either recompile it, or enable mysqlnd (e.g. in cPanel).
If you are only learning PHP, it would be much easier for you to learn PDO instead.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With