PHP - Complete PDO CRUD

Submitted by: 
Language: 
Visitors have accessed this post 499 times.

In this tutorial we will create a Complete PDO CRUD using PDO. This code has several functionalities that can manipulate a data through database server using PDO query. The code use a PDO query to (Create, Read, Update, Delete) a certain data to a database server with a high data protection in order to avoid data injection tools.

We will be using PDO as a query scripting it an acronym for PHP Data Objects. It is a lean, clean, and consistent way to access databases. This means developers can write portable code much easier.

Getting started:

First you have to download & install XAMPP or any local server that run PHP scripts. Here's the link for XAMPP server https://www.apachefriends.org/index.html.

And, this is the link for the bootstrap that i used for the layout design https://getbootstrap.com/.

Creating Database

Open your database web server then create a database name in it db_pdo_crud, after that click Import then locate the database file inside the folder of the application then click ok.
tut1

Creating the database connection

Open your any kind of text editor(notepadd++, etc..). Then just copy/paste the code below then name it conn.php.

  1. <?php
  2. $db_username = 'root';
  3. $db_password = '';
  4. $conn = new PDO( 'mysql:host=localhost;dbname=db_pdo_crud', $db_username, $db_password );
  5. if(!$conn){
  6. die("Fatal Error: Connection Failed!");
  7. }
  8. ?>

Creating The Interface

This is where we will create a simple form for our application. To create the forms simply copy and write it into you text editor, then save it as index.php.

  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4. <link rel="stylesheet" type="text/css" href="css/bootstrap.css">
  5. <meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
  6. </head>
  7. <body>
  8. <nav class="navbar navbar-default">
  9. <div class="container-fluid">
  10. <a href="<a href="https://sourcecodester.com"" rel="nofollow">https://sourcecodester.com"</a> class="navbar-brand">Sourcecodester</a>
  11. </div>
  12. </nav>
  13. <div class="col-md-3"></div>
  14. <div class="col-md-6 well">
  15. <h3 class="text-primary">PHP - Complete PDO CRUD</h3>
  16. <hr style="border-top:1px dotted #ccc;" />
  17. <div class="col-md-3">
  18. <form method="POST" action="add.php">
  19. <div class="form-group">
  20. <label>Firstname</label>
  21. <input class="form-control" type="text" name="firstname"/>
  22. </div>
  23. <div class="form-group">
  24. <label>Lastname</label>
  25. <input class="form-control" type="text" name="lastname"/>
  26. </div>
  27. <div class="form-group">
  28. <label>Address</label>
  29. <input class="form-control" type="text" name="address"/>
  30. </div>
  31. <div class="form-group">
  32. <button class="btn btn-primary form-control" type="submit" name="save">Save</button>
  33. </div>
  34. </form>
  35. </div>
  36. <div class="col-md-9">
  37. <table class="table table-bordered">
  38. <thead class="alert-info">
  39. <tr>
  40. <th>Firstname</th>
  41. <th>Lastname</th>
  42. <th>Address</th>
  43. <th>Action</th>
  44. </tr>
  45. </thead>
  46. <tbody>
  47. <?php
  48. require 'conn.php';
  49. $sql = $conn->prepare("SELECT * FROM `user`");
  50. $sql->execute();
  51. while($fetch = $sql->fetch()){
  52. ?>
  53. <tr>
  54. <td><?php echo $fetch['firstname']?></td>
  55. <td><?php echo $fetch['lastname']?></td>
  56. <td><?php echo $fetch['address']?></td>
  57. <td><button class="btn btn-warning btn-sm" data-toggle="modal" data-target="#update<?php echo $fetch['user_id']?>">Edit</button> | <a class="btn btn-danger btn-sm" href="delete.php?id=<?php echo $fetch['user_id']?>">Delete</a></td>
  58. </tr>
  59.  
  60. <div class="modal fade" id="update<?php echo $fetch['user_id']?>" aria-hidden="true">
  61. <div class="modal-dialog">
  62. <div class="modal-content">
  63. <form method="POST" action="update.php">
  64. <div class="modal-header">
  65. <h3 class="modal-title">Update User</h3>
  66. </div>
  67. <div class="modal-body">
  68. <div class="col-md-2"></div>
  69. <div class="col-md-8">
  70. <div class="form-group">
  71. <label>Firstname</label>
  72. <input class="form-control" type="text" value="<?php echo $fetch['firstname']?>" name="firstname"/>
  73. <input type="hidden" value="<?php echo $fetch['user_id']?>" name="user_id"/>
  74. </div>
  75. <div class="form-group">
  76. <label>Lastname</label>
  77. <input class="form-control" type="text" value="<?php echo $fetch['lastname']?>" name="lastname"/>
  78. </div>
  79. <div class="form-group">
  80. <label>Address</label>
  81. <input class="form-control" type="text" value="<?php echo $fetch['address']?>" name="address"/>
  82. </div>
  83. <div class="form-group">
  84. <button class="btn btn-warning form-control" type="submit" name="update">Update</button>
  85. </div>
  86. </div>
  87. </div>
  88. <br style="clear:both;"/>
  89. <div class="modal-footer">
  90. <button class="btn btn-danger" data-dismiss="modal">Close</button>
  91. </div>
  92. </form>
  93. </div>
  94. </div>
  95. </div>
  96.  
  97. <?php
  98. }
  99. ?>
  100. </tbody>
  101. </table>
  102. </div>
  103. </div>
  104. <script src="js/jquery-3.2.1.min.js"></script>
  105. <script src="js/bootstrap.js"></script>
  106. </body>
  107. </html>

Creating the Main Function

This code contains the main function of the application. This code can manipulate a data when the button is clicked. To do that write these block of codes inside the text editor and save it as shown below.
add.php

  1. <?php
  2. require_once 'conn.php';
  3.  
  4. if(ISSET($_POST['save'])){
  5. try{
  6. $firstname = $_POST['firstname'];
  7. $lastname = $_POST['lastname'];
  8. $address = $_POST['address'];
  9. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  10. $sql = "INSERT INTO `user` (`firstname`, `lastname`, `address`) VALUES ('$firstname', '$lastname', '$address')";
  11. $conn->exec($sql);
  12. }catch(PDOException $e){
  13. echo $e->getMessage();
  14. }
  15.  
  16. $conn = null;
  17. header('location:index.php');
  18. }
  19. ?>

update.php

  1. <?php
  2. require_once 'conn.php';
  3.  
  4. if(ISSET($_POST['update'])){
  5. try{
  6. $user_id = $_POST['user_id'];
  7. $firstname = $_POST['firstname'];
  8. $lastname = $_POST['lastname'];
  9. $address = $_POST['address'];
  10. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  11. $sql = "UPDATE `user`SET `firstname` = '$firstname', `lastname` = '$lastname', `address` = '$address' WHERE `user_id` = '$user_id'";
  12. $conn->exec($sql);
  13. }catch(PDOException $e){
  14. echo $e->getMessage();
  15. }
  16.  
  17. $conn = null;
  18. header('location:index.php');
  19. }
  20. ?>

delete.php

  1. <?php
  2. if(ISSET($_GET['id'])){
  3. require_once 'conn.php';
  4. $id = $_GET['id'];
  5. $sql = $conn->prepare("DELETE from `user` WHERE `user_id`='$id'");
  6. $sql->execute();
  7. header('location:index.php');
  8. }
  9. ?>

There you have it we successfully created a Complete PDO CRUD using PDO. I hope that this simple tutorial help you to what you are looking for. For more updates and tutorials just kindly visit this site. Enjoy Coding!!!


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.