Fatal error: Call to a member function bind_param(

问题:

This question already has an answer here:

• mysqli_fetch_assoc() expects parameter / Call to a member function bind_param() errors. How to get the actual mysql error and fix it? 1 answer

I'm making a login script which fetches data from two tables. I understand that this error occurs when the statement returns FALSE AKA a boolean, but why is it returning false??? I made a function which works up to a point

    function loginall($username, $password)
{
    $db_host="localhost";
    $db_username="root";
    $db_password="";
    $db_name="name";
    $con=mysqli_connect($db_host, $db_username,$db_password, $db_name);
    $mysqli = new mysqli("$db_host","$db_username","$db_password", "$db_name");

    $qry = "SELECT username, password, level, active FROM businesses  WHERE username=? AND password=? 
    UNION SELECT username, password, level, active FROM employees WHERE username=? AND password=?";
    $stmt = $mysqli->prepare($qry);
    $stmt->bind_param("ssss", $u,$p,$uu,$pp);
    $u = $username;
    $p = $password;
    $uu = $username;
    $pp = $password;
    $stmt->execute();
    $result = $stmt->get_result();
    while($row = $result->fetch_array(MYSQLI_ASSOC))
    {
        return $row;
    } 
}

it works great until I try fetching more columns from the tables, or even trying to SELECT * from the tables. I read through other similar questions and found codes to get the error to appear, but no luck. Thank you!

回答1:

Your function will end/return as soon as it hits the first return statement in the loop (first iteration). You will need to build the complete array and then return it once.

This ought to do it:

if(!($stmt=$mysqli->prepare($qry))){
    return ["Prepare failed: ".mysqli_error($mysqli)];  // what does this say?
}elseif(!$stmt->bind_param("ssss",$u,$p,$uu,$pp)){
    return ["Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error];
}else{
    $u = $username;
    $p = $password;
    $uu = $username;
    $pp = $password;
    if (!$stmt->execute()){
        return ["Execute failed: (" . $stmt->errno . ") " . $stmt->error];
    }else{
        $result = $stmt->get_result();
        while($row = $result->fetch_array(MYSQLI_ASSOC)){
            $rows[]=$row;
        }
        return $rows;
    }
}

Try backticking all of your column names. LEVEL is a MySQL KEYWORD.

回答2:

Try this maybe bind_result() not get_result():

You might be wondering, why even use bind_result()?

This is strictly due to preference, as the syntax is considered to be more readable.

However, it should be noted that bind_result() may not be used with the * wildcard selector. It must contain explicit values

Here in this code using bind_result(), the values $usernameRow, $passwordRow, .... are form the database tebles:

.....
...
.
$stmt->bind_param("ssss", $username, $password, $username, $password);
$stmt->execute();
$stmt->store_result();
$numRows = $stmt->num_rows;
$stmt->bind_result($usernameRow, $passwordRow, $levelRow, $activeRow); 
if($numRows > 0) {
  while ($stmt->fetch()) {
    $u[] = $usernameRow;
    $p[] = $passwordRow;
    $uu[] = $levelRow;
    $pp[] = $activeRow;
  }
}
$stmt->close();