In this article, We will create a PHP crud operation using PDO extension. The CRUD means Create, edit, update and delete. so here we will give you a simple example of insert, update, delete using PHP PDO with MySQL.
PDO stands for PHP Data Objects. PDO is a simple and consistent way to access databases. This means that developers can write portable code much more easily. PDO is not an abstraction layer like PearDB. PDO is just more like a data access layer that uses a unified API.
So you can see our example for PHP crud operation using PDO extension.
Create Database and Tables
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(64) NOT NULL,
`last_name` varchar(64) NOT NULL,
`address` text NOT NULL,
`email` varchar(64) NOT NULL,
`mobile` varchar(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
connect to database in mysql
Create a new connect.php file. The code below is to provide how to connect PDO with MySQL.
The following code is used to connect MySQL from PHP. It requires hostname, database username, database password, and database name.
<?php
// DB credentials.
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'php_crud_pdo';
try
{
$db = new PDO("mysql:host=".$db_host.";dbname=".$db_name,$db_user, $db_pass);
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?>Creating New Record in MySQL Database
Create a PHP file add.php in this file in add the below code.
The code below in the INSERT query through creates a new record to the database table. This HTML form contains input fields to enter user data to be inserted into the table.
<?php
if(isset($_POST['btnadd']))
{
$first_name = $_POST['txtFname'];
$last_name = $_POST['txtLname'];
$address = $_POST['txtAddress'];
$email = $_POST['txtEmail'];
$mobile = $_POST['txtMobile'];
include('connect.php');
$sql="insert into users (first_name,last_name,address,email,mobile) values(:first_name,:last_name,:address,:email,:mobile)";
$query = $db->prepare($sql);
$query->bindParam(':first_name',$first_name,PDO::PARAM_STR);
$query->bindParam(':last_name',$last_name,PDO::PARAM_STR);
$query->bindParam(':address',$address,PDO::PARAM_STR);
$query->bindParam(':email',$email,PDO::PARAM_STR);
$query->bindParam(':mobile',$mobile,PDO::PARAM_STR);
$query->execute();
$lastInsertId = $db->lastInsertId();
header('location:index.php');
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title></title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="row" style="margin-bottom:10px;">
<div class="col-lg-10"></div>
<div class="col-lg-2"><a href="index.php" class="btn btn-primary">Back</a></div>
</div>
<form method="post" name="frmAdd">
<div class="form-group">
<label for="txtFname">First Name:</label>
<input type="text" class="form-control" name="txtFname" required id="txtFname">
</div>
<div class="form-group">
<label for="txtLname">Last Name:</label>
<input type="text" class="form-control" required name="txtLname" id="txtLname">
</div>
<div class="form-group">
<label for="txtEmail">Email:</label>
<input type="email" class="form-control" required name="txtEmail" id="txtEmail">
</div>
<div class="form-group">
<label for="txtMobile">Mobile:</label>
<input type="text" class="form-control" required name="txtMobile" id="txtMobile">
</div>
<div class="form-group">
<label for="txtAddress">Address:</label>
<textarea name="txtAddress" class="form-control" required id="txtAddress"></textarea>
</div>
<button type="submit" class="btn btn-success" name="btnadd">Add</button>
</form>
</div>
</body>
</html>PHP MySQL Read
Create a new PHP file index.php in this file in add the below code.
The following code shows how to fetch all the records from the database in the index file.
<!DOCTYPE html>
<html lang="en">
<head>
<title></title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="row" style="margin-bottom:10px;">
<div class="col-lg-10"></div>
<div class="col-lg-2"><a href="add.php" class="btn btn-primary">Add</a></div>
</div>
<table class='display dataTable table table-bordered table-striped no-footer' align="center">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Email</th>
<th>Mobile</th>
<th>Action</th>
</tr>
</thead>
<?php
include('connect.php');
$slt="select * from users";
$query=$db->prepare($slt);
$query->execute();
while($row=$query->fetch(PDO::FETCH_ASSOC))
{
?>
<tr>
<td><?php echo $row['first_name']; ?></td>
<td><?php echo $row['last_name']; ?></td>
<td><?php echo $row['address']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['mobile']; ?></td>
<td><a class="btn btn-primary" href="edit.php?edit_id=<?php echo $row['id'];?>">Edit</a> <a class="btn btn-danger" href="delete.php?delete_id=<?php echo $row['id'];?>" onclick="return confirm('Are you sure you want to delete this data?');">Delete</a> </td>
</tr>
<?php
}
?>
</table>
</div>
</body>
</html>Php MySQL Update Record
Create a new edit.php file in this file to add the below code.
First, we will fetch a particular data record in the update form. On submitting edited user information we form an update query to edit the record with the reference of its edit_id.
<?php
$edit_id = $_REQUEST['edit_id'];
include('connect.php');
$slt="select * from users where id=:id";
$query=$db->prepare($slt);
$query->bindParam(':id',$edit_id,PDO::PARAM_STR);
$query->execute();
$row=$query->fetch(PDO::FETCH_ASSOC);
?>
<?php
if(isset($_POST['btnUpdate']))
{
$first_name = $_POST['txtFname'];
$last_name = $_POST['txtLname'];
$address = $_POST['txtAddress'];
$email = $_POST['txtEmail'];
$mobile = $_POST['txtMobile'];
include('connect.php');
$sql="update users set first_name=:first_name,last_name=:last_name,address=:address,email=:email,mobile=:mobile where id=:id";
$query = $db->prepare($sql);
$query->bindParam(':first_name',$first_name,PDO::PARAM_STR);
$query->bindParam(':last_name',$last_name,PDO::PARAM_STR);
$query->bindParam(':address',$address,PDO::PARAM_STR);
$query->bindParam(':email',$email,PDO::PARAM_STR);
$query->bindParam(':mobile',$mobile,PDO::PARAM_STR);
$query->bindParam(':id',$edit_id,PDO::PARAM_STR);
$query->execute();
header('location:index.php');
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title></title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="row" style="margin-bottom:10px;">
<div class="col-lg-10"></div>
<div class="col-lg-2"><a href="index.php" class="btn btn-primary">Back</a></div>
</div>
<form method="post" name="frmUpdate">
<div class="form-group">
<label for="txtFname">First Name:</label>
<input type="text" class="form-control" name="txtFname" required id="txtFname" value="<?php echo $row['first_name'];?>">
</div>
<div class="form-group">
<label for="txtLname">Last Name:</label>
<input type="text" class="form-control" name="txtLname" required id="txtLname" value="<?php echo $row['last_name'];?>">
</div>
<div class="form-group">
<label for="txtEmail">Email:</label>
<input type="email" class="form-control" name="txtEmail" required id="txtEmail" value="<?php echo $row['email'];?>">
</div>
<div class="form-group">
<label for="txtMobile">Mobile:</label>
<input type="text" class="form-control" name="txtMobile" required id="txtMobile" value="<?php echo $row['mobile'];?>">
</div>
<div class="form-group">
<label for="txtAddress">Address:</label>
<textarea name="txtAddress" class="form-control" required id="txtAddress"><?php echo $row['address'];?></textarea>
</div>
<button type="submit" class="btn btn-success" value="" name="btnUpdate">Update</button>
</form>
</div>
</body>
</html>Delete Record from MySQL Database
Create a new delete.php file in this file to add the below code.
The below code is used to delete a particular record from the database.
<?php
include('connect.php');
$sql = "delete from users WHERE id=:id";
$query = $db->prepare($sql);
$query->bindParam(':id',$_REQUEST['delete_id'], PDO::PARAM_STR);
$query->execute();
header("location:index.php");
?>We think would you like this article, so you can click on “Show Demo” button and you can see this demo article.
Please follow and like us:
