MySql Prepared Statement

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.

mysql_prepared1

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.



Leave a Reply

avatar
  Subscribe  
Notify of