PHP - Simple PDO CRUD

In this tutorial we will create a Simple PDO CRUD using PHP. PHP is a server-side scripting language designed primarily for web development. PDO stands for PHP Data Objects. It is a lean and consistent way to access databases. This means developers can write portable code much easier. It is mostly used by a newly coders for its user friendly environment. So Let's do the coding.

Before we 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.

Creating Database

This is where we store all the data we process throug HTML forms. To create a database open any kind of database base server that you have(wamp, xamp, etc..). Then create database and name it 'db_member'. After that click SQL then copy/paste the code below then click GO.
  1. CREATE TABLE `member` (
  2. `mem_id` int(11) NOT NULL AUTO_INCREMENT,
  3. `firstname` varchar(50) NOT NULL,
  4. `lastname` varchar(50) NOT NULL,
  5. `address` varchar(50) NOT NULL
  6. PRIMARY KEY (`mem_id`)

Creating the database Connection

This is the where the database connection, just simple copy/paste the provided code below and save it as 'connection.php'.
  1. <?php
  2. $db_username = 'root';
  3. $db_password = '';
  4. $conn = new PDO( 'mysql:host=localhost;dbname=db_member', $db_username, $db_password );
  5. if(!$conn){
  6. die("Fatal Error: Connection Failed!");
  7. }
  8. ?>

Creating The Mark Up

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 shown below. 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="https://sourcecodester.com" 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 - PDO CRUD</h3>
  16. <hr style="border-top:1px dotted #ccc;" />
  17. <div class="col-md-3"></div>
  18. <div class="col-md-6">
  19. <form method="POST" action="add.php">
  20. <div class="form-group">
  21. <label>Firstname</label>
  22. <input class="form-control" type="text" name="firstname"/>
  23. </div>
  24. <div class="form-group">
  25. <label>Lastname</label>
  26. <input class="form-control" type="text" name="lastname"/>
  27. </div>
  28. <div class="form-group">
  29. <label>Address</label>
  30. <input class="form-control" type="text" name="address"/>
  31. </div>
  32. <div class="form-group">
  33. <button class="btn btn-primary form-control" type="submit" name="save">Save</button>
  34. </div>
  35. </form>
  36. </div>
  37. <table class="table table-bordered">
  38. <thead class="alert-danger">
  39. <tr>
  40. <th>Firstname</th>
  41. <th>Lastname</th>
  42. <th>Address</th>
  43. <th>Action</th>
  44. </tr>
  45. </thead>
  46. <tbody class="alert-warning">
  47. <?php
  48. require 'connection.php';
  49. $sql = $conn->prepare("SELECT * FROM `member` ORDER BY `mem_id` DESC");
  50. $sql->execute();
  51. while($row = $sql->fetch()){
  52. ?>
  53. <tr>
  54. <td><?php echo $row['firstname']?></td>
  55. <td><?php echo $row['lastname']?></td>
  56. <td><?php echo $row['address']?></td>
  57. <td><a href="edit.php?id=<?php echo $row['mem_id']?>">Edit</a> | <a href="delete.php?id=<?php echo $row['mem_id']?>">Delete</a></td>
  58. </tr>
  59. <?php
  60. }
  61. ?>
  62. </tbody>
  63. </table>
  64. </div>
  65.  
  66. </body>
  67.  
  68. </html>
edit.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="https://sourcecodester.com" 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 - PDO CRUD</h3>
  16. <hr style="border-top:1px dotted #ccc;" />
  17. <div class="col-md-3"></div>
  18. <div class="col-md-6">
  19. <?php
  20. if(ISSET($_GET['id'])){
  21. require_once 'connection.php';
  22. $id = $_GET['id'];
  23. $sql = $conn->prepare("SELECT * FROM `member` WHERE `mem_id`='$id'");
  24. $sql->execute();
  25. $row = $sql->fetch();
  26. }
  27. ?>
  28. <form method="POST" action="update.php?id=<?php echo $id?>">
  29. <div class="form-group">
  30. <label>Firstname</label>
  31. <input class="form-control" type="text" value="<?php echo $row['firstname']?>" name="firstname"/>
  32. </div>
  33. <div class="form-group">
  34. <label>Lastname</label>
  35. <input class="form-control" type="text" value="<?php echo $row['lastname']?>" name="lastname"/>
  36. </div>
  37. <div class="form-group">
  38. <label>Address</label>
  39. <input class="form-control" type="text" value="<?php echo $row['address']?>" name="address"/>
  40. </div>
  41. <div class="form-group">
  42. <button class="btn btn-warning form-control" type="submit" name="update">Update</button>
  43. </div>
  44. </form>
  45. <?php
  46. $conn = null;
  47. ?>
  48. </div>
  49. </div>
  50.  
  51. </body>
  52.  
  53. </html>

Adding Function

We will now create the add function, this function will store the value into the database. To do that just copy/paste the code below then name it add.php.
  1. <?php
  2. require_once 'connection.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 `member` (`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. ?>

Delete Function

We will now create the delete function, this will delete the targeted value base on the mem_id key. To do that just copy/paste the code below then name it delete.php.
  1. <?php
  2. if(ISSET($_GET['id'])){
  3. require_once 'connection.php';
  4. $id = $_GET['id'];
  5. $sql = $conn->prepare("DELETE from `member` WHERE `mem_id`='$id'");
  6. $sql->execute();
  7. header('location:index.php');
  8. }
  9. ?>

Update Function

We will now create the update function, this code will stored all the value within the form and update the data into the database base on the mem_id. To do that just copy/paste the code below then name it update.php.
  1. <?php
  2. require_once 'connection.php';
  3.  
  4. if(ISSET($_POST['update'])){
  5. try{
  6. $id = $_GET['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 `member`SET `firstname` = '$firstname', `lastname` = '$lastname', `address` = '$address' WHERE `mem_id` = '$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. ?>
There you have it we create a Simple PDO CRUD. I hope that this simple tutorial help your to your on working projects. For more updates and tutorials just kindly visit this site, don't forget to LIKE and SHARE. Enjoy Coding!!

Tags

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
6 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.