Read, Edit ,Delete and Update data using PHP Prepared Statement

by Anuj Kumar

In this tutorial I will explain how to read, edit and delete data from database using PHP insert data in database using PHP Prepared Statement.

View Demo

Structure for 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;

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;

Database Connection File(Config.php). Include this file in every page.

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

<?php

$dbuser="root";

$dbpass="";

$host="localhost";

$dbname = "stmt";

$mysqli = new mysqli($host, $dbuser, $dbpass, $dbname);

Code for read data from database using PHP Prepared Statement(index.php)

<html>
<title>Prepared Statement</title>
<body>
<h3>Fetch, Edit and Delete Data from Database using PHP Preopared Statement</h3>
<table border="1">
<tr>
<td><b>Sn.</b></td>
<td><b>Name</b></td>
<td><b>Contact no.</b></td>
<td><b>Email-id :</b></td>
<td><b>Adress</b></td>
<td><b>Reg Date</b></td>
<td><b>Action</b></td>
</tr>
<?php //code for read data from Database
	$ret = "select * from user";
	$stmt2 = $mysqli->prepare($ret);
	$stmt2->execute();
	 $res=$stmt2->get_result();
	 $cnt=1;
	   while($row=$res->fetch_object())
	  {
?>
<tr>
<td><?php echo $cnt;?></td>
<td><?php echo $row->name;?></td>
<td><?php echo $row->email;?></td>
<td><?php echo $row->contactno;?></td>
<td><?php echo $row->addrss;?></td>
<td><?php echo $row->posting_date;?></td>
<td><a href="edit.php?id=<?php echo $row->id;?>">Edit</a> |<a href="index.php?del=<?php echo $row->id;?>"> Delete</a></td>
</tr>
<?php $cnt=$cnt+1; } ?>
</table>
</body>
</html>

<html>

<title>Prepared Statement</title>

<body>

<h3>Fetch, Edit and Delete Data from Database using PHP Preopared Statement</h3>

<tr>

<td><b>Contact no.</b></td>

<td><b>Email-id :</b></td>

<td><b>Adress</b></td>

<td><b>Action</b></td>

</tr>

<?php //code for read data from Database

$ret = "select * from user";

$stmt2 = $mysqli->prepare($ret);

$stmt2->execute();

$res=$stmt2->get_result();

$cnt=1;

while($row=$res->fetch_object())

{

<tr>

<td><?php echo $row->email;?></td>

<td><?php echo $row->contactno;?></td>

<td><?php echo $row->addrss;?></td>

<td><?php echo $row->posting_date;?></td>

<td><a href="edit.php?id=<?php echo $row->id;?>">Edit</a> |<a href="index.php?del=<?php echo $row->id;?>"> Delete</a></td>

</tr>

<?php $cnt=$cnt+1; } ?>

</table>

</body>

</html>

Code for Delete a Record from Database

if(isset($_GET['del']))
{
	$id=intval($_GET['del']);
	$adn="delete from user where id=?";
	$stmt= $mysqli->prepare($adn);
	$stmt->bind_param(i,$id);
	$rs=$stmt->execute();
	if(rs==true)
	{
	echo "<script>alert('User has been successfully Deleted');</script>";
	header('location:index.php');
	}
}

if(isset($_GET['del']))

{

$id=intval($_GET['del']);

$adn="delete from user where id=?";

$stmt= $mysqli->prepare($adn);

$stmt->bind_param(i,$id);

$rs=$stmt->execute();

if(rs==true)

{

echo "<script>alert('User has been successfully Deleted');</script>";

header('location:index.php');

}

Put this code at the top of the index.php
Code for edit the data(edit.php). For editing the code first we have to create a html form. After creating HTML form fetch the data from database inside form fields(previous data).

<html>
<title>Prepared statement</title>
<body>
<h2>Edit Data using PHP Prepared Statement</h2>
<?php
$id=$_GET['id'];
$ret = "select * from user where id=?";
$stmt2 = $mysqli->prepare($ret);
$stmt2->bind_param('i',$id);
$stmt2->execute();
$res=$stmt2->get_result();
$cnt=1;
while($row=$res->fetch_object())
{
?>
<tr>
<form name="stmt" method="post">
<table>
<tr>
<td>Name :</td>
<td><input type="text" name="name" value="<?php echo $row->name;?>" required="required" /> </td>
</tr>
<tr>
<td>Email :</td>
<td><input type="email" name="email" value="<?php echo $row->email;?>" required="required" /></td>
</tr>
<tr>
<td>Contact no. :</td>
<td><input type="text" name="contact" value="<?php echo $row->contactno; ?>" required="required" /></td>
</tr>
<tr>
<td>Address :</td>
<td><textarea name="addrss" cols="30" rows="4" required="required"><?php echo $row->addrss; ?></textarea></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="update" value="Submit" /></td>
</tr>
</table>
</form>
<?php } ?>
</body>
</html>

<html>

<title>Prepared statement</title>

<body>

<h2>Edit Data using PHP Prepared Statement</h2>

<?php

$id=$_GET['id'];

$ret = "select * from user where id=?";

$stmt2 = $mysqli->prepare($ret);

$stmt2->bind_param('i',$id);

$stmt2->execute();

$res=$stmt2->get_result();

$cnt=1;

while($row=$res->fetch_object())

{

<tr>

<table>

<tr>

<td><input type="text" name="name" value="<?php echo $row->name;?>" required="required" /> </td>

</tr>

<tr>

<td>Email :</td>

<td><input type="email" name="email" value="<?php echo $row->email;?>" required="required" /></td>

</tr>

<tr>

<td>Contact no. :</td>

<td><input type="text" name="contact" value="<?php echo $row->contactno; ?>" required="required" /></td>

</tr>

<tr>

<td>Address :</td>

<td><textarea name="addrss" cols="30" rows="4" required="required"><?php echo $row->addrss; ?></textarea></td>

</tr>

<tr>

</tr>

</table>

</form>

<?php } ?>

</body>

</html>

Code for edit the Data. Put this code at the top of the edit.php

if(isset($_POST['update']))
{
$name=$_POST['name'];
$email=$_POST['email'];
$contact=$_POST['contact'];
$addrss=$_POST['addrss'];
$uid=$_GET['id'];
$ad="update user set name=?,email=?,contactno=?,addrss=? where id=?";
$stmt= $mysqli->prepare($ad);
$stmt->bind_param('ssisi',$name,$email,$contact,$addrss,$uid);
$stmt->execute();
// $newId = $stmtins->insert_id;
$stmt->close();
echo "<script>alert('Data updated Successfully');</script>" ;
}

if(isset($_POST['update']))

{

$name=$_POST['name'];

$email=$_POST['email'];

$contact=$_POST['contact'];

$addrss=$_POST['addrss'];

$uid=$_GET['id'];

$ad="update user set name=?,email=?,contactno=?,addrss=? where id=?";

$stmt= $mysqli->prepare($ad);

$stmt->bind_param('ssisi',$name,$email,$contact,$addrss,$uid);

$stmt->execute();

// $newId = $stmtins->insert_id;

$stmt->close();

echo "<script>alert('Data updated Successfully');</script>" ;

}

View Demo

Download Source Code(Read, Edit ,Delete and Update data using PHP Prepared Statement)

Size: 3.33 KB

Version: V 1.0

Download Now!

Read, Edit ,Delete and Update data using PHP Prepared Statement

Recommended Tutorials for you

You may also like...

Latest Tutorials

Tutorials Categories

SQL Tutorials

Read, Edit ,Delete and Update data using PHP Prepared Statement

Recommended Tutorials for you

You may also like...

PHP Intro

PHP Constants

How to get the first day of the week in PHP

Latest Tutorials

Tutorials Categories

SQL Tutorials