Sunday, December 13, 2020

CRUD using PDO in PHP and MySQL

 In this tutorial I will show you how to create CRUD PDO application using PHP and MySQL


What is PDO?

   PDO = PHP Data Objects. This is a PHP extension that defines a consistent and lightweight interface for accessing databases.

    CRUD = Create/Read/Update/Delete. It means, any basic application that has ability to for creating, deleting, updating and reading the records in the database. Every database application like Inventory System, Accounting System, Point of Sale and other Business-related applications uses CRUD as basis of its database records manipulations.


Contents for PHP PDO CRUD


Creating a database having a table

Establishing the database connection

Creating and Reading records

Edit records

Delete a record







Program Listing

connect.php

 <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "crud_pdo;
";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

index.php

<?php 
include("connect.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD using PDO in PHP and MySQL</title>
</head>

<body>
<h2>CRUD using PDO in PHP and MySQL</h2>
<h4>Add Record in the Database</h4>
<form action="create.php" method="post" enctype="application/x-www-form-urlencoded">
<label>Name</label>
<input type="text" name="name"   size=50  required="required" /><br /><br />
<label>Telephone Number</label>
<input type="text" name="telephone"  size=50  required="required" /><br /><br />
<label>Mobile Number</label>
<input type="text" name="mobile"   size=50  required="required" /><br /><br />

<label>Email Address</label>
<input type="email" name="email"  size=50 required="required" /><br /><br />

<input type="submit" name="submit" required="required" value="submit" />
</form><br><br>
<table border="1" width="900px" >
<tr>
<th>ID No.</th>
<th>Name</th>
<th>Telephone Number</th>
<th>Mobile Number</th>
<th>Email Address</th>
<th>Action</th>
</tr>
<?php 
$get_datas = $conn->prepare("SELECT * FROM crud");
$get_datas->execute();
if($get_datas->rowCount()>0){
$i=1;
while($res=$get_datas->fetch(PDO::FETCH_ASSOC)){
?>
<tr>
<td align="center"><?php echo $i++; ?></td>
<td align="center"><?php echo $res['name']; ?></td>
<td align="center"><?php echo $res['telephone']; ?></td>
<td align="center"><?php echo $res['mobile']; ?></td>
<td align="center"><?php echo $res['email']; ?></td>
<td><a href="edit.php?id=<?php echo $res['id'];?>">Edit</a><br /><a href="delete.php?id=<?php echo $res['id'];?>">Delete</a></td>
</tr>
<?php } }else{
echo "<tr><td colspan='5'>Records not found</td></tr>";
} ?>
</table>
<script type="text/javascript">
<?php if($_GET['message']){ ?>
alert('<?php echo $_GET['message'];?>');
<?php } ?>
</script>
</body>
</html>

edit.php

<?php 
include("connect.php");
$id = $_GET['id'];
$get_data = $conn->prepare("select * from crud where id = :id");
$get_data->bindParam(":id",$id);
$get_data->execute();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD using PDO in PHP and MySQL</title>
</head>

<body>
<h2>Update Record in the Databae</h2>
<?php if($get_data->rowCount()>0){ 
$result = $get_data->fetch(PDO::FETCH_ASSOC);
?>
<form action="update.php?id=<?php echo $id; ?>" method="post" enctype="application/x-www-form-urlencoded">
<label>Name</label>
<input type="text" name="name" required="required" value="<?php  if(isset($result['name'])){ echo $result['name']; } ?>" /><br /><br />
<label>Telephone Number</label>
<input type="text" name="telephone" required="required" value="<?php  if(isset($result['telephone'])){ echo $result['telephone']; } ?>" /><br /><br />
<label>Mobile Number</label>
<input type="text" name="mobile" required="required" value="<?php  if(isset($result['mobile'])){ echo $result['mobile']; } ?>" /><br /><br />

<label>Email Address</label>
<input type="email" name="email" required="required" value="<?php  if(isset($result['email'])){ echo $result['email']; } ?>" /><br /><br />

<input type="submit" name="submit" required="required" value="submit" />
</form>
<?php } else { 
echo "Invalid Request";
} ?>
<script type="text/javascript">
<?php if($_GET['message']){ ?>
alert('<?php echo $_GET['message'];?>');
<?php } ?>
</script>
</body>
</html>

update.php

<?php
include("connect.php");
$id = $_GET['id'];
$name   = "";
$telephone="";
$email  = "";
$mobile = "";
if (isset($_POST['submit'])) {
    $name         = filter_var($_POST['name'], FILTER_SANITIZE_STRING); // to filter string
    $telephone    = filter_var($_POST['telephone'], FILTER_SANITIZE_EMAIL); // to filter email
    $email        = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); // to filter email
    $mobile       = filter_var($_POST['mobile'], FILTER_SANITIZE_NUMBER_INT); // to filter number
    $check_mobile = $conn->prepare("select * from crud where mobile = '" . $mobile . "' and id not in ('".$id."')"); // to check duplicate
    $check_mobile->execute();
    if ($check_mobile->rowCount() > 0) {
        header("Location: index.php?message=Duplicate entry");
    } else {
        $insert_query = $conn->prepare("update crud set name = :name, telephone=:telephone, email=:email, mobile=:mobile where id = :id"); //to insert data
        try {
            $conn->beginTransaction();
            $insert_query->bindParam(":name", $name);
            $insert_query->bindParam(":telephone", $telephone);
            $insert_query->bindParam(":email", $email);
            $insert_query->bindParam(":mobile", $mobile);
$insert_query->bindParam(":id", $id);
            $count = $insert_query->execute();
            if ($count> 0) {
                header("Location: edit.php?id=$id&message=Record has been updated successfully"); //success data insertion
                header("location:index.php");
            } else {
                header("Location: index.php?id=$id&message=Failed to update"); //failure data insertion
            }
            $conn->commit();
        }
        catch (PDOExecption $e) {
            $dbh->rollback();
            print "Error!: " . $conn->getMessage() . "</br>"; //exception
        }
    }
}
?>

create.php



<?php

// create.php
// insert record in the table

include("connect.php");
$name   = "";
$telephone = "";
$mobile = "";
$email  = "";

if (isset($_POST['submit'])) {
    $name         = filter_var($_POST['name'], FILTER_SANITIZE_STRING); // to filter string
    $telephone    = filter_var($_POST['telephone'], FILTER_SANITIZE_EMAIL); // to filter email
    $mobile       = filter_var($_POST['mobile'], FILTER_SANITIZE_NUMBER_INT); // to filter number
    $email        = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); // to filter email
    $check_mobile = $conn->prepare("SELECT * FROM crud WHERE mobile = '" . $mobile . "'"); // to check duplicate records
    $check_mobile->execute();
    if ($check_mobile->rowCount() > 0) {
        header("Location: index.php?message=Duplicate entry");
    } else {
        $insert_query = $conn->prepare("INSERT INTO crud (name,telephone,mobile,email) VALUES (:name,:telephone,:mobile,:email)"); //to insert data in the table
        try {
            $conn->beginTransaction();
            $insert_query->bindParam(":name", $name);
            $insert_query->bindParam(":telephone", $telephone);
            $insert_query->bindParam(":mobile", $mobile);
            $insert_query->bindParam(":email", $email);
            $insert_query->execute();
            if ($conn->lastInsertId() > 0) {
                header("Location: index.php?message=Record has been inserted successfully"); //success data insertion
            } else {
                header("Location: index.php?message=Failed to insert"); //failure data insertion
            }
            $conn->commit();
        }
        catch (PDOExecption $e) {
            $dbh->rollback();
            print "Error!: " . $conn->getMessage() . "</br>"; //exception
        }
    }
}
?>

crud_pdo.sql

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 10.4.14-MariaDB 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `crud` (
`id` int (11),
`name` varchar (600),
`telephone` varchar (300),
`mobile` varchar (300),
`email` varchar (300)
); 
insert into `crud` (`id`, `name`, `telephone`, `mobile`, `email`) values('3','Jacob Samuel Pomperada','4335091','09173084334','jacobsamuel@yahoo.com');
insert into `crud` (`id`, `name`, `telephone`, `mobile`, `email`) values('4','Jake Pomperada','4335081','09173084360','jakerpomperada@gmail.com');





No comments:

Post a Comment