MySQLi bind_param() error

Question

I'm trying to make a function that receive a query (sql) and a parameter (array) but I receive this error:

PHP Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given

My code is:

function dbRead($query, $param) {
    $mysqli = new mysqli(DB::READ_HOST, DB::READ_USER, DB::READ_PASS, DB::NAME);
    // Check that connection was successful.
    if ($mysqli->connect_error) {
        $result = "Connection error";
    } else {
        // Check that $conn creation succeeded
        if ($conn = $mysqli->prepare($query)) {
            call_user_func_array(array($conn, 'bind_param'), $param);  
            $conn->execute();
            $result = $conn->get_result();
            $result = $result->fetch_array();
            $conn->close();
        } else {
            $result = "Prepare failed";
        }
    }
    $mysqli->close();
    return $result;
}

$test = dbRead('SELECT * FROM user WHERE id=? and email=?', array(123,'example@example.com'))

And if my function code is

function dbRead($query, $param) {
    $mysqli = new mysqli(DB::READ_HOST, DB::READ_USER, DB::READ_PASS, DB::NAME);
    // Check that connection was successful.
    if ($mysqli->connect_error) {
        $result = "Connection error";
    } else {
        // Check that $conn creation succeeded
        if ($conn = $mysqli->prepare($query)) {
            $ref = array();
            foreach ($param as $key => $value) {
                $ref[$key] = &$param[$key];
            }
            call_user_func_array(array($conn, 'bind_param'), $ref);  
            $conn->execute();
            $result = $conn->get_result();
            $result = $result->fetch_array();
            $conn->close();
        } else {
            $result = "Prepare failed";
        }
    }
    $mysqli->close();
    return $result;
}

I receive this error

PHP Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

My PHP version is 5.4.36

Ok. Now, I don't know why you're using `$conn` when you already declared your connection as `$mysqli = new mysqli` that could be what's causing the main problem. — Funk Forty Niner, Mar 24 '15 at 15:34
@Fred-ii- If I use directly `$mysqli` i receive this error `call_user_func_array() expects parameter 1 to be a valid callback, class 'mysqli' does not have a method 'bind_param'` — , Mar 24 '15 at 15:39

score -1 · Answer 1 · edited May 05 '15 at 03:22

I was trying to do something very similar and pieced together the solution from a few different posts on PHP References and bind_param. What's probably not immediately clear from the bind_param examples (or you forgot) is that the first argument is a string of the parameter types, one character per parameter (in your case, likely "is" for int and string), and you already got that the rest of the arguments must be references in your second function definition.

So, creating the arguments array should be something like this instead:

$ref = array("is");
foreach ($param as $value)
   $ref[count($ref)] = &$value;

Though there are many ways to do it... and you should probably pass in the argument types along with the query, but MySQL seems to be relaxed when it comes to type exact types. I also prefer to pass the connection around, and support multiple result rows, e.g.:

function doRead($conn, $query, $argTypes, $args){
   $success = false;
   $execParams = array($argTypes);
   foreach($args as $a)
      $execParams[count($execParams)] = &$a;

   if (!$stmt = $conn->prepare($query)){
      echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
   }else if (!call_user_func_array(array($stmt, "bind_param"), $execParams)){
      echo "Param Bind failed, [" . implode(",", $args) . "]:" . $argTypes . " (" . $stmt->errno . ") " . $stmt->error;
   } else if (!$stmt->execute()) {
      echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
   } else
      $success = true;

   $ret = array();
   if($success){
      $res = $stmt->get_result();
      while ($row = $res->fetch_array(MYSQLI_ASSOC))
         array_push($ret, $row);
   }
   $stmt->close();
   return $ret;
}

MySQLi bind_param() error

1 Answers1