PHP – Prepared Statements with a dynamic number of parameters

I had trouble getting Prepared Statements in PHP with MySQLi, so here’s my solution. There are two issues that I had with this: passing a dynamic number of parameters to be bound, and that I wanted an associated array to be returned.

   public function getPreparedStatement($query, $params) {
       $rows    = null;

       try {
            //Connect to Database
            $this->connect();

            // Create a prepared statement 
            $stmt = $this->link->prepare($query);
            if($stmt) {
                //Bind parameters - s - string, b - blob, i - int, etc                 
                call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($params));

                $stmt->execute();

                $rows = fetchAssocArray($stmt);

                $stmt->close();
            }//if

            //Close connection
            $this->close();
       } catch(Exception $e) {
           echo 'Message: ' . $e->getMessage();
       }//catch

       return $rows;
   }

I used call_user_func_array to call a callback on my array of of parameters that I passed in. One issue with this approach is that the array needs to have all of the values be references, thus I used the makeValuesReferenced function that I found here, while fetchAssocArray is a user created function that I found here.

The function can be called like this:

$query = "SELECT Title FROM adventureworks.employee WHERE Title = ? AND Gender = ?;";
$params = array("ss", "Marketing Assistant", "F");
$rows = $db->getPreparedStatement($query, $params);
This entry was posted in Database, MySQL, PHP. Bookmark the permalink.

1 Response to PHP – Prepared Statements with a dynamic number of parameters

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s