MySQL Prepared Statement

0 4736

Mysql database support prepared statements.

A prepared statement is used to execute the same statement repeatedly. We pass a parameters (labeled "?") in a prepared statement.

We create a parameter template using prepare statement & send to the database. It's also known as parameterized statement.

For e.g.: INSERT INTO tbl_student VALUES(?, ?)

Execute: when we write a prepare statement after execution its query joined the values to the parameters & the database executes the prepare statement. The query may execute the statement as many times as it wants with different values.

Step 1: Create a database

Database name: tutorial

Tbl_student(stu_name,stu_rollno)

Step 2: create a connection file. File name is "connection.php".

<?php
// $sname is server name

$sname = "localhost";

// $uname is user name

$uname = "root";

// $pwd is server password

$pwd = "";

$databasename = "tutorial";

// data base name are defined.
// Create connection

$db_conn = new mysqli($sname, $uname, $pwd, $databasename);

// Check connection

if ($db_conn->error)

{
die ("Connection failed" . $db_conn->error);
}
?>

Step 3: php page (stu_inf.php)

<?php  include("connection.php");
$qry = $db_conn->prepare("insert into tbl_student(stu_name,stu_rollno) VALUES (?,?)");

// bind the parameters

$qry->bind_param("ss", $stu_name, $stu_rollno);

// parameters defined and execute

$stu_name = "Ram";
$stu_rollno=1;
$qry->execute();

// defined second parameters and execute

$stu_name = "Raja";
$stu_rollno = 2;
$qry->execute();

echo "New records successfully created";
?>

Result:

Two records are insert into a table using prepared statement. In this way we insert many records using prepare.

Advantage:

The statement is executed several times; but using prepared statement we reduces parsing time because query of preparation is execute only one time. Using bind_param we bind the parameter which helps to reduce the bandwidth to the server.

SQL INJECTION doesn't occur using prepared statement.