prepare statements with PDO

  • Avinash
  • 0
  • Aug 30, 2010
  • Tutorial

You should first read below article before continue with this article.

  1. PDO Introduction
  2. Mysql Connection PDO

Many of the matured databases support the use of prepared statements. Prepares statements can be considered as a kind of compiled template for the SQL that as application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

  • The query needs to be parsed (or prepared) once but can be executed multiple times with same or different values. When query is prepared database analyzes, compile and optimized its plan for executing the query. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements will use fewer resources and so runs faster.
  • The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application uses only prepared statements then developer can be sure that no SQL injection will occur.

For replacing different values in prepare statement we can use place holders in query. Placeholders can be of two types:

  • Named placeholder
  • Positional placeholder

Let’s try with some examples:
Multiple inserts with prepared statements and named placeholder.

prepare('insert into info (name, value) values (:name, :value)'); // Below two lines will bind the php variable to the prepared statements. $query->bindParam(':name',$name); $query->bindParam(':value',$value); // Insert 1 $name='name1'; $value='value1'; $query->execute(); // Insert 2 $name='name2'; $value='value2'; $query->execute(); ?>
<?php
  $query=$dbh->prepare('insert into info (name, value) values (:name, :value)');
  // Below two lines will bind the php variable to the prepared statements.
  $query->bindParam(':name',$name);
  $query->bindParam(':value',$value);
  // Insert 1
  $name='name1'; $value='value1';
  $query->execute();
  // Insert 2
  $name='name2'; $value='value2';
  $query->execute();
?>

Multiple Inserts with prepared statements and positional ? place holder

prepare('insert into info (name, value) values (?, ?)'); // Below two lines will bind the php variable to the prepared statements. $query->bindParam(1,$name); $query->bindParam(2,$value); // Insert 1 $name='name1'; $value='value1'; $query->execute(); // Insert 2 $name='name2'; $value='value2'; $query->execute(); ?>
<?php
  $query=$dbh->prepare('insert into info (name, value) values (?, ?)');
  // Below two lines will bind the php variable to the prepared statements.
  $query->bindParam(1,$name);
  $query->bindParam(2,$value);
  // Insert 1
  $name='name1'; $value='value1';
  $query->execute();
  // Insert 2
  $name='name2'; $value='value2';
  $query->execute();
?>

Source : php.net

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: