This tutorial tackles on how to CRUD(create, read, update, delete) MySQL database using PDO with Bootstrap/Modal. PDO as per the PHP manual stands for PHP Data Objects, a lightweight, consistent interface for accessing databases in PHP. We use Bootstrap/Modal in this tutorial to handle our forms.
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.
<?php
Class Connection{
private $server = "mysql:host=localhost;dbname=mydatabase";
private $username = "root";
private $password = "";
private $options = array(PDO
::ATTR_ERRMODE => PDO
::ERRMODE_EXCEPTION,PDO
::ATTR_DEFAULT_FETCH_MODE => PDO
::FETCH_ASSOC,); protected $conn;
public function open(){
try{
$this->conn = new PDO($this->server, $this->username, $this->password, $this->options);
return $this->conn;
}
catch (PDOException $e){
echo "There is some problem in connection: " . $e->getMessage();
}
}
public function close(){
$this->conn = null;
}
}
?>
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.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>PHP CRUD Operation using PDO with Bootstrap/Modal</title>
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h1 class="page-header text-center">PHP CRUD Operation using PDO</h1>
<div class="row">
<div class="col-sm-8 col-sm-offset-2">
<a href="#addnew" class="btn btn-primary" data-toggle="modal"><span class="glyphicon glyphicon-plus"></span> New</a>
<?php
if(isset($_SESSION['message'])){ ?>
<div class="alert alert-info text-center" style="margin-top:20px;">
<?php echo $_SESSION['message']; ?>
</div>
<?php
unset($_SESSION['message']); }
?>
<table class="table table-bordered table-striped" style="margin-top:20px;">
<thead>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
<th>Action</th>
</thead>
<tbody>
<?php
//include our connection
include_once('connection.php');
$database = new Connection();
$db = $database->open();
try{
$sql = 'SELECT * FROM members';
foreach ($db->query($sql) as $row) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['address']; ?></td>
<td>
<a href="#edit_<?php echo $row['id']; ?>" class="btn btn-success btn-sm" data-toggle="modal"><span class="glyphicon glyphicon-edit"></span> Edit</a>
<a href="#delete_<?php echo $row['id']; ?>" class="btn btn-danger btn-sm" data-toggle="modal"><span class="glyphicon glyphicon-trash"></span> Delete</a>
</td>
<?php include('edit_delete_modal.php'); ?>
</tr>
<?php
}
}
catch(PDOException $e){
echo "There is some problem in connection: " . $e->getMessage();
}
//close connection
$database->close();
?>
</tbody>
</table>
</div>
</div>
</div>
<?php include('add_modal.php'); ?>
<script src="jquery.min.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>
</body>
</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.
<!-- Add New -->
<div class="modal fade" id="addnew" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button> <div class="container-fluid"> <form method="POST" action="add.php"> <div class="row form-group"> <label class="control-label" style="position:relative; top:7px;">Firstname:
</label> <input type="text" class="form-control" name="firstname"> <div class="row form-group"> <label class="control-label" style="position:relative; top:7px;">Lastname:
</label> <input type="text" class="form-control" name="lastname"> <div class="row form-group"> <label class="control-label" style="position:relative; top:7px;">Address:
</label> <input type="text" class="form-control" name="address"> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel
</button> <button type="submit" name="add" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save
</a>
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.
<!-- Edit -->
<div class="modal fade" id="edit_<?php echo $row['id']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<center><h4 class="modal-title" id="myModalLabel">Edit Member</h4></center>
</div>
<div class="modal-body">
<div class="container-fluid">
<form method="POST" action="edit.php?id=<?php echo $row['id']; ?>">
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Firstname:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control" name="firstname" value="<?php echo $row['firstname']; ?>">
</div>
</div>
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Lastname:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control" name="lastname" value="<?php echo $row['lastname']; ?>">
</div>
</div>
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Address:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control" name="address" value="<?php echo $row['address']; ?>">
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
<button type="submit" name="edit" class="btn btn-success"><span class="glyphicon glyphicon-check"></span> Update</a>
</form>
</div>
</div>
</div>
</div>
<!-- Delete -->
<div class="modal fade" id="delete_<?php echo $row['id']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<center><h4 class="modal-title" id="myModalLabel">Delete Member</h4></center>
</div>
<div class="modal-body">
<p class="text-center">Are you sure you want to Delete</p>
<h2 class="text-center"><?php echo $row['firstname'].' '.$row['lastname']; ?></h2>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
<a href="delete.php?id=<?php echo $row['id']; ?>" class="btn btn-danger"><span class="glyphicon glyphicon-trash"></span> Yes</a>
</div>
</div>
</div>
</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.
<?php
include_once('connection.php');
if(isset($_POST['add'])){ $database = new Connection();
$db = $database->open();
try{
//make use of prepared statement to prevent sql injection
$stmt = $db->prepare("INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
//if-else statement in executing our prepared statement
$_SESSION['message'] = ( $stmt->execute(array(':firstname' => $_POST['firstname'] , ':lastname' => $_POST['lastname'] , ':address' => $_POST['address'])) ) ?
'Member added successfully' : 'Something went wrong. Cannot add member';
}
catch(PDOException $e){
$_SESSION['message'] = $e->getMessage();
}
//close connection
$database->close();
}
else{
$_SESSION['message'] = 'Fill up add form first';
}
header('location: index.php');
?>
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.
<?php
include_once('connection.php');
if(isset($_POST['edit'])){ $database = new Connection();
$db = $database->open();
try{
$id = $_GET['id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
//if-else statement in executing our query
$_SESSION['message'] = ( $db->exec($sql) ) ?
'Member updated successfully' : 'Something went wrong. Cannot update member';
}
catch(PDOException $e){
$_SESSION['message'] = $e->getMessage();
}
//close connection
$database->close();
}
else{
$_SESSION['message'] = 'Fill up edit form first';
}
header('location: index.php');
?>
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.
<?php
include_once('connection.php');
$database = new Connection();
$db = $database->open();
try{
$sql = "DELETE FROM members WHERE id = '".$_GET['id']."'";
//if-else statement in executing our query
$_SESSION['message'] = ( $db->exec($sql) ) ?
'Member deleted successfully' : 'Something went wrong. Cannot delete member'; }
catch(PDOException $e){
$_SESSION['message'] = $e->getMessage();
}
//close connection
$database->close();
}
else{
$_SESSION['message'] = 'Select member to delete first';
}
header('location: index.php');
?>
That ends this tutorial. I hope this will help you and you have learn something usefull with this tutorial. You can download my sample source code that I have created for this tutorial by clicking the Download button below. Explore more in this website for more tutorials and Free project source codes.
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.
