PDO Query with Parameter Binding in PHP 7 - Part 1

When working with PDO, we can make requests to our Database. For this, we provide SQL scripts (queries) to PHP. In this case, we can use two types of queries:

1. A “static” query, meaning that it does not expect any parameters. For example: SELECT * FROM persons. Note that we do not provide any parameters for this query, just the query itself

2. A query that expects parameters. Example: SELECT * FROM persons WHERE name = 'Tim'. In this second case, we provide a value to be found in our table. In other words, we provide a parameter

It is possible to create these two types of queries when using PDO, but in this article we will focus on the second type. To provide parameters for a connection in PDO, coincidentally we also have two ways:

1 - Named parameters

2 - Positional parameters

Named parameters

For the examples in this article, we will create a MySQL Database that will have a table called table_users, which will have three columns. Here is the script for its creation:

CREATE TABLE table_users (
  idUser INT NOT NULL AUTO_INCREMENT,
  nameUser VARCHAR(256) NOT NULL,
  emailUser VARCHAR(256) NOT NULL,
  CONSTRAINT PRIMARY KEY(idUser)
);

We will add information to this table using PDO and named parameters. Ready? Then, let’s go!

First, we need to create a connection with PDO:

Next, it is necessary to create a statement, meaning the declaration of an SQL query, that will allow us to perform some query or change in the Database. In our case, we will perform an INSERT script

In fact, we teach how to develop a complete CRUD using PDO in our Complete PHP 7 Course, which you can access at this link: https://hcode.com.br/cursos/PHP7

To provide named parameters, we need to define a colon (:) and an identifier for the parameter

In red we see the parameter identifiers, which could have any other name we desired

Now, to define a value for the parameters, we need to use the bindParam() method, which will be called by our statement. The first argument of this method will be the name of the parameter in PDO, and the second will be the value of the parameter:

Note that we created variables a little above with the values of the parameters

Finally, just call the execute() method on the statement, which will actually execute the query in the Database:

Let’s check our Database now:

Excellent! The information has been added to MySQL without any errors. Did you like our example? This is how we can provide named parameters for PDO

But, what about positional parameters? How are they used and what is the difference between them and named parameters? We will talk about that in the next article: PDO Query with Parameter Binding in PHP 7 - Part 2 , until then :)