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).
This Post Has 19 Comments
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()?
Junk, poor documentation, poor implementation.
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].
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.
Thanks a lot!
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!
It’s not available right now. Not sure what you mean in your question. Could you explain?
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
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.
update is not working
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
No problem!
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->
Yeah, I’m aware. This is over 5 years old. All my new code does this different.
Can you share that new code? Is it available somewhere on your site?
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?
I replied to you over on YouTube.
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
Hello !
Thanks for this code. It’s awesome for me.
Can you share your new code please ?