Dynamic binding of params array MySQLi

Question

I'm building a search feature for my website where the user can search using 3 parameters. The user can choose to enter all 3 to filter the table or none at all and receive the whole table. I figured out how to dynamically build the query depending on what the user inputs but I'm having trouble calling bind_params() with the right number of arguments and the right order.

Code:

$sql = "SELECT position, rank, fullname, phonenumber, email, division 
FROM `table` WHERE 1=1 ";


if(!empty($_POST['fname'])){
   $firstname = $_POST['fname'];
   $sql .= " AND `fullname` LIKE '%?%'";
}


if($_POST['div'] !== "All"){
   $division = $_POST['div'];
   $sql .= " AND `division` LIKE '%?%'";
}


if(!empty($_POST['pos'])){
   $position = $_POST['pos'];
   $sql .= " AND `position` LIKE '%?%'";
}


$stmnt = $db->prepare($sql);
$stmnt -> bind_param('sss', $firstname, $division, $position); 
$stmnt -> bind_result($position, $rank, $fullname, $phonenumber, $email,$division);
$stmnt -> execute();

I always get this error: Number of variables doesn't match number of parameters in prepared statement I understand why I'm getting it but I tried multiple things but nothing worked out. Any hints or links to documentation would be useful. Thanks

It's worth noting this is easier to do with PDO since it has named placeholders and you can just pass in an array() structure to execute(). — tadman
– tadman, Commented Jul 6, 2016 at 17:39
Note: execute, then bind the result. php.net/manual/en/mysqli-stmt.bind-result.php which is probably the major fault here while making sure that "all" the conditions are met and that all POST arrays contain values. If one of those should fail, so would your query. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 6, 2016 at 17:48
There's a lot of places to go to learn about PDO, but here's one that goes through the basics. Adding things to an associative array is easy, and later you can execute() with those values. Doing this with mysqli requires low-level PHP hacking to pass in arbitrary arguments, it's a giant mess. — tadman
– tadman, Commented Jul 6, 2016 at 17:55
@Fred-ii- Thank you for the link. However, that isn't the issue here I'm making sure all the POST arrays contain values but I'm having trouble supplying the right arguments to bind_params. I'm looking at PDO now like @tadman suggested. — ybce
– ybce, Commented Jul 6, 2016 at 17:56

rhazen · Accepted Answer · 2016-07-07 13:04:09Z

You can pass your data and parameters as an array:

$data = array();
$format = array();

if(!empty($_POST['fname'])){
    $firstname = $_POST['fname'];
    $sql .= " AND `fullname` LIKE '%?%'";
    $format[] = 's';
    $data[] = $firstname;
}

Then pass them by reference and bind with call_user_func_array

if(!empty($format) && !empty($data)):

            $format = implode( '', $format );
            $format = str_replace( '%', '', $format );

            array_unshift( $data, $format );
            call_user_func_array( array( $stmnt , 'bind_param' ), (referenceValues( $data ) );

        endif;

The pass by reference function:

public function referenceValues($array)
    {
        $refs = array();

        foreach ($array as $key => $value):

            $refs[$key] = &$array[$key];

        endforeach;

        return $refs; 
    }

Thank you for your answer, I switched to PDO but I will accept your answer for future visitors.

Collectives™ on Stack Overflow

Dynamic binding of params array MySQLi

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related