PHP CRUD for Beginners Part-II
05/04/2019 . 6 minutes, 4 seconds to read . Posted by Admin#mysql #phpmyadmin #best-practices #WebDevelopment
OverView
In this Part of the Tutorial, we will perform two basics operation of CRUD( Update, Delete) using the modern approach PDO(PHP Document Object). PDO is a lightweight PHP extension that provides a data-access abstraction layer. For part-I click on the link https://www.coderanks.com/crud/php.
Pre-requisite
To begin with this tutorial, you need to proceed with Part-I of the tutorial, click the link for Part-I https://www.coderanks.com/crud/php.
App Structure
In the 2nd part of the tutorial, we have three main files
- edit.php– used for editing a record. It uses an HTML form which will be filled out with data based on the given "id" parameter.
- update.php– used for updating a record. It uses a PHP Script that gets "id" parameter and updates that specific record.
- delete.php– used for deleting a record. It accepts an "id" parameter and deletes the record with it.
Update Record
The file contains PHP Script to fetch specific record from the database using the id parameter passed as a query string on the redirect from index.php to edit.php. The select query is used to retrieve the record using where clause. The form contains input with an additional property value that displays the value fetched from the database. The form post the values to update.php using the POST method to update these values we have a script in update.php
edit.php
<!DOCTYPE HTML>
<html>
<head>
<title>PHP CRUD Tutorial</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
<?php
$hostname='localhost';
$db_name='php_crud';
$username='root';
$password='';
try {
$con = new PDO("mysql:host=$hostname;dbname=$db_name",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// echo "<div class='alert alert-success'>Connection Established Sucessfully.</div>";
}
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
$id=$_GET['uid'];
$query="SELECT * from user where id=:id";
$stmt=$con->prepare($query);
$stmt->bindValue(':id', $id);
$res=$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_ASSOC);
?>
<div class="container">
<div class="page-header">
<h1>Edit User</h1>
</div>
<form action="update.php?uid=<?=$user['id']?>" method="post">
<table class='table table-hover table-responsive table-bordered'>
<tr>
<td> First Name</td>
<td><input type='text' name='first_name' class='form-control' value='<?=$user['first_name']?>' />
</td>
</tr>
<tr>
<td>Last Name</td>
<td><input type='text' name='last_name' class='form-control' value='<?=$user['last_name']?>' /></td>
</tr>
<tr>
<td>Email</td>
<td> <input type='email' name='email' class='form-control' value='<?=$user['email']?>' /></td>
</tr>
<tr>
<td></td>
<td>
<input type='submit' value='Update' class='btn btn-primary' />
<a href='index.php' class='btn btn-danger'>Back to User List</a>
</td>
</tr>
</table>
</form>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</body>
</html>File Output
It receives "id" as a parameter and applies UPDATE query on the row where id matches and updated the record and returns true or false.
update.php
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<?php
$hostname='localhost';
$db_name='php_crud';
$username='root';
$password='';
try {
$con = new PDO("mysql:host=$hostname;dbname=$db_name",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// echo "<div class='alert alert-success'>Connection Established Sucessfully.</div>";
}
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
$id=$_GET['uid'];
$first_name=$_POST['first_name'];
$last_name=$_POST['last_name'];
$email=$_POST['email'];
$password="123456789";
$status=1;
$query=" UPDATE user SET first_name=:name, last_name=:lastname, email=:email, password=:password, status=:status where id=:id";
$stmt=$con->prepare($query);
$stmt->bindValue(':id', $id);
$stmt->bindParam(':name', $first_name);
$stmt->bindParam(':lastname', $last_name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':password', $password);
$stmt->bindParam(':status', $status);
if($stmt->execute())
{
echo "<div class='alert alert-success'>User Update SuccessFully.</div>";
echo "<a class='btn btn-md btn-danger' href='index.php'>Go Back </a>";
}
else{
echo "<div class='alert alert-danger'>User Upate Failed.</div>";
echo "<a class='btn btn-md btn-danger' href='index.php'>Go Back </a>";
}
?>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
File Output
Delete Record
index.php has the button of delete displaying in the table with each record on click it will ask for confirmation.
File Output
This file includes PHP code to delete a specific record it accepts id as a parameter and deletes the row from the table where id matches.
delete.php
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<?php
$hostname='localhost';
$db_name='php_crud';
$username='root';
$password='';
try {
$con = new PDO("mysql:host=$hostname;dbname=$db_name",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// echo "<div class='alert alert-success'>Connection Established Sucessfully.</div>";
}
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
$id=$_GET['uid'];
$query="DELETE from user where id=:id";
$stmt=$con->prepare($query);
$stmt->bindValue(':id', $id);
if($stmt->execute())
{
echo "<div class='alert alert-success'>User Deleted SuccessFully.</div>";
echo "<a class='btn btn-md btn-danger' href='index.php'>Go Back </a>";
}
else{
echo "<div class='alert alert-danger'>delete user failed.</div>";
echo "<a class='btn btn-md btn-danger' href='index.php'>Go Back </a>";
}
?>
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>File Output
Important Points
- PDO extension works with PHP 5.0 or higher version.
- If you want to run this code on PHP lower version,you need to Install PDO extension.
- Crud App is not completed yet,we will perform remaning two CRUD operations (Update,Delete) later in Part-II of this tutorial.
- email attribute is unique in the "user" table,always enter unique email.
Hope you have learned CRUD in PHP
Thanks