PHP CRUD Operation using PDO with Bootstrap/Modal

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

Getting Started

I've used bootstrap and jQuery in this tutorial to enable bootstrap modals. These files are included in the downloadable of this tutorial but, if you want, you may download them using the links below:

Bootstrap
jQuery

Creating our Database

Next, we create our MySQL database.

I've included a .sql file in the downloadable of this tutorial which is a mysql database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.

You should be able to create a database with tables named mydatabase.

Creating our Connection

Next, we create a connection to our database by creating a new file, name it as connection.php and paste the codes below.

  1. <?php
  2.  
  3. Class Connection{
  4.  
  5. private $server = "mysql:host=localhost;dbname=mydatabase";
  6. private $username = "root";
  7. private $password = "";
  8. private $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,);
  9. protected $conn;
  10.  
  11. public function open(){
  12. try{
  13. $this->conn = new PDO($this->server, $this->username, $this->password, $this->options);
  14. return $this->conn;
  15. }
  16. catch (PDOException $e){
  17. echo "There is some problem in connection: " . $e->getMessage();
  18. }
  19.  
  20. }
  21.  
  22. public function close(){
  23. $this->conn = null;
  24. }
  25.  
  26. }
  27.  
  28. ?>

In here, we have created a class named Connection and we are going to include this file and create a new object for our connection if we want to connect to our database.

Displaying our Data

Next, we are going to display that data in our table by creating a new file, name it as our index.php and paste the below codes.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>PHP CRUD Operation using PDO with Bootstrap/Modal</title>
  6. <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
  7. </head>
  8. <body>
  9. <div class="container">
  10. <h1 class="page-header text-center">PHP CRUD Operation using PDO</h1>
  11. <div class="row">
  12. <div class="col-sm-8 col-sm-offset-2">
  13. <a href="#addnew" class="btn btn-primary" data-toggle="modal"><span class="glyphicon glyphicon-plus"></span> New</a>
  14. <?php
  15. if(isset($_SESSION['message'])){
  16. ?>
  17. <div class="alert alert-info text-center" style="margin-top:20px;">
  18. <?php echo $_SESSION['message']; ?>
  19. </div>
  20. <?php
  21.  
  22. unset($_SESSION['message']);
  23. }
  24. ?>
  25. <table class="table table-bordered table-striped" style="margin-top:20px;">
  26. <thead>
  27. <th>ID</th>
  28. <th>Firstname</th>
  29. <th>Lastname</th>
  30. <th>Address</th>
  31. <th>Action</th>
  32. </thead>
  33. <tbody>
  34. <?php
  35. //include our connection
  36. include_once('connection.php');
  37.  
  38. $database = new Connection();
  39. $db = $database->open();
  40. try{
  41. $sql = 'SELECT * FROM members';
  42. foreach ($db->query($sql) as $row) {
  43. ?>
  44. <tr>
  45. <td><?php echo $row['id']; ?></td>
  46. <td><?php echo $row['firstname']; ?></td>
  47. <td><?php echo $row['lastname']; ?></td>
  48. <td><?php echo $row['address']; ?></td>
  49. <td>
  50. <a href="#edit_<?php echo $row['id']; ?>" class="btn btn-success btn-sm" data-toggle="modal"><span class="glyphicon glyphicon-edit"></span> Edit</a>
  51. <a href="#delete_<?php echo $row['id']; ?>" class="btn btn-danger btn-sm" data-toggle="modal"><span class="glyphicon glyphicon-trash"></span> Delete</a>
  52. </td>
  53. <?php include('edit_delete_modal.php'); ?>
  54. </tr>
  55. <?php
  56. }
  57. }
  58. catch(PDOException $e){
  59. echo "There is some problem in connection: " . $e->getMessage();
  60. }
  61.  
  62. //close connection
  63. $database->close();
  64.  
  65. ?>
  66. </tbody>
  67. </table>
  68. </div>
  69. </div>
  70. </div>
  71. <?php include('add_modal.php'); ?>
  72. <script src="jquery.min.js"></script>
  73. <script src="bootstrap/js/bootstrap.min.js"></script>
  74. </body>
  75. </html>

Creating our Add Modal

Next, we create the modal that will hold our add form by creating a new file, name it as add_modal.php and paste the below codes.

  1. <!-- Add New -->
  2. <div class="modal fade" id="addnew" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  3. <div class="modal-dialog">
  4. <div class="modal-content">
  5. <div class="modal-header">
  6. <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  7. <center><h4 class="modal-title" id="myModalLabel">Add New</h4></center>
  8. </div>
  9. <div class="modal-body">
  10. <div class="container-fluid">
  11. <form method="POST" action="add.php">
  12. <div class="row form-group">
  13. <div class="col-sm-2">
  14. <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
  15. </div>
  16. <div class="col-sm-10">
  17. <input type="text" class="form-control" name="firstname">
  18. </div>
  19. </div>
  20. <div class="row form-group">
  21. <div class="col-sm-2">
  22. <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
  23. </div>
  24. <div class="col-sm-10">
  25. <input type="text" class="form-control" name="lastname">
  26. </div>
  27. </div>
  28. <div class="row form-group">
  29. <div class="col-sm-2">
  30. <label class="control-label" style="position:relative; top:7px;">Address:</label>
  31. </div>
  32. <div class="col-sm-10">
  33. <input type="text" class="form-control" name="address">
  34. </div>
  35. </div>
  36. </div>
  37. </div>
  38. <div class="modal-footer">
  39. <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  40. <button type="submit" name="add" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</a>
  41. </form>
  42. </div>
  43.  
  44. </div>
  45. </div>
  46. </div>

Creating our Edit and Delete Modal

Next, we create the modal for our edit and delete. Create a new file, name it as edit_delete_modal.php and paste the below codes.

  1. <!-- Edit -->
  2. <div class="modal fade" id="edit_<?php echo $row['id']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  3. <div class="modal-dialog">
  4. <div class="modal-content">
  5. <div class="modal-header">
  6. <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  7. <center><h4 class="modal-title" id="myModalLabel">Edit Member</h4></center>
  8. </div>
  9. <div class="modal-body">
  10. <div class="container-fluid">
  11. <form method="POST" action="edit.php?id=<?php echo $row['id']; ?>">
  12. <div class="row form-group">
  13. <div class="col-sm-2">
  14. <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
  15. </div>
  16. <div class="col-sm-10">
  17. <input type="text" class="form-control" name="firstname" value="<?php echo $row['firstname']; ?>">
  18. </div>
  19. </div>
  20. <div class="row form-group">
  21. <div class="col-sm-2">
  22. <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
  23. </div>
  24. <div class="col-sm-10">
  25. <input type="text" class="form-control" name="lastname" value="<?php echo $row['lastname']; ?>">
  26. </div>
  27. </div>
  28. <div class="row form-group">
  29. <div class="col-sm-2">
  30. <label class="control-label" style="position:relative; top:7px;">Address:</label>
  31. </div>
  32. <div class="col-sm-10">
  33. <input type="text" class="form-control" name="address" value="<?php echo $row['address']; ?>">
  34. </div>
  35. </div>
  36. </div>
  37. </div>
  38. <div class="modal-footer">
  39. <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  40. <button type="submit" name="edit" class="btn btn-success"><span class="glyphicon glyphicon-check"></span> Update</a>
  41. </form>
  42. </div>
  43.  
  44. </div>
  45. </div>
  46. </div>
  47.  
  48. <!-- Delete -->
  49. <div class="modal fade" id="delete_<?php echo $row['id']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  50. <div class="modal-dialog">
  51. <div class="modal-content">
  52. <div class="modal-header">
  53. <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  54. <center><h4 class="modal-title" id="myModalLabel">Delete Member</h4></center>
  55. </div>
  56. <div class="modal-body">
  57. <p class="text-center">Are you sure you want to Delete</p>
  58. <h2 class="text-center"><?php echo $row['firstname'].' '.$row['lastname']; ?></h2>
  59. </div>
  60. <div class="modal-footer">
  61. <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  62. <a href="delete.php?id=<?php echo $row['id']; ?>" class="btn btn-danger"><span class="glyphicon glyphicon-trash"></span> Yes</a>
  63. </div>
  64.  
  65. </div>
  66. </div>
  67. </div>

Creating our Add Script

Next, we create our script that will add data to our database. Create a new file, name it as add.php and paste the code below.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. if(isset($_POST['add'])){
  5. $database = new Connection();
  6. $db = $database->open();
  7. try{
  8. //make use of prepared statement to prevent sql injection
  9. $stmt = $db->prepare("INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
  10. //if-else statement in executing our prepared statement
  11. $_SESSION['message'] = ( $stmt->execute(array(':firstname' => $_POST['firstname'] , ':lastname' => $_POST['lastname'] , ':address' => $_POST['address'])) ) ? 'Member added successfully' : 'Something went wrong. Cannot add member';
  12.  
  13. }
  14. catch(PDOException $e){
  15. $_SESSION['message'] = $e->getMessage();
  16. }
  17.  
  18. //close connection
  19. $database->close();
  20. }
  21.  
  22. else{
  23. $_SESSION['message'] = 'Fill up add form first';
  24. }
  25.  
  26. header('location: index.php');
  27.  
  28. ?>

Creating our Edit Script

Next, we create our edit script by creating a new file, name it as edit.php and paste the below codes.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. if(isset($_POST['edit'])){
  5. $database = new Connection();
  6. $db = $database->open();
  7. try{
  8. $id = $_GET['id'];
  9. $firstname = $_POST['firstname'];
  10. $lastname = $_POST['lastname'];
  11. $address = $_POST['address'];
  12.  
  13. $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
  14. //if-else statement in executing our query
  15. $_SESSION['message'] = ( $db->exec($sql) ) ? 'Member updated successfully' : 'Something went wrong. Cannot update member';
  16.  
  17. }
  18. catch(PDOException $e){
  19. $_SESSION['message'] = $e->getMessage();
  20. }
  21.  
  22. //close connection
  23. $database->close();
  24. }
  25. else{
  26. $_SESSION['message'] = 'Fill up edit form first';
  27. }
  28.  
  29. header('location: index.php');
  30.  
  31. ?>

Creating our Delete Script

Lastly, we create our delete script which will delete our selected row by creating a new file, name it as delete.php and paste the below codes.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. if(isset($_GET['id'])){
  5. $database = new Connection();
  6. $db = $database->open();
  7. try{
  8. $sql = "DELETE FROM members WHERE id = '".$_GET['id']."'";
  9. //if-else statement in executing our query
  10. $_SESSION['message'] = ( $db->exec($sql) ) ? 'Member deleted successfully' : 'Something went wrong. Cannot delete member';
  11. }
  12. catch(PDOException $e){
  13. $_SESSION['message'] = $e->getMessage();
  14. }
  15.  
  16. //close connection
  17. $database->close();
  18.  
  19. }
  20. else{
  21. $_SESSION['message'] = 'Select member to delete first';
  22. }
  23.  
  24. header('location: index.php');
  25.  
  26. ?>

That ends this tutorial. Happy Coding :)


Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

Hello, first of all, great tutorial!

I want to use the Modal CRUD for my website, but I have MSSQL Server, not MYSQL, but this should not be a problem.

I have made a Modal with taps, in the first tab there will be shown general information, in the second one there will be a kind of chat. I can open the modal, can select the tabs, I can see the right entries but I have a problem to add a new chat entry. This is my add.php:

  1. include_once 'dbconfig.php';
  2. $message = $_POST['message'];
  3. $regid = $_POST["reqid"];
  4. try
  5. {
  6. $stmt =$DB_con -> prepare("INSERT INTO dbo.RequestChat (ReqID, message) VALUES (:id, :message)");
  7.  
  8. $stmt->bindParam(':id',$regid);
  9. $stmt->bindParam(':message',$message);
  10. $stmt->execute();
  11. return true;
  12. }
  13. catch(PDOException $e)
  14. {
  15. echo $e->getMessage();
  16. return false;
  17. }
  18.  

I have added this code to my index.php to test the add function, it works, it seems that there is something missing in the modal or in the add.php, so the values can nit been written in the database.

Do you have an idea why I can not add something in the database.

BR Sven

Hi there, first of all, great Tutorial,

I want to use CRUD with Modal in my website, but I have a problem to add new entries in my database. I am using MSSQL, here is my add.php:

  1. include_once 'dbconfig.php';
  2. $message = $_POST['message'];
  3. $regid = $_POST["reqid"];
  4.  
  5. try
  6. {
  7. $stmt =$DB_con -> prepare("INSERT INTO dbo.RequestChat (ReqID, message) VALUES (:id, :message)");
  8.  
  9. $stmt->bindParam(':id',$regid);
  10. $stmt->bindParam(':message',$message);
  11. $stmt->execute();
  12.  
  13. return true;
  14. }
  15. catch(PDOException $e)
  16. {
  17. echo $e->getMessage();
  18. return false;
  19. }

I have tested this code in my index.php, it works like a charm. but when I use it like in your Tutorial, I can not add a new entry in the database.

Do you have an idea where my failure is?

BR

Sven

Hi there, firat of all, very coot tutorial!

I want to use CRUD with Modal and MSSQL, but I have a problem by adding a new entry in MSSQL Database, my add.php loosk like:

  1. include_once 'dbconfig.php';
  2. $message = $_POST['message'];
  3. $regid = $_POST["reqid"];
  4. try
  5. {
  6. $stmt =$DB_con -> prepare("INSERT INTO dbo.RequestChat (ReqID, message) VALUES (:id, :message)");
  7. $stmt->bindParam(':id',$regid);
  8. $stmt->bindParam(':message',$message);
  9. $stmt->execute();
  10. return true;
  11. }
  12. catch(PDOException $e)
  13. {
  14. echo $e->getMessage();
  15. return false;
  16. }

Do you have am idea why i can not add something in the database?

BR
Sven

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.