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).

18 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
Franco
August 24, 2018 at 3:29 pm

Sorry but it´s not good idea to call a method each time you need to build a connection You solve this easly by using the propertie $this->

Reply
John Morris
August 25, 2018 at 8:03 am

Yeah, I’m aware. This is over 5 years old. All my new code does this different.

Reply
James
September 22, 2018 at 9:16 pm

Can you share that new code? Is it available somewhere on your site?

Reply
Paul Welding
August 25, 2018 at 7:45 am

Hi There,

I need help please.

I got everything working fine up until the binding ‘call_user_func_array()’, I get the following error:

Warning: call_user_func_array() expects parameter 1 to be a valid callback, class ‘PDOStatement’ does not have a method ‘bind_param’

Any idea what’s going on here?

Reply
John Morris
August 25, 2018 at 8:03 am

I replied to you over on YouTube.

Reply
sradha
September 16, 2018 at 1:46 am

Hi john

I am facing the following issue , can you please help me out .

$result_set = $dbObj->select(‘SELECT * FROM trading WHERE end_date >= ? AND end_date <= ?', array($start_date,$end_date), array('%s','%s'));

what is wrong with this query ?

I am getting this following error .

Notice: Undefined variable: results in C:\xampp\htdocs\trading_software\db.php on line 142

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.