PDO Prepare and Execute query

PDO::prepare()Prepares a statement for execution and returns a statement object. Prepares an SQL statement to be executed by the PDOStatement::execute() method.

  1. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed.
  2. You cannot use both named and question mark parameter markers within the same SQL statement, pick one or the other parameter style.
  3. Use these parameters to bind any user-input, do not include the user-input directly in the query.
  4. You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute().
  5. You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

Example #1 Prepare an SQL statement with Question mark parameters

 <?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');


$name = 'w3clan';

$sql = "INSERT INTO table(`time`, `name`) VALUES(NOW(), ?)";

$stmt = $db->prepare($sql);

$stmt->execute(array($name));

?>

Example 1a ) Select Statement & Multiple Execute

 <?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');


$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?';

$stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$stmt->execute(array(150, 'red'));

$red = $stmt->fetchAll();

// Fetchin another value - We can keep on fetching new value with different value.

$stmt->execute(array(175, 'yellow'));

$yellow = $stmt->fetchAll();

?>

You can bind arguments into SQL functions or pass any function like now() :-

<?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');


$name = 'w3clan';

$password = 'badpass';

// MySQL has inbuilt `password` function to encrypt password, we will use same

$stmt = $db->prepare("INSERT INTO table(`last_login`, `hexvalue`, `password`) VALUES(now(), HEX(?), PASSWORD(?))");

$stmt->execute(array($name, $password));

Example #2 Prepare an SQL statement with named parameters

 <?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

$name = 'w3clan';

$sql = "INSERT INTO table(`time`, `name`) VALUES(NOW(), :name)";

$stmt = $db->prepare($sql);

$stmt->execute(array(':name' => $name));

?>

Example #2a Prepare named parameters "Select Statement & Multiple Execute"

 <?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');


$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';

$stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$stmt->execute(array(':calories' => 150, ':colour' => 'red'));

$red = $stmt->fetchAll();

// Fetchin another value - We can keep on fetching new value with different value.

$stmt->execute(array(':calories' => 175, ':colour' => 'yellow'));

$yellow = $stmt->fetchAll();

?>

You can bind arguments into SQL functions or pass any function like now() with named properties

<?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');


$name = 'w3clan';

$password = 'badpass';

// MySQL has inbuilt `password` function to encrypt password, we will use same

$stmt = $db->prepare("INSERT INTO table(`last_login`, `hexvalue`, `password`) VALUES(now(), HEX(:hexvalue), PASSWORD(:passwd))");

$stmt->execute(array(':hexvalue' => $name, ':passwd' => $password));

Loading ...

Related Results :

  1. PDO query()
  2. PDO Prepare and Execute query
Note :
  • Related Posts are generally User Blog posts.
  • or Other tutorials from other networks of w3clan.com.
  • Any registered user can create related posts based on search term tags.

About the Author