In this tutorial, we will tackle about creating a Simple Web Application with CRUD Operation and preventing duplication input value. We will be using PHP Language as the back-end and MySQL Database. The Duplication Validation that we'll be creating, is a real-time or live function using jQuery, and by using Ajax Request, data validation will be automatically triggers without leaving the page or submitting the form yet.
In the simple application that we will be creating, I will use Bootstrap v5 as the CSS Framework and jQuery to execute our Ajax Requests.
The goal of the application that we'll create is a simple Product Listing that have a CRUD (Create, Read, Edit, and Delete) Operation and Duplicate Validation for the code field. The Code Field will serve as unique code of the products.
Getting Started:
Download and Install any local web servers that has an Apache WebServer and MySQL Database Server such as XAMPP.
Download and Compile the following libraries in your source code folder.
Before you continue below, please make sure that your Apache and MySQL is already running in your system.
Creating the Database
Open your PHPMyAdmin in a browser and create a new database naming dummy_db. Next, navigate to the SQL Tab/Page in your newly created database. Copy the SQL Script below and paste in the provided text field.
Creating the Database Connection
Open your favorite text-editor such as notepad++ or sublime text editors. Create a new PHP File naming db_connect.php and copy/paste the PHP Script Below. Save the file in your source code folder where your downloaded libraries are compiled.
<?php
// Database Host
$host = 'localhost';
// Database Username
$username = 'root';
// Database Password
$password = '';
// Selected Database Name
$dbName = 'dummy_db';
$conn= new mysqli($host, $username, $password,$dbName);
if(!$conn){
die("Database Connection Error: ".$conn->error); }
Creating the Interfaces
Next, we will be creating the user interfaces. Save the following files using the filename provided before each scripts.
index.php
This is the file which contains our main template of the application.
<?php
session_start();
require_once('db_connect.php');
$page = isset($_GET['page']) ? $_GET['page'] : 'home';
?>
<!DOCTYPE html>
<meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title><?php echo ucwords(str_replace('_',' ',$page)) ?></title> <link rel="stylesheet" href="Font-Awesome-master/css/all.min.css"> <link rel="stylesheet" href="css/bootstrap.min.css"> <link rel="stylesheet" href="./css/custom.css"> <main>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark bg-gradient" id="topNavBar">
<a class="navbar-brand fw-bolder" href="https://sourcecodester.com"> SourceCodester
<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> <div class="collapse navbar-collapse" id="navbarNav"> <a class="nav-link <?php echo $page == "home" ? "active" :"" ?>" href="./?page=home">Home
</a> <a class="nav-link <?php echo $page == "product_list" ? "active" :"" ?>" href="./?page=product_list">Product List
</a> <!-- <a href="./admin" id="login-btn" class="text-decoration-none text-light fw-bolder">Admin Panel</a> -->
</nav>
<div class="container py-3" id="page-container"> <?php
if(isset($_SESSION['flashdata'])):
?>
<div class="dynamic_alert alert alert-<?php echo $_SESSION['flashdata']['type'] ?>">
<div class="float-end"><a href="javascript:void(0)" class="text-dark text-decoration-none" onclick="$(this).closest('.dynamic_alert').hide('slow').remove()">x
</a></div> <?php echo $_SESSION['flashdata']['msg'] ?>
<?php unset($_SESSION['flashdata']) ?>
<?php endif; ?>
<div class="content py-5"> <?php
include $page.'.php';
?>
</main>
<?php if(isset($conn)) $conn->close(); ?>
home.php
This is the default page of the application when browsing the project. This contains the Add New Product Form.
<div class="content py-3"> <h3 class="text-center fw-bolder">Add New Product
</h3> <center><hr class="bg-primary mx-1 opacity-100" style="width:5em"></center> <div class="row justify-content-center"> <div class="card shadow col-md-8"> <div class="container-fluid py3"> <form action="save_product.php" method="POST"> <div class="col-md-12 form-group"> <label for="code" class="control-label text-primary">Code
</label> <input type="text" max="50" name="code" id="code" autofocus class="form-control form-control-sm" required> <div class="col-md-12 form-group"> <label for="product_name" class="control-label text-primary">Product Name
</label> <input type="text" name="product_name" id="product_name" class="form-control form-control-sm" required> <div class="col-md-12 form-group"> <label for="description" class="control-label text-primary">Description
</label> <textarea rows="3" name="description" id="description" class="form-control form-control-sm" required></textarea> <div class="col-md-12 form-group"> <label for="price" class="control-label text-primary">Price
</label> <input type="number" name="price" id="price" class="form-control form-control-sm text-end" required> <div class="form-group col-md-12 text-center mt-4"> <button class="btn btn-primary rounded-0 col-3" id="submit">Save
</button> <button class="btn btn-secondary rounded-0 col-3" type="reset">Cancel
</button> $(function(){
var validation_el = $('
<div>')
validation_el.addClass('validation-err alert alert-danger my-2')
validation_el.hide()
$('input[name="code"]').on('input',function(){
var code = $(this).val()
$(this).removeClass("border-danger border-success")
$(this).siblings(".validation-err").remove();
var err_el = validation_el.clone()
if(code == '')
return false;
$.ajax({
url:"validate.php",
method:'POST',
data:{code:code},
dataType:'json',
error:err=>{
console.error(err)
alert("An error occured while validating the data")
},
success:function(resp){
if(Object.keys(resp).length > 0 && resp.field_name == 'code'){
err_el.text(resp.msg)
$('input[name="code"]').addClass('border-danger')
$('input[name="code"]').after(err_el)
err_el.show('slideDown')
$('#submit').attr('disabled',true)
}else{
$('input[name="code"]').addClass('border-success')
$('#submit').attr('disabled',false)
}
}
})
})
})
</script>
product_list.php
This file contains the product list interface. This includes the query that fetches the product data from the database.
<div class="content py-3"> <h3 class="text-center fw-bolder">List of Products
</h3> <center><hr class="bg-primary mx-1 opacity-100" style="width:5em"></center> <div class="card card-primary bg-light border-top-1 border-primary shadow rounded-0"> <div class="card-body rounded-0"> <table class="table table-bordered table-striped"> <th class="py-1 px-2">#
</th> <th class="py-1 px-2">Code
</th> <th class="py-1 px-2">Name
</th> <th class="py-1 px-2">Description
</th> <th class="py-1 px-2">Price
</th> <th class="py-1 px-2">Action
</th> <?php
$i=1;
$products = $conn->query("SELECT * FROM `product_list` order by `product_name` asc ");
while($row = $products->fetch_assoc()):
?>
<td class="py-1 px-2 text-center"><?= $i++; ?></td> <td class="py-1 px-2"><?= $row['code'] ?></td> <td class="py-1 px-2"><?= $row['product_name'] ?></td> <td class="py-1 px-2"><?= $row['description'] ?></td> <td class="py-1 px-2"><?= number_format($row['price'],2) ?></td> <td class="text-center py-1 px-2"> <a href="./?page=edit_product&id=<?= $row['id'] ?>" class="btn btn-sm btn-primary rounded-0">Edit
</a> <a href="./?page=delete_product&id=<?= $row['id'] ?>" class="btn btn-sm btn-danger rounded-0" onclick="return confirm('Are you sure to delete this product?')">Delete
</a> <?php endwhile; ?>
edit_product.php
This file contains the PHP and HTML scripts for editing the product details.
<?php
if(isset($_GET['id'])){
$qry = $conn->query("SELECT * FROM `product_list` where id = '{$_GET['id']}'");
if($qry->num_rows > 0){
$result = $qry->fetch_array();
foreach($result as $k => $v){
if(!is_numeric($k)){
$$k = $v;
}
}
}else{
$_SESSION['flashdata']['type']='danger';
$_SESSION['flashdata']['msg']='Unable to load Update Product due to unknown ID.';
echo "
<script>location.href='./?page=product_list';
</script>";
}
}else{
$_SESSION['flashdata']['type']='danger';
$_SESSION['flashdata']['msg']='Unable to load Update Product because ID # is required.';
echo "
<script>location.href='./?page=product_list';
</script>";
}
?>
<div class="content py-3"> <h3 class="text-center fw-bolder">Update Product
</h3> <center><hr class="bg-primary mx-1 opacity-100" style="width:5em"></center> <div class="row justify-content-center"> <div class="card shadow col-md-8"> <div class="container-fluid py3"> <form action="update_product.php" method="POST"> <input type="hidden" name="id" value="<?= $_GET['id'] ?>">
<div class="col-md-12 form-group"> <label for="code" class="control-label text-primary">Code
</label> <input type="text" max="50" name="code" id="code" autofocus class="form-control form-control-sm" value="<?= isset($code) ? $code : "" ?>" required>
<div class="col-md-12 form-group"> <label for="product_name" class="control-label text-primary">Product Name
</label> <input type="text" name="product_name" id="product_name" class="form-control form-control-sm" value="<?= isset($product_name) ? $product_name : "" ?>" required>
<div class="col-md-12 form-group"> <label for="description" class="control-label text-primary">Description
</label> <textarea rows="3" name="description" id="description" class="form-control form-control-sm" required><?= isset($description) ? $description : "" ?></textarea> <div class="col-md-12 form-group"> <label for="price" class="control-label text-primary">Price
</label> <input type="number" step="any" name="price" id="price" class="form-control form-control-sm text-end" value="<?= isset($price) ? $price : "" ?>" required>
<div class="form-group col-md-12 text-center mt-4"> <button class="btn btn-primary rounded-0 col-3">Save
</button> <a class="btn btn-secondary rounded-0 col-3" href="./?page=product_list">Cancel
</a> $(function(){
var validation_el = $('
<div>')
validation_el.addClass('validation-err alert alert-danger my-2')
validation_el.hide()
$('input[name="code"]').on('input',function(){
var code = $(this).val()
$(this).removeClass("border-danger border-success")
$(this).siblings(".validation-err").remove();
var err_el = validation_el.clone()
if(code == '')
return false;
$.ajax({
url:"validate.php",
method:'POST',
data:{code:code,id : '<?= $_GET['id'] ?>'},
dataType:'json',
error:err=>{
console.error(err)
alert("An error occured while validating the data")
},
success:function(resp){
if(Object.keys(resp).length > 0 && resp.field_name == 'code'){
err_el.text(resp.msg)
$('input[name="code"]').addClass('border-danger')
$('input[name="code"]').after(err_el)
err_el.show('slideDown')
}else{
$('input[name="code"]').addClass('border-success')
}
}
})
})
})
</script>
Creating PHP Queries
The following files our the script for saving, updating, and deleting products on the database. Save the following files using the filename provided before each scripts.
validate.php
This PHP Script contains the codes that validates the product code if theres a product with given product code has already exists.
<?php
require_once('db_connect.php');
// extracting POST Variables
$error = [];
$check = $conn->query("SELECT * FROM `product_list` where code = '{$code}'". (isset($id) && $id > 0 ?
" and id != '{$id}' " : "" )); if($check->num_rows > 0){
$error['field_name'] = 'code';
$error['msg']=" Code already exists on the product list";
}
?>
save_product.php
This PHP Script contains the codes that saves the new product created from the Add New Product Form to the database.
<?php
require_once('db_connect.php');
// Escaping post string values
foreach($_POST as $k => $v){
$_POST[$k] = $conn->real_escape_string($v);
}
}
// extracing POST Variables
// Preparing Insert Query
$stmt = $conn->prepare("INSERT INTO `product_list` (`code`,`product_name`,`description`,`price`) VALUES (?, ?, ?, ?)");
if($stmt){
// Binding Insertion Parameters
$stmt->bind_param("sssd", $code, $product_name, $description, $price);
// Executing the query
$save = $stmt->execute();
$_SESSION['flashdata']['type'] = 'success';
$_SESSION['flashdata']['msg'] = 'New Product was successfully added.';
// Closing Query
$stmt->close();
header("location:./?page=product_list"); }else{
$_SESSION['flashdata']['type'] = 'danger';
$_SESSION['flashdata']['msg'] = 'An error occurred while saving the data. Error: '.$conn->error;
header("location:".$_SERVER['HTTP_REFERER']); }
// Closing the database Connection
$conn->close();
?>
update_product.php
This PHP Script contains the code that updates the product details form the Edit Product Form to the Database.
<?php
require_once('db_connect.php');
// Escaping post string values
foreach($_POST as $k => $v){
$_POST[$k] = $conn->real_escape_string($v);
}
}
// extracing POST Variables
// Preparing Insert Query
$stmt = $conn->prepare("UPDATE `product_list` set `code` = ?, `product_name` = ?, `description` = ?, `price` = ? where id = ?");
if($stmt){
// Binding Insertion Parameters
$stmt->bind_param("sssdi", $code, $product_name, $description, $price, $id);
// Executing the query
$save = $stmt->execute();
$_SESSION['flashdata']['type'] = 'success';
$_SESSION['flashdata']['msg'] = 'Product was successfully updated.';
// Closing Query
$stmt->close();
header("location:./?page=product_list"); }else{
$_SESSION['flashdata']['type'] = 'danger';
$_SESSION['flashdata']['msg'] = 'An error occurred while saving the data. Error: '.$conn->error;
header("location:".$_SERVER['HTTP_REFERER']); }
// Closing the database Connection
$conn->close();
?>
delete_product.php
This PHP Script contains the code that removes/deletes the selected product from the database.
<?php
require_once('db_connect.php');
$qry = $conn->query("SELECT * FROM `product_list` where id = '{$_GET['id']}'");
if($qry->num_rows <= 0){
$_SESSION['flashdata']['type']='danger';
$_SESSION['flashdata']['msg']='Unable to DELETE Product due to unknown ID.';
echo "<script>location.href='./?page=product_list';</script>";
}
}else{
$_SESSION['flashdata']['type']='danger';
$_SESSION['flashdata']['msg']='Unable to DELETE Product because ID # is required.';
echo "<script>location.href='./?page=product_list';</script>";
}
// Preparing Insert Query
$stmt = $conn->prepare("DELETE FROM `product_list` where id = ?");
if($stmt){
// Binding Insertion Parameters
$stmt->bind_param("i", $_GET['id']);
// Executing the query
$save = $stmt->execute();
$_SESSION['flashdata']['type'] = 'success';
$_SESSION['flashdata']['msg'] = 'Product was successfully deleted.';
// Closing Query
$stmt->close();
header("location:./?page=product_list"); }else{
$_SESSION['flashdata']['type'] = 'danger';
$_SESSION['flashdata']['msg'] = 'An error occurred while deleting the data. Error: '.$conn->error;
header("location:".$_SERVER['HTTP_REFERER']); }
// Closing the database Connection
$conn->close();
?>
Sample Snapshots
Product Form with Validation Error
Product List
That's it! You can now test the application that we created on your end and see if it is working meets our goal for this tutorial. If ever you have encountered any errors on your end, please review your codes and compare them to the codes that I provided above. I have also provided the working source code for this tutorial. You can download it by clicking the Download Button below this article.
DEMO VIDEO
That's the end of this tutorial. For any questions or queries, you can leave a comment below.
Explore more on this website for more Tutorials and Free Source Codes.
Happy Coding :)
