PHP – PDO dynamic parameter binding

If you’re not using large frameworks and rely on your own skills and expertise to get the job done, then you’ve probably written many queries and used the same database handler to execute your query, while always making sure your data is properly escaped before saving into the database.

While writing custom functions, I found myself escaping data manually every time I have to insert new data into the database. It was hectic. After writing functions to clean the data, it still required a redundant amount of work to apply it against all user input.

I ended up writing two small functions which allow me to pass the parameters I need for the query and dynamically bind each parameter to the query. Dynamic binding became very useful when you’re dealing with forms and lots of input fields to process.

Here’s the example:

Explanation: I manually throw PHP exceptions because if I know ahead of time that the function will break while someone else uses it incorrectly, then I don’t want the system to work at all and the code should be fixed immediately. Continuing, we use the “prepare()” function to set everything up, following by a “foreach” loop to cycle through the $params array and bind $key to $value to the previously created statement ($stm). Note, $value is passed by a reference. Lastly, we execute the query and return the count of affected rows. That way in your model you can check if the SQL statement you created has executed properly.

This function would reside in your database file (or class) which contains a database handler. The usage part would ideally be another function which processes a request, usually from a model, but it all depends how you structure your code.

The function can be customized in any way you want. Let me know if this was helpful or you have a better approach to automating the process of dynamically binding parameters while using PDO.

 

Leave a Reply

Your email address will not be published. Required fields are marked *