PHP

PHP Prepared Statements

PHP Prepared Statements used to avoid sql injections. In this tutorial I explains how to implement prepared statement in php.
Steps for Implement Prepared statement in PHP 

  1. Make a connection with the database server
  2. Initialize all prepared statements
  3. Initialize all query templates
  4. Prepare all statements
  5. Assign all bind parameters
  6. Execute
  7. Close the prepared statements
  8. Done

Database Connection(config.php)

<?php
$dbuser="root";
$dbpass="";
$host="localhost";
$dbname = "test";
$mysqli = new mysqli($host, $dbuser, $dbpass, $dbname);
?>

Structure of user table

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `contactno` int(11) NOT NULL,
  `addrss` longtext NOT NULL,
  `posting_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Now Create a HTML Form for Data Insertion(index.php)

<form name="stmt" method="post">
<table>
<tr>
<td>Name :</td>
<td><input type="text" name="name" required="required" /> </td>
</tr>
<tr>
<td>Email :</td>
<td><input type="email" name="email" required="required" /></td>
</tr>
<tr>
<td>Contact no. :</td>
<td><input type="text" name="contact" required="required" /></td>
</tr>
<tr>
<td>Address :</td>
<td><textarea name="addrss" cols="30" rows="4" required="required"></textarea></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value="Submit" /></td>
</tr>
</table>
</form>

Code For Insert  Data Into Database Using PHP Prepared Statement. Put this code on the top of the index.php page

<?php
include('config.php');
if(isset($_POST['submit']))
{
$name=$_POST['name'];
$email=$_POST['email'];
$contact=$_POST['contact'];
$addrss=$_POST['addrss'];
$ad="insert into user(name,email,contactno,addrss) values(?,?,?,?)";
$stmt= $mysqli->prepare($ad);
$stmt->bind_param(ssis,$name,$email,$contact,$addrss);
$stmt->execute();
$stmt->close();
echo "<script>alert('Data added Successfully');</script>" ;
}
?>

Store the query in a variable.
Prepares a statement returning a result set as aPrepared Statement.
We can use question marks (?) for values.
we can then call the execute(array()) method.

Binding Datatypes

bind_params is the array of the parameters you want to bind.
Types: s = string, i = integer, d = double, b = blob
execute()  :-Execute the prepared statement. We can use an array of values to replace the question mark parameters.
close() :- Close the prepared statements.
Here is the full code that we have written during this tutorial:

<?php
include('config.php');
if(isset($_POST['submit']))
{
$name=$_POST['name'];
$email=$_POST['email'];
$contact=$_POST['contact'];
$addrss=$_POST['addrss'];
$ad="insert into user(name,email,contactno,addrss) values(?,?,?,?)";
$stmt= $mysqli->prepare($ad);
$stmt->bind_param(ssis,$name,$email,$contact,$addrss);
$stmt->execute();
$stmt->close();
echo "<script>alert('Data added Successfully');</script>" ;
 }
?>
<html>
<title>Prepared statement</title>
<body>
<h2>Insert Data in the Database using PHP Prepared Statement</h2>
<form name="stmt" method="post">
<table>
<tr>
<td>Name :</td>
<td><input type="text" name="name" required="required" /> </td>
</tr>
<tr>
<td>Email :</td>
<td><input type="email" name="email" required="required" /></td>
</tr>
<tr>
<td>Contact no. :</td>
<td><input type="text" name="contact" required="required" /></td>
</tr>
<tr>
<td>Address :</td>
<td><textarea name="addrss" cols="30" rows="4" required="required"></textarea></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value="Submit" /></td>
</tr>
</table>
</form>
</body>
</html>

For run this code on localhost create a database with name test and import the sql file available inside the download package.
View Demo

Download Source Code(PHP Prepared Statements)
Size: 2 KB
Version: V 1.0

Recommended Tutorials for you


Related posts

PHP Operators

Anuj Kumar

PHP Echo / Print

Anuj Kumar

How to Fetch data from database Using OOPS In PHP

Anuj Kumar

Time Ago Script

Anuj Kumar

How to append a string in PHP

Anuj Kumar

Password Hashing in PHP

Anuj Kumar

Looping Statement In PHP

Anuj Kumar

Date And Time Formatting With PHP

Anuj Kumar

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.