PHP PDO Prepared Statements - Coding Cyber

PHP PDO Prepared Statements

Intro to Prepared Statements : Binding Values

Prepared statements use placeholders for values that are coming from external sources such as an online form. You can bind values to placeholders using the bindParam or bindValue methods.

PDO has the option of using either named or anonymous parameters in prepared statements. We’ll begin by looking at named and anonymous parameters to bind values.

Alternatively, you can pass an array of values directly to the execute method. You will learn about them in the next steps.

Other Articles in Learn PHP PDO

Using a prepared statement for a single query involves two round trips to the database server. The first time to validate and optimize the SQL, the second to send the values to the placeholders.

1. Different Methods of Binding Input Values

You can bind values to placeholders with these 2 methods bindParam or bindValue methods

the bindParam method only works with a variable, you can’t pass it an expression such as a calculation or a string concatenated to a variable.

This is because the value is evaluated Until the statement is executed. In other words, you’re binding the variable rather than it’s value.

bindValue binds the value immediately, so the value must be known. with the BindValue method we can set a field value to NULL.

2. Using Named Placeholders/Parameters

Here in this step, you will learn about using named placeholders. Here I’m using insert sql query, you can use it with the update and delete queries also.

First of all, I’m passing named placeholders in SQL query. That is :firstname, :lastname, :email, :gender, :age. After that, you should prepare the statement to take the values from external sources. Here I’m passing the values from URL.

Then binding the values with named placeholders. Here I’m using both methods bindValue and bindParam.

And the final step is execute method to execute the SQL query.

Here is the code for prepared statements with named placeholders.

After running the above code, you will get the output as boolean true or false. It’s more useful output than exec method.

3. Using Anonymous Placeholders/Parameters

Anonymous Placeholders is the opposite of named placeholders. Here we won’t use names, just we will use question marks as placeholders and numbers for assigning values to placeholders.

Everything is same as above code, for placeholders I’m using question marks.

After preparing the statement, binding values then execute.

Here also output will be same, that is boolean true or false.

4. Named Array Placeholders/Parameters

Named Array Placeholders as the name suggests, we will use named placeholders to prepare statement.

After that, we will bind values with the array, and this array will be passed to execute method.

Here is the example code for Named Array Placeholders.

Here also output will be same, that is boolean true or false.

5. Anonymous Array Placeholders/Parameters

In anonymous array placeholders we will use question marks as placeholders then we will prepare the statement. After that, we will pass the values as an array to execute method.

Here is the example code for anonymous array placeholders.

Here also output will be same, that is boolean true or false.

6. Binding Output Values

A prepared statement can also bind the output values of a result set to named variables using the bind column method. This avoids the need to use an associative array, simplifying embedding the results in HTML

We will use this bindColumn method after execute method. I’m using a simple select query with the while loop to loop through the results.

Here is an example code to bindColumn to named variables.

After executing the code, you will see the full names of the records with id and email.

Vivek Vengala

Vivek Vengala is a Online Entrepreneur, Web Developer from Hyderabad India.