Close

December 26, 2013

A Simple PHP Class For Prepared Statements in MySQLi

PHP Prepared Statements Class

Prepared statements are all the rage right now in PHP development… and for good reason. Not only do prepared statements make your queries more secure… they also help future-proof your code by relying more heavily on PHP itself for that security.

If you’re not using prepared statements in your queries, you really should be. Here’s a simple class that helps you do just that using MySQLi:

I recommend walking through this code and unraveling how it all comes together. There are a few gotchas when using prepared statements in a dynamic way like this.

Or you could just check out my course PHP & MySQL 101 where I walk you step-by-step through building this exact class (and a lot more).

12 Comments on “A Simple PHP Class For Prepared Statements in MySQLi

Randy
April 10, 2014 at 11:51 am

Question – I’m learning PHP and I just don’t quite get callback functions. In the code, why can’t you just use $stmt->bind_param()?

Reply
this guy
April 10, 2014 at 12:56 pm

Junk, poor documentation, poor implementation.

Reply
Eric Shoberg
December 7, 2014 at 10:49 am

Its actually a pretty good start for someone trying to understand creation of such classes. There’s a thing or two I would change but altogether not a terrible implementation. On insert though, trying to insert a row with a duplicated unique value will throw an error and it would be uncaught in that method. “$stmt->affected_rows” will return true even if the execution returns a duplication error $stmt->affected_rows == [-1].

Reply
Roy
May 3, 2014 at 11:36 pm

Hi John,

Thank you so much for that, this is truly amazing. I can’t begin to tell your how much this helps me. Is there any way to possibly view your “PHP & MySQL 101” course? I’m straggling to learn PHP and MySQL and it would help me so much. Thank you.

Reply
Damien Delbergue
May 24, 2014 at 5:40 am

Thanks a lot!

Reply
Yorick van Kruining
August 11, 2014 at 2:57 pm

Hi John, I was wondering if there was any way to view your “PHP & MySQL 101” course or if that’s really not an option. Could you please explain what the input for the insert function is ?
Thanks alot!

Reply
John Morris
August 11, 2014 at 3:30 pm

It’s not available right now. Not sure what you mean in your question. Could you explain?

Reply
Alan D.
December 16, 2014 at 3:03 pm

Does that work for complex queries like this one:

SELECT `TABLE_A`.`COLUMN_A`,
`TABLE_B`.`COLUMN_B`
FROM `TABLE_A`
LEFT JOIN `TABLE_B`
ON `TABLE_A`.`TABLE_A_COLUMN_ID` = `TABLE_B`.`TABLE_A_COLUMN_ID`
WHERE (
`TABLE_A`.`COLUMN_A` IN (SELECT `TABLE_C`.`COLUMN_C` FROM `TABLE_C` WHERE `TABLE_C`.`COLUMN_C` = $PHPVAR1)
AND
TABLE_A`.`COLUMN_B` BETWEEN $PHPVAR2 AND $PHPVAR3
)
OR
(`TABLE_B`.`COLUMN_C` > $PHPVAR4 AND `TABLE_B`.`COLUMN_D` > DATEADD(dd, $PHPVAR5, GETDATE()))
GROUP BY `TABLE_A`.`COLUMN_A`
ORDER BY $PHPVAR6
LIMIT $PHPVAR7, $PHPVAR8

Reply
Muhammad Kashif
February 5, 2015 at 3:32 am

Please help me with the insert function of this class , I am facing the following issue.

Warning: call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object on

call_user_func_array( array( $stmt, ‘bind_param’), $this->ref_values($values));

while printing the sql and values i get

INSERT INTO members (0,1,2,3,4) VALUES (?,?,?,?,?)Array
(
[0] => sssss
[1] => ATC Mart
[2] => kashifs@xomnett.com
[3] => atcmart
[4] => 3334819634
[5] => Self
)

What is wrong ? I will be grateful for the quick reply.

Reply
Francesco Gadaleta
February 9, 2015 at 8:00 am

update is not working

Reply
pricereduc
January 7, 2018 at 9:23 am

Thanks for this article, I would like to build a class like this but it’s still hard for me, anyway it is very useful to avoid much code when we want to update, select, insert,delete using MySQLi thanks to this class and its methods

Reply
John Morris
January 11, 2018 at 11:22 am

No problem!

Reply

Leave a Reply

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