Select, Insert, Update And Delete with PDO MySQL

By | November 13, 2016

In today’s post we will cover Simple select, insert, update and delete with PDO MySQL (CRUD) . Many developers these days prefer using PDO for CRUD operations as compared to older method. Using PDO we will have more security because it has better password encryption. PDO gives us portability to use different database systems. For this tutorial we will stick with MySQL database.

Select, Insert, Update And Delete with PDO MySQL

PDO is a PHP extension used commonly for CRUD operations.

We have break down our code in three separate files. First file db.php consists of database connection. Crud.php file will contain User Interface wherein we will insert, update and delete records. And the third file crud_process.php contains functionality to perform crud operation. Also check pdo tutorial video where we have shown the demo.

Live Demo

Download

You can download our script. Live demo doesn’t show update and delete functionality it will only work for inserting records. Complete CRUD code using PDO can be downloaded from here.

Select, Insert, Update And Delete with PDO MySQL

db.php file to make MySQL database connection.


<?php $server = "localhost"; $username = "root"; $password = ""; $dbname = "PDO"; $dconn = new PDO("mysql:host=$server;dbname=$dbname", $username, $password); $dconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

?>

crud.php File contains user interface. Insert, update and delete buttons. Use ID to delete and update records.


<?php require_once 'db.php'; ?>
<!DOCTYPE html>
<html>
<head>
 <title>PDO TUTORIAL</title>


<style>
 #Form_data
 {
 
 position: relative;
 left: -275px;
 top: 75px;
 width: 204px;
 margin: 0px auto;
 text-align: left;
 padding: 20px;
 background-color: #ffffff;
 color: #333;
 border: 1px solid #e5e5e5;
 box-shadow: rgba(200,200,200,0.7) 0 4px 10px -1px;
 
 }
 .buttons
 {
 border-color: #3ac162 !important;
 color: #fff;
 background-color: #5fcf80 !important;
 }

 #tabledata
 {

 position: relative;
 left: 175px;
 top: -250px;
 width: 320px;
 margin: 0px auto;
 text-align: left;
 padding: 20px;
 background-color: #ffffff;
 color: #333;
 border: 1px solid #e5e5e5;
 box-shadow: rgba(200,200,200,0.7) 0 4px 10px -1px;
 
 }
</style>


</head>
<body>



<h3 style="color:red;">* Enter ID only while deleting or updating record</h3>




<form method="POST" action="crud_process.php">


<div id="Form_data">
<label><b>TechPlugg PDO Tutorial</b></label></br></br>
<label style="color:#555">Name</label>

 <input type="text" name="firstname" id="firstname"></br></br>
 <label style="color:#555">Email</label>
 <input type="text" name="email" id="email"></br></br>
 <label style="color:#555">Password</label>
 <input type="password" name="password" id="password"></br></br>

 
 <label style="color:#555">ID</label>
 <input type="text" name="id"></br></br>
 <input type="submit" value ="Insert" class="buttons" name="addrecord">
 <input type="submit" value="Update" class="buttons" name="updaterecord">
 <input type="submit" value="Delete" class="buttons" name="deleterecord">
 
</div>




</form>


</body>
</html>

<?php $stmt = $dconn->prepare("SELECT * FROM users ORDER BY id ASC");
$stmt->execute();
?>



<div id="tabledata">


<table border="1" cellpadding="5" cellspacing="3" width="30%">


<tr align="left">


<th>ID</th>




<th>FIRST NAME</th>




<th>Email</th>


 </tr>



<?php if($stmt->rowCount() > 0)
{
 while($row=$stmt->FETCH(PDO::FETCH_ASSOC))
 {
 ?>
 


<tr>


<td><?php print($row['ID']); ?></td>




<td><?php print($row['firstname']); ?></td>




<td><?php print($row['email']); ?></td>


 
 
 </tr>


 <?php } } else { ?>


<tr>


<td><?php echo("No Records"); ?></td>


 </tr>


 <?php } ?>
</table>


</div>



crud_process.php file contains code for performing insert, update and update. Select statement to view data inserted in database.


<?php require_once 'db.php'; if(isset($_POST['addrecord'])) { try { $userfname = $_POST['firstname']; $useremail = $_POST['email']; $userpassword = md5($_POST['password']); $stmt = $dconn->prepare("INSERT INTO users (firstname,email,password) VALUES(:userfname, :useremail, :userpassword)");
 
 $stmt->bindparam(':userfname', $userfname);
 $stmt->bindparam(':useremail', $useremail);
 $stmt->bindparam(':userpassword', $userpassword);
 $stmt->execute();
 header("Location: crud.php");
 
 }
 catch(PDOException $e)
 {
 echo "Error: " . $e->getMessage();
 }
 }
 if(isset($_POST['updaterecord']))
 {
 $userfname = $_POST['firstname']; 
 $useremail = $_POST['email'];
 $userpassword = md5($_POST['password']);
 $id = $_POST['id'];
 
 $stmt = $dconn->prepare("UPDATE users SET firstname=:userfname, email=:useremail,password=:userpassword WHERE id=:id");
 $stmt->bindparam(':userfname', $userfname);
 $stmt->bindparam(':useremail', $useremail);
 $stmt->bindparam(':userpassword', $userpassword);
 $stmt->bindparam(':id', $id);
 $stmt->execute();
 header("Location: crud.php");
 }
 
 if(isset($_POST['deleterecord']))
 {
 try 
 {
 
 $id = $_POST['id'];
 $stmt = $dconn->prepare("DELETE FROM users WHERE id=:id");
 $stmt->execute(array(':id' => $id));
 header("Location: crud.php");
 }
 catch(PDOException $e)
 {
 echo "Error: " . $e->getMessage();
 }
 }
 
 
$dconn = null;
?>

I hope this is simple PDO tutorial to perform CRUD using PHP Data object PDO.