PDO ManualPHP Tutorials

Prepared SQL statements using PDO with example – PHP PDO Manual 3

In this tutorial you will learn How to run Prepared SQL statements using PDO. Before starting you must create a database connection using PDO which is already discussed here. PDO is basically a PHP class or we can say that it is layer in which sql query run. You can easily create and run prepared sql query using PDO if you know how to run sql query using PDO. In this article you will learn how to create prepared SQL query using PDO with bind parameters in PDO.

Why we use prepared sql query or statements ?

Prepared SQL query or statement are very important part of a website. If you want to insert multiple rows using sql query then in mysql you have write same query multiple times. But in prepared statement a single query is enough to insert multiple rows in database table. With the help of prepared statements your code length will be decreased either by using loops or simply defining array.

How to use pdo prepared sql query or statement ?

Prepared statements for sql commands is really helpful for executing same sql statements multiple times. In mysql for inserting multiple rows you have copy and paste whole commands multiple times for saving data. But in mysqli and PDO a single sql command is used for executing multiple similar sql statements. Syntax for prepared statements in PDO is shown below :-

Single Query Prepared Statement

<?php
try {
   //Trying to connect database connection
   $my_connect = new PDO("mysql:host=<!--your mysql host-->;dbname=<!--your database name-->", <!--Database Username-->, <!--Database Password-->);
} catch(PDOException $e){
    //Error in connection will be here
	echo $e;
}

$myquery = $my_connect->prepare("INSERT INTO tablename (name) VALUES (:name) ");
$myquery->bindValue("name","devildoxx");
$myquery->execute();
?>

Multiple Query Prepared Statement

<?php
try {
   //Trying to connect database connection
   $my_connect = new PDO("mysql:host=<!--your mysql host-->;dbname=<!--your database name-->", <!--Database Username-->, <!--Database Password-->);
} catch(PDOException $e){
    //Error in connection will be here
	echo $e;
}

$myquery = $my_connect->prepare("INSERT INTO tablename (name) VALUES (:name) ");
$myquery->bindValue("name",$name);

$name = "Devildoxx";
$myquery->execute();

$name = "Anil";
$myquery->execute();

$name = "Rahul";
$myquery->execute();

$name = "Nikita";
$myquery->execute();
?>

In next part you will learn how to bind parameters in PDO prepared statement or sql query.

Why we use bind parameters in PDO prepare sql query or statement ?

Mysql injection is the main reason for using prepared pdo sql query or statements with bind parameters. By using pdo prepared sql query with bind parameters you prevent your website database from getting hacked. Most of the hackers use various tricks to get access to database. Some hackers do not hack for getting data, they just hack a website to take revenge or practise. Out of millions of websites available on internet, lot of websites get hack daily. More than 100000 websites are hack daily by hackers. To prevent this type of hacking we use PDO prepared statement or sql query with bind parameters to block this type of attack.

How to bind parameters in PDO using PHP ?

Syntax for PDO prepared statement or sql query with bind parameter

<?php
try {
   //Trying to connect database connection
   $my_connect = new PDO("mysql:host=<!--your mysql host-->;dbname=<!--your database name-->", <!--Database Username-->, <!--Database Password-->);
} catch(PDOException $e){
    //Error in connection will be here
	echo $e;
}
$name = "Devildoxx";
$myquery = $my_connect->prepare("INSERT INTO tablename (name) VALUES (:name) ");
$myquery->bindValue("name",$name);
$myquery->execute();
?>

In above syntax :name is a parameter to which we bind variable $name to get results from database. Using this method will prevent mysql injection for being get hack easily.

 

Tags
Show More

Anil Mehra

I am Anil Mehra, a passionate, workaholic and a Full Stack Tech Savvy Programmer with true north towards growth. I have worked on 256 live projects in MNC. I am expertise in the field of Programming, Server Management, SEO, Blogging and SMO...

Related Articles

2 Comments

  1. After creating your myquery object using prepare method, it is also possible to use bindParam method this way:
    myquery->bindParam(‘:name’, ‘value’, PDO::PARAM_STR);
    bindParam has on its 3rd parameter data_type (it works also with bindValue method) which will allow you to choose the type of data you want for your value such as PDO::PARAM_STR for strings or PDO::PARAM_INT for integers

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button
Close
Close