In this tutorial, I am going to show you how you can create a simple CRUD in Bootstrap 4 in PHP with search functionality.
What is CRUD?
- Create a simple database table.
- Reading / Fetch a database table data.
- Update database table data.
- Delete database table data.
Into your main folder create below folders and files.
- include [Folder].
- add-users.php [file].
- config.php [file].
- browse-users.php [file].
- edit-users.php [file].
Download the Database class from here and paste into include folder.
How to set up the config file.
Open the config.php file and paste the below code in it.
Remember include config file in all PHP files with the help of include_once() function. It is the main file that holds a database connection. So without DB connection, you can not perform any DB operation into a database.
<?php
include_once('include/Database.php');
define('SS_DB_NAME', 'test');
define('SS_DB_USER', 'root');
define('SS_DB_PASSWORD', '');
define('SS_DB_HOST', 'localhost');
$dsn = "mysql:dbname=".SS_DB_NAME.";host=".SS_DB_HOST."";
$pdo = "";
try {
$pdo = new PDO($dsn, SS_DB_USER, SS_DB_PASSWORD);
}catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
$db = new Database($pdo);
?>
How to add user data into DB?
Create a database table with the name of the users. Consider the below query to create a users table into your DB.
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(64) NOT NULL ,
`useremail` VARCHAR(128) NOT NULL ,
`userphone` VARCHAR(24) NOT NULL ,
`dt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ,
PRIMARY KEY (`id`)
) ENGINE = MyISAM;
After creating a table you need to create a form into your add-users.php file. Open your add-users.php file and paste the below code into the container div.
Remember: I am using Bootstrap 4 as a design framework. The below snippet is a bootstrap 4 code.
<form method="post">
<div class="form-group">
<label>User Name <span class="text-danger">*</span></label>
<input type="text" name="username" id="username" class="form-control" placeholder="Enter user name" required>
</div>
<div class="form-group">
<label>User Email <span class="text-danger">*</span></label>
<input type="email" name="useremail" id="useremail" class="form-control" placeholder="Enter user email" required>
</div>
<div class="form-group">
<label>User Phone <span class="text-danger">*</span></label>
<input type="tel" name="userphone" id="userphone" class="form-control" placeholder="Enter user phone" required>
</div>
<div class="form-group">
<button type="submit" name="submit" value="submit" id="submit" class="btn btn-primary"><i class="fa fa-fw fa-plus-circle"></i> Add User</button>
</div>
</form>
Insert into the database table.
After creating a form you need to submit form data into your table. Copy and paste below code before or above <form> tag.
<?php
if(isset($_REQUEST['submit']) and $_REQUEST['submit']!=""){
extract($_REQUEST);
if($username==""){
header('location:'.$_SERVER['PHP_SELF'].'?msg=un');
exit;
}elseif($useremail==""){
header('location:'.$_SERVER['PHP_SELF'].'?msg=ue');
exit;
}elseif($userphone==""){
header('location:'.$_SERVER['PHP_SELF'].'?msg=up');
exit;
}else{
$data = array(
'username'=>$username,
'useremail'=>$useremail,
'userphone'=>$userphone,
);
$insert = $db->insert('users',$data);
if($insert){
header('location:'.$_SERVER['PHP_SELF'].'?msg=ras');
exit;
}else{
header('location:'.$_SERVER['PHP_SELF'].'?msg=rna');
exit;
}
}
}
?>
Below code is a conditional base code if the mandatory fields are empty then the below code will execute. And you can paste below code in anywhere you want to display error or success message.
<?php
if(isset($_REQUEST['msg']) and $_REQUEST['msg']=="un"){
echo '<div class="alert alert-danger"><i class="fa fa-exclamation-triangle"></i> User name is mandatory field!</div>';
}elseif(isset($_REQUEST['msg']) and $_REQUEST['msg']=="ue"){
echo '<div class="alert alert-danger"><i class="fa fa-exclamation-triangle"></i> User email is mandatory field!</div>';
}elseif(isset($_REQUEST['msg']) and $_REQUEST['msg']=="up"){
echo '<div class="alert alert-danger"><i class="fa fa-exclamation-triangle"></i> User phone is mandatory field!</div>';
}elseif(isset($_REQUEST['msg']) and $_REQUEST['msg']=="ras"){
echo '<div class="alert alert-success"><i class="fa fa-thumbs-up"></i> Record added successfully!</div>';
}elseif(isset($_REQUEST['msg']) and $_REQUEST['msg']=="rna"){
echo '<div class="alert alert-danger"><i class="fa fa-exclamation-triangle"></i> Record not added <strong>Please try again!</strong></div>';
}
?>
Create a browse user page with the search.
In this page, there is a 2 part. One is for the search form and the other is for browse users table structure. Open browse-users.php and add config.php at the top of the page. After adding a config.php copy and paste the below code.
PHP code that we use to fetch and search user’s data.
<?php
$condition = '';
if(isset($_REQUEST['username']) and $_REQUEST['username']!=""){
$condition .= ' AND username LIKE "%'.$_REQUEST['username'].'%" ';
}
if(isset($_REQUEST['useremail']) and $_REQUEST['useremail']!=""){
$condition .= ' AND useremail LIKE "%'.$_REQUEST['useremail'].'%" ';
}
if(isset($_REQUEST['userphone']) and $_REQUEST['userphone']!=""){
$condition .= ' AND userphone LIKE "%'.$_REQUEST['userphone'].'%" ';
}
if(isset($_REQUEST['df']) and $_REQUEST['df']!=""){
$condition .= ' AND DATE(dt)>="'.$_REQUEST['df'].'" ';
}
if(isset($_REQUEST['dt']) and $_REQUEST['dt']!=""){
$condition .= ' AND DATE(dt)<="'.$_REQUEST['dt'].'" ';
}
$userData = $db->getAllRecords('users','*',$condition);
?>
To create a search form paste below code into the browse-users.php page.
<div class="col-sm-12">
<h5 class="card-title"><i class="fa fa-fw fa-search"></i> Find User</h5>
<form method="get">
<div class="row">
<div class="col-sm-2">
<div class="form-group">
<label>User Name</label>
<input type="text" name="username" id="username" class="form-control" value="<?php echo isset($_REQUEST['username'])?$_REQUEST['username']:''?>" placeholder="Enter user name">
</div>
</div>
<div class="col-sm-2">
<div class="form-group">
<label>User Email</label>
<input type="email" name="useremail" id="useremail" class="form-control" value="<?php echo isset($_REQUEST['useremail'])?$_REQUEST['useremail']:''?>" placeholder="Enter user email">
</div>
</div>
<div class="col-sm-2">
<div class="form-group">
<label>User Phone</label>
<input type="tel" class="tel form-control" name="userphone" id="userphone" x-autocompletetype="tel" value="<?php echo isset($_REQUEST['userphone'])?$_REQUEST['userphone']:''?>" placeholder="Enter user phone">
</div>
</div>
<div class="col-sm-4">
<div class="form-group">
<label>Date</label>
<div class="input-group">
<input type="text" class="fromDate form-control" name="df" id="df" value="<?php echo isset($_REQUEST['df'])?$_REQUEST['df']:''?>" placeholder="Enter from date">
<div class="input-group-prepend"><span class="input-group-text">-</span></div>
<input type="text" class="toDate form-control" name="dt" id="dt" value="<?php echo isset($_REQUEST['dt'])?$_REQUEST['dt']:''?>" placeholder="Enter to date">
<div class="input-group-append"><span class="input-group-text"><a href="javascript:;" onClick="$('#df,#dt').val('');"><i class="fa fa-fw fa-sync"></i></a></span></div>
</div>
</div>
</div>
<div class="clearfix"></div>
<div class="col-sm-3">
<div class="form-group">
<label> </label>
<div>
<button type="submit" name="submit" value="search" id="submit" class="btn btn-primary"><i class="fa fa-fw fa-search"></i> Search</button>
<a href="<?php echo $_SERVER['PHP_SELF'];?>" class="btn btn-danger"><i class="fa fa-fw fa-sync"></i> Clear</a>
</div>
</div>
</div>
</div>
</form>
</div>
After the search form creates a table structure needed to display user’s data. Copy and paste below code after search form.
<table class="table table-striped table-bordered">
<thead>
<tr class="bg-primary text-white">
<th>Sr#</th>
<th>User Name</th>
<th>User Email</th>
<th>User Phone</th>
<th class="text-center">Record Date</th>
<th class="text-center">Action</th>
</tr>
</thead>
<tbody>
<?php
if(count($userData)>0){
$s = '';
foreach($userData as $val){
$s++;
?>
<tr>
<td><?php echo $s;?></td>
<td><?php echo $val['username'];?></td>
<td><?php echo $val['useremail'];?></td>
<td><?php echo $val['userphone'];?></td>
<td align="center"><?php echo date('Y-m-d',strtotime($val['dt']));?></td>
<td align="center">
<a href="edit-users.php?editId=<?php echo $val['id'];?>" class="text-primary"><i class="fa fa-fw fa-edit"></i> Edit</a> |
<a href="delete.php?delId=<?php echo $val['id'];?>" class="text-danger" onClick="return confirm('Are you sure to delete this user?');"><i class="fa fa-fw fa-trash"></i> Delete</a>
</td>
</tr>
<?php
}
}else{
?>
<tr><td colspan="6" align="center">No Record(s) Found!</td></tr>
<?php } ?>
</tbody>
</table>
For edit users, you need to open the edit-users.php file and add below PHP code in it which is used to fetch and update user’s data.
<?php
if(isset($_REQUEST['editId']) and $_REQUEST['editId']!=""){
$row = $db->getAllRecords('users','*',' AND id="'.$_REQUEST['editId'].'"');
}
if(isset($_REQUEST['submit']) and $_REQUEST['submit']!=""){
extract($_REQUEST);
if($username==""){
header('location:'.$_SERVER['PHP_SELF'].'?msg=un&editId='.$_REQUEST['editId']);
exit;
}elseif($useremail==""){
header('location:'.$_SERVER['PHP_SELF'].'?msg=ue&editId='.$_REQUEST['editId']);
exit;
}elseif($userphone==""){
header('location:'.$_SERVER['PHP_SELF'].'?msg=up&editId='.$_REQUEST['editId']);
exit;
}
$data = array(
'username'=>$username,
'useremail'=>$useremail,
'userphone'=>$userphone,
);
$update = $db->update('users',$data,array('id'=>$editId));
if($update){
header('location: browse-users.php?msg=rus');
exit;
}else{
header('location: browse-users.php?msg=rnu');
exit;
}
}
?>
To create an edit form you need to copy and page the below code into the edit-users.php file.
<div class="col-sm-6">
<h5 class="card-title">Fields with <span class="text-danger">*</span> are mandatory!</h5>
<form method="post">
<div class="form-group">
<label>User Name <span class="text-danger">*</span></label>
<input type="text" name="username" id="username" class="form-control" value="<?php echo $row[0]['username']; ?>" placeholder="Enter user name" required>
</div>
<div class="form-group">
<label>User Email <span class="text-danger">*</span></label>
<input type="email" name="useremail" id="useremail" class="form-control" value="<?php echo $row[0]['useremail']; ?>" placeholder="Enter user email" required>
</div>
<div class="form-group">
<label>User Phone <span class="text-danger">*</span></label>
<input type="tel" name="userphone" id="userphone" maxlength="12" class="form-control" value="<?php echo $row[0]['userphone']; ?>" placeholder="Enter user phone" required>
</div>
<div class="form-group">
<input type="hidden" name="editId" id="editId" value="<?php echo $_REQUEST['editId']?>">
<button type="submit" name="submit" value="submit" id="submit" class="btn btn-primary"><i class="fa fa-fw fa-edit"></i> Update User</button>
</div>
</form>
</div>
For delete record, you just need to paste below code into delete.php file.
<?php
include_once('config.php');
if(isset($_REQUEST['delId']) and $_REQUEST['delId']!=""){
$db->delete('users',array('id'=>$_REQUEST['delId']));
header('location: browse-users.php?msg=rds');
exit;
}
?>
If you want to add a date search then you must add the date picker in this example I am using JQuery UI date picker.
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.0/themes/smoothness/jquery-ui.css" type="text/css">
<script src="https://code.jquery.com/ui/1.12.0/jquery-ui.min.js" integrity="sha256-eGE6blurk5sHj+rmkfsGYeKyZx3M4bG+ZlFyA7Kns7E=" crossorigin="anonymous"></script>
