Tag Archives: Object Oriented Programming MySQLi Prepared Statements

OOP MySQLi : How to use Prepared Statements

Hi there guys ! In the last tutorial we talked about How to connect to your database via MySQLi and today we are going to discuss a very important topic in learning how to handle the new MySQLi.If you didn’t read the first article on MySQLi, I suggest you check it out before.

Why use Prepared Statements?

  • Security – You won’t need to escape characters when using prepared statements
  • Perfomance – The statement is prepared only once, but you can execute it multiple times
  • Possibly Cleaner Code – As you won’t need to escape the variables, you are writing less and more organised code

Downsides of Prepared Statements

The first downside of using prepared statements that I can think of it that you can echo out the query so that you can easily check if you created the query correctly or if you have some errors and you want to debug it, you can easily output it and see what’s wrong.As with the Prepared statements, you can’t echo out the query.Another downside is that it can be confusing at start if you don’t have much background in programming but that shouldn’t scare you.I encourage you guys to learn how to use prepared statements.

How to use Prepared Statements

Alright, so you’ve decided to learn how to use Prepared Statements, that’s a really good thing and will definitely help you in your future projects.
$database – variable which contains the OOP database connection.

prepare()

Alright, so you are probably asking what we just did.. When we prepare the statement, instead of using WHERE id = {$id}like we would’ve used normally we replaced the variable with a question mark ( ? ).If you have more than one variable, for example you have WHERE email = {$email} AND id = {$id}.All you need to do is replace all the variables from the query with ? so we would have this query WHERE email = ? AND id = ?.

bind_param()

What does bind_param() do? Well, remember the ? (question mark) signs that we just used ? I guess you already figured it out what it does, but I’ll tell you anyway.It binds / assigns every question mark you used with the prepare() function in the beginning with a variable.

It requires 2 or more parameters.The first parameter will always be a string which contains the type of variable you are sending to the database.I know it sounds weird but let me give you and example, examples are good.Lets say we have 2 variables that we want to assign, $email and $id.The email variable is a string so we will use the s character and for the id variable, which is an integer we’ll use the i character.Check out this example.

The s represents the type of the $email variable and the i represents the type of the $id variable.

I hope you learned something today, thanks for reading my tutorial.Stay tune for the part 2 where we learn to execute the statement and if needed get some results from the database.