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:

https://gist.github.com/8135167

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

Do you want more freelance clients?

I’ll show you what I learned over the last 15 years to grind out (from absolute scratch) a backlog of new clients wanting to hire you. Who your best client prospect are, what services you should be offering them, where to find them and more. Just enter your email address in the box below and let’s get started:

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on pinterest
Pinterest
John Morris

JOHN MORRIS

I’m a 15-year veteran of freelance web development. I’ve worked with bestselling authors and average Joe’s next door. These days, I focus on helping other freelancers build their freelance business and their lifestyles.

You might also like

This Post Has 19 Comments

  1. 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()?

  2. Junk, poor documentation, poor implementation.

    1. 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].

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

  4. 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!

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

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

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

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

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

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

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

  9. 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?

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

  11. Hello !
    Thanks for this code. It’s awesome for me.
    Can you share your new code please ?

Leave a Reply

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

Do you want more freelance clients?

Enter your email below to get started building your system for consistently bringing in new freelance clients:

WHAT OTHERS ARE SAYING

Daniel Mohlendick

On the Freelancing on Upwork course: “This is by far the best course i have watched on Skillshare!! Thank you so much.”

Lewis Howes

John is amazing at building membership sites. He converted one of my sites over from it’s existing (hardly working) platform over to the clean and simple to use WishList membership platform. I highly recommend using John and WishList for any of your membership site needs.

Thabo Motsoahae

John is one of the best instructors I have come across, I learned a lot from his online tutorials.

Ray Edwards

I recommend John every chance I get. If every person I worked with were as committed to excellence, punctuality, value, and unquestionable integrity… the world would be a better place. Highest recommendation.

Sukh Plaha

John is a fantastic and patient tutor, who is not just able to share knowledge and communicate it very effectively – but able to support one in applying it. However, I believe that John has a very rare ability to go further than just imparting knowledge and showing one how to apply it. He is able to innately provoke one’s curiosity when explaining and demonstrating concepts, to the extent that one can explore and unravel their own learning journey. Thanks very much John!

Bob Patterson

Not only is John a very talented programmer and developer, he is also an excellent communicator. He has a talent for taking complex subjects and communicating them in terms that anyone can understand. This is a rare combination. This ability has enabled me to take my skills and knowledge to the next level. Thank you John for for all that you do.

Close Menu