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

 I am currently accepting programming work, IT projects, school and application development, programming projects, thesis and capstone projects, IT consulting work, computer tutorials, and web development work kindly contact me at the following email address for further details.  If you want to advertise on my website kindly contact me also in my email address also. Thank you.

My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is  +63 (034) 4335675.

Here in Bacolod City I also accepting computer repair, networking, and Arduino Project development at a very affordable price. My website is www.jakerpomperada.blogspot.com and www.jakerpomperada.com

If you like this video please click the LIKE button, SHARE, and SUBSCRIBE to my channel.

Your support on my channel is highly appreciated.

Thank you very much.

Program Listing

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
        }
    }
}
?>

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
        }
    }
}
?>

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>

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>Email Address</th>
<th>Mobile</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>

delete.php

<?php
include("connect.php");
if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $checkid = $conn->prepare("select * from crud where id = '" . $id . "'"); // to check id
    $checkid->execute();
    if ($checkid->rowCount() > 0) {
		$insert_query = $conn->prepare("delete from crud where id = :id"); //to insert data
        try {
            $conn->beginTransaction();
			$insert_query->bindParam(":id", $id);
            $count = $insert_query->execute();
			
            if ($count> 0) {
                header("Location: index.php?message=Record has been Deleted successfully"); //success data insertion
            } else {
                header("Location: index.php?message=Failed to Delete"); //failure data insertion
            }
            $conn->commit();
        }
        catch (PDOExecption $e) {
            $dbh->rollback();
            print "Error!: " . $conn->getMessage() . "</br>"; //exception
        }
        
    } else {
        header("Location: index.php?message=Invalid Request");
    }
}
?>

Download the Complete Source Code Here

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();
    }
?>

Leave a Reply

Your email address will not be published.