Dhwany Technology Pvt. Ltd
dhwany technology menu icon

Blogs

Prepared Statement and data binding in PHP Data Object (PDO)

Prepared Statement and data binding in PHP Data Object (PDO)

sachin     10 Apr 2018     PHP      NA     

In the previous blog, we discussed how to create the connection with multiple databases through PHP data object (PDO). Here I will use that connection variable $DTPDO to PDO::prepare() statement and its execution. If you have not read that blog, so you must read it. It will definitely help you.

PHP data object (PDO) advance series: create database connection 
 
A prepared statement is pre-compiled SQL statement. Through this feature, the same SQL statements can be re-executed over and over. Before continuing this blog. We must know how the prepared statement works?

I set a viable called $statement and pass a regular MySQL query into it but instead of value parameter, the named placeholder will be passed with the colon prefix. For example   

$statement = “INSERT INTO TABLENAME(name, address, phone) value(:name, :address, :phone)”;
$DTPDO->prepare($statement);

The question mark can be placed instead of named placeholder. The question mark parameter will be assigned as an order of the indexed position. For example, The first parameter will be bounded automatically with the name column, second with address and so on.

$statement = “INSERT INTO TABLENAME(name, address, phone) value(?, ?, ?)”;
$DTPDO->prepare($statement);

In this following code, only one parameter binds a single value at the time. Multiple values can’t be bounded by a single parameter. You cannot insert form value directly in MySQL query. You must bind the form value with MySQL parameter. We will discuss further that how to bind value with parameter?

But now, If the PDO::prepare() statement has been successfully prepared by the database server. So, it returns a PDOStatement object. Otherwise, it returns FALSE or emits PDOException, Which depends on error handling. Prepared statements don’t interact with the database server, so PDO::prepare() does not check that how the statement is working.

 

How can bind MySQL parameter with form variable?

 

PHP data object allow you to bind the value and parameter in deferent ways. PDOStatement::bindParam() and PDOStatement::bindValue() has to be called for binding variable.

PDOStatement::bindParam() is  bind the parameter to specific variable name.

PDOStatement::bindValue() is bind the variable as a reference and will only be  evaluated when execute the prepared statement with PDOStatement::execute().

I going to show you the complete code of execute the prepared statement with bind named parameter.

$statement = “INSERT INTO TABLENAME(name, address, phone) value(:name, :address, :phone)”;
$DTVariable = $DTPDO->prepare($statement);

# binds the value.
$DTVariable->bindValue(‘:name’, $name, PDO::PARAM_STR, 35); 
$DTVariable->bindParam(‘:phone’, $phone, PDO::PARAM_INT);

# executes the prepared statement.
$DTVariable->execute();

In this code, the first parameter is passed a named placeholder and the second one is that variable in which the value is stored. The third parameter is defined the data type of value. Actually, PDO has predefined Constants and if you want to know more about it. So, visit on http://php.net/manual/en/pdo.constants.php. You can specify the length of the value which stored in the variable as I defined length 35 on the fourth parameter.

On the other side, the question mark can be used instead of the named parameter. I show you the same in the example shown above. I use the indexed position of the question mark instead of named placeholder.

$statement = “INSERT INTO TABLENAME(name, address, phone) value(?, ?, ?)”;
$DTVariable = $DTPDO->prepare($statement);

# binds the value with question mark placeholder.
$DTVariable->bindValue(1, $name, PDO::PARAM_STR, 35); 
$DTVariable->bindParam(2, $phone, PDO::PARAM_INT);

# executes the prepared statement.
$DTVariable->execute();

Actually, PDO gives more options to bind value. An array is one of them, It’s like binding params, at the time of execution, it’s passed as a string. No matter the data type, you define or not. I think it’s an easiest way to bind value. Just pass the array to the PDOStatement::execute()

I’m going to show the example that how it works with the named placeholder.

$statement = “INSERT INTO TABLENAME(name, address, phone) value(:name, :address, :phone)”;
$DTVariable = $DTPDO->prepare($statement);

#With named placeholder
$DTVariable->execute([‘:name’=>’sachin’, ‘:address’=>’delhi’, ‘:phone’=>’99XXXXXX69’]);

#with question mark
$DTVariable->execute([’sachin’, ’delhi’, ’99XXXXXX69’]);

In the next blog, we will discuss how to fetch data through PDO. If you have any query in the prepared statement. Then comment on this blog. You can also connect with me on twitter or facebook. At the end of this series, I will show you how to work on the login system with using PHP data object.


comment icon
ASAD SHAFIQ 23 May 2018
nice help thnx sir

Leave Comments