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

Join 7,700 Other Freelancers Who've Built Thriving Freelance Businesses

Over 7,700 other freelancers have started thriving freelance businesses using the information on this blog. Are you next? Subscribe below to get notified whenever a new article is posted and create your own success story:

You might also like

Facebook
Twitter
LinkedIn
Reddit
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.

This Post Has 19 Comments

  1. Randy

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

    Junk, poor documentation, poor implementation.

    1. Eric Shoberg

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

    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. Yorick van Kruining

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

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

  5. Alan D.

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

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

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

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

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

      1. James

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

  9. Paul Welding

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

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

    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.

Join 7,700 Other Freelancers Who've Built Thriving Freelance Businesses

Over 7,700 other freelancers have started thriving freelance businesses using the information on this blog. Are you next? Subscribe below to get notified whenever a new article is posted and create your own success story:

Success Stories

Ready to add your name here?

Tim Covello

Tim Covello

75 SEO and website clients now. My income went from sub zero to over 6K just last month. Tracking 10K for next month. Seriously, you changed my life.

Michael Phoenix

Michael Phoenix

By the way, just hit 95K for the year. I can’t thank you enough for everything you’ve taught me. You’ve changed my life. Thank you!

Stephanie Korski

Stephanie Korski

I started this 3 days ago, following John’s suggestions, and I gained the Upwork Rising Talent badge in less than 2 days. I have a call with my first potential client tomorrow. Thanks, John!

Jithin Veedu

Jithin Veedu

John is the man! I followed his steps and I am flooded with interviews in a week. I got into two Talent clouds. The very next day, I got an invitation from the talent specialists from Upwork and a lot more. I wanna shout out, he is the best in this. Thanks John for helping me out!

Divyendra Singh Jadoun

Divyendra Singh Jadoun

After viewing John’s course, I made an Upwork account and it got approved the same day. Amazingly, I got my first job the very same day, I couldn’t believe it, I thought maybe I got it by coincidence. Anyways I completed the job and received my first earnings. Then, after two days, I got another job and within a week I got 3 jobs and completed them successfully. All the things he says seem to be minute but have a very great impact on your freelancing career.

Sarah Mui

Sarah Mui

I’ve been in an existential crisis for the last week about what the heck I’m doing as a business owner. Even though I’ve been a business for about a year, I’m constantly trying to think of how to prune and refine services. This was very personable and enjoyable to watch. Usually, business courses like this are dry and hard to get through…. repeating the same things over and over again. This was a breath of fresh air. THANK YOU.

Waqas Abdul Majeed

Waqas Abdul Majeed

I’ve definitely learnt so much in 2.5 hours than I’d learn watching different videos online on Youtube and reading tons of articles on the web. John has a natural way of teaching, where he is passionately diving in the topics and he makes it very easy to grasp — someone who wants you to really start running your business well by learning about the right tools and implement them in your online business. I will definitely share with many of the people I know who have been struggling for so long, I did find my answers and I’m sure will do too.

Scott Plude

Scott Plude

I have been following John Morris for several years now. His instruction ranges from beginner to advanced, to CEO-level guidance. I have referred friends and clients to John, and have encouraged my own daughter to pay attention to what he says. All of his teachings create wealth for me (and happiness for my clients!) I can’t speak highly enough about John, his name is well known in my home.

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!

Mohamed Misrab

Misrab Mohamed

John has been the most important person in my freelance career ever since I started. Without him, I would have taken 10 or 20 years more to reach the position I am at now (Level 2 seller on Fiverr and Top Rated on Upwork).

Join 7,700 Other Freelancers Who've Built Thriving Freelance Businesses

Over 7,700 other freelancers have started thriving freelance businesses using the information on this blog. Are you next? Subscribe below to get notified whenever a new article is posted and create your own success story: