Table of Contents
Introduction
हेलो दोस्तों आज हम इस post में PHP crud operation के बारेमे जानेगे. PHP का इस्तेमाल करके data को database table
में कैसे insert, view, update, delete करा सकते है वह हम इस post में details से example के साथ समझेंगे!. database में data को insert करने के लिए INSERT INTO command और data को view करने के लिए SELECT command और data को update करने के लिए UPDATE command का और data को database से delete करने के लिए DELETE command का इस्तेमाल होता है!.
php crud operation
php crud operation perform करने के लिए हम कोन कोन सी File create करने की जरुरत है!. file structure में जैसे की
1) dbconn.php file => ये file हमारे database के साथ connection create करने के लिए है!.
2) insert.php file => ये file form data को get करने के बाद में database के sepecific table में data को insert करने के लिए है!.
3) view.php file => ये file table के data को databse से display करने के लिए है!.
4) update.php file => ये file table के specific data को update करने के लिए है!.
5) delete.php file => ये file table से data को delete करने के लिए है!.
Database connection
Database का conneciton करने से पहले हम ये समाज लेते है की localhost पे database को कैसे create कर सकते है। सबसे पहले तो हमे हमारे machine यानि laptop/computer में localhost server WAMP server software को install कर लेना है। उसके बाद WAMP start होने के बाद में browser के url में http://localhost/phpmyadmin रन करके login कर लेना है!.
phpmyadmin में login होने के बाद में database को create कर लेना है!. database create करने के लिए SQL tab पे click करके CREATE DATABASE crud; command run karke database को create कर सकते है!.
database create होने के बाद में database में table create करने के लिए ये command को run करना है!.
CREATE TABLE IF NOT EXISTS `register` ( `id` INT AUTO_INCREMENT primary key NOT NULL, `username` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `phone` char(11) NOT NULL, `password` varchar(255) NOT NULL, `image` varchar(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP , updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
dbconn.php file
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "crud";
$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
} else{
//echo "Connected successfully";
}
?>
Insert data in database table
Database में data को insert करने के लिए insert.php file को create किया है!.HTML form के जरिये Data को database में insert करने के लिए हमें एक HTML form की अवसक्ता होगी!.
तो आप निचे code में देख सकते है HTML form
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title> CRUD operation in php </title>
<link rel="stylesheet" href="../style.css">
<link rel="stylesheet" href="style.css">
<!-- icons CDN Link -->
<link href='https://unpkg.com/boxicons@2.0.7/css/boxicons.min.css' rel='stylesheet'>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.3/jquery.validate.min.js"></script>
<script>
$(document).ready(function () {
$(".form_data").validate({
rules: {
username: {
required: true
},
email: {
required: true,
email: true
},
password: "required",
confirm_password: {
equalTo: "#floatingPassword"
},
phone:{
required: true
},
image: {
required: true,
extension: "png|jpeg|jpg",
filesize: 1048576
}
},
messages: {
username: {
required: "this field is required"
},
email: {
required: "We need your email address to contact you",
email: "Your email address must be in the format of name@domain.com"
},
phone: {
required: "this field is required"
},
image: "File must be JPEG or PNG, less than 1MB"
}
});
});
</script>
<style>
.error{
color: red;
top: 34px !important;
padding: 25px 0 !important;
}
.mb-3 {
margin-bottom: 2rem!important;
}
</style>
</head>
<body>
<section class="home-section">
<div class="container">
<div class="row">
<div class="col-lg-10 col-xl-9 mx-auto">
<div class="card flex-row my-5 border-0 shadow rounded-3 overflow-hidden">
<div class="card-img-left d-none d-md-flex">
<!-- Background image for card set in CSS! -->
</div>
<div class="card-body p-4 p-sm-5">
<h5 class="card-title text-center mb-5 fw-light fs-5">Register</h5>
<form action="#" method="POST" class="form_data" enctype="multipart/form-data">
<div class="form-floating mb-3">
<label for="floatingInputUsername">Username</label>
<input type="text" class="form-control" name="username" id="floatingInputUsername" placeholder="myusername" required autofocus>
</div>
<div class="form-floating mb-3">
<label for="floatingInputEmail">Email address</label>
<input type="email" class="form-control" name="email" id="floatingInputEmail" placeholder="name@example.com">
</div>
<div class="form-floating mb-3">
<label for="floatingInputEmail">Phone Number</label>
<input type="text" class="form-control" name="phone" id="floatingInputphone" placeholder="Enter your phone ">
</div>
<hr>
<div class="form-floating mb-3">
<label for="floatingPassword">Password</label>
<input type="password" class="form-control" name="password" id="floatingPassword" placeholder="Password">
</div>
<div class="form-floating mb-3">
<label for="floatingPasswordConfirm">Confirm Password</label>
<input type="password" name="confirm_password" class="form-control" id="floatingPasswordConfirm" placeholder="Confirm Password">
</div>
<div class="form-floating mb-3">
<label for="file">Upload Your Image</label>
<input type="file" name="image" class="form-control" id="file">
</div>
<div class="d-grid mb-2">
<input name="submit" class="btn btn-lg btn-primary btn-login fw-bold text-uppercase" type="submit" value="Register">
</div>
<a class="d-block text-center mt-2 small" href="/login.php">Have an account? Sign In</a>
</form>
</div>
</div>
</div>
</div>
</div>
</section>
</body>
</html>
अब जब user HTML form से data fill करके submit करता है तो उस data को server side get करने के बाद में database में insert करवाया जाता है!.
<?php
include('inc/dbconn.php');
if( isset( $_POST['submit'] ) ){
$username = $_POST['username'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$password = $_POST['password'];
$file_name = $_FILES['image']['name'];
$temp_name = $_FILES['image']['tmp_name'];
$allow = array("jpg", "jpeg", "gif", "png");
$todir = 'uploads/';
if( $temp_name ){
$info = explode('.', strtolower( $_FILES['image']['name']) );
if ( in_array( end($info), $allow) ) {
if ( move_uploaded_file( $_FILES['image']['tmp_name'], $todir . basename($_FILES['image']['name'] ) ) ) {
//echo "Image uploaded successfully!";
}
} else {
//echo "Image Not uploaded successfully!";
}
}
$created_at = date("Y-m-d H:i:s");
$updated_at = date("Y-m-d H:i:s");
$inser_qury = "INSERT INTO `register` (`username`, `email`, `phone`, `password`,`image`,`created_at`,`updated_at`) VALUES ('".$username."', '".$email."', '".$phone."', '".$password."','".$file_name."','".$created_at."','".$updated_at."')";
$select_res = mysqli_query($conn, $inser_qury);
if( $select_res ){
echo "<script>alert('Data inserted successfully!');</script>";
//echo "Data inserted successfully!";
} else{
echo "<script>alert('Data inserted Unsuccessfully!');</script>";
}
}
?>
Insert data fullsource code
<?php
include('inc/dbconn.php');
if( isset( $_POST['submit'] ) ){
$username = $_POST['username'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$password = $_POST['password'];
$file_name = $_FILES['image']['name'];
$temp_name = $_FILES['image']['tmp_name'];
$allow = array("jpg", "jpeg", "gif", "png");
$todir = 'uploads/';
if( $temp_name ){
$info = explode('.', strtolower( $_FILES['image']['name']) );
if ( in_array( end($info), $allow) ) {
if ( move_uploaded_file( $_FILES['image']['tmp_name'], $todir . basename($_FILES['image']['name'] ) ) ) {
//echo "Image uploaded successfully!";
}
} else {
//echo "Image Not uploaded successfully!";
}
}
$created_at = date("Y-m-d H:i:s");
$updated_at = date("Y-m-d H:i:s");
$inser_qury = "INSERT INTO `register` (`username`, `email`, `phone`, `password`,`image`,`created_at`,`updated_at`) VALUES ('".$username."', '".$email."', '".$phone."', '".$password."','".$file_name."','".$created_at."','".$updated_at."')";
$select_res = mysqli_query($conn, $inser_qury);
if( $select_res ){
echo "<script>alert('Data inserted successfully!');</script>";
//echo "Data inserted successfully!";
}else{
echo "<script>alert('Data inserted Unsuccessfully!');</script>";
}
}
include('header.php');
?>
<section class="home-section">
<div class="container">
<div class="row">
<div class="col-lg-10 col-xl-9 mx-auto">
<div class="card flex-row my-5 border-0 shadow rounded-3 overflow-hidden">
<div class="card-img-left d-none d-md-flex">
<!-- Background image for card set in CSS! -->
</div>
<div class="card-body p-4 p-sm-5">
<h5 class="card-title text-center mb-5 fw-light fs-5">Register</h5>
<form action="#" method="POST" class="form_data" enctype="multipart/form-data">
<div class="form-floating mb-3">
<label for="floatingInputUsername">Username</label>
<input type="text" class="form-control" name="username" id="floatingInputUsername" placeholder="myusername" required autofocus>
</div>
<div class="form-floating mb-3">
<label for="floatingInputEmail">Email address</label>
<input type="email" class="form-control" name="email" id="floatingInputEmail" placeholder="name@example.com">
</div>
<div class="form-floating mb-3">
<label for="floatingInputEmail">Phone Number</label>
<input type="text" class="form-control" name="phone" id="floatingInputphone" placeholder="Enter your phone ">
</div>
<hr>
<div class="form-floating mb-3">
<label for="floatingPassword">Password</label>
<input type="password" class="form-control" name="password" id="floatingPassword" placeholder="Password">
</div>
<div class="form-floating mb-3">
<label for="floatingPasswordConfirm">Confirm Password</label>
<input type="password" name="confirm_password" class="form-control" id="floatingPasswordConfirm" placeholder="Confirm Password">
</div>
<div class="form-floating mb-3">
<label for="file">Upload Your Image</label>
<input type="file" name="image" class="form-control" id="file">
</div>
<div class="d-grid mb-2">
<input name="submit" class="btn btn-lg btn-primary btn-login fw-bold text-uppercase" type="submit" value="Register">
</div>
<a class="d-block text-center mt-2 small" href="/login.php">Have an account? Sign In</a>
</form>
</div>
</div>
</div>
</div>
</div>
</section>
</body>
</html>
Fetch the Data from database
Database से data को fetch करने के लिए index.php file है!.
index.php file
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title> CRUD operation in php </title>
<link rel="stylesheet" href="../style.css">
<link rel="stylesheet" href="style.css">
<!-- icons CDN Link -->
<link href='https://unpkg.com/boxicons@2.0.7/css/boxicons.min.css' rel='stylesheet'>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.3/jquery.validate.min.js"></script>
</head>
<body>
<section class="home-section">
<div class="home-content">
<?php
include('inc/dbconn.php');
$select_query = "SELECT * FROM `register`";
$select_res = mysqli_query($conn, $select_query);
?>
<table class="table table-bordered">
<thead>
<th>Id</th>
<th>Username</th>
<th>Email</th>
<th>Phone</th>
<th>Image</th>
<th>Operation</th>
</thead>
<tbody>
<?php
if (mysqli_num_rows($select_res) > 0) {
while($row = mysqli_fetch_assoc($select_res)) {?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['username']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['phone']; ?></td>
<td>
<?php
if( $row['image'] ){
echo "<img src='uploads/". $row['image'] ."'' width='150px;' height=''150px;>";
}else{ }
?>
</td>
<td>
<button class="btn btn-primary"><a style="color:#fff;" href="view.php/?ID=<?php echo $row['id'];?>">View</a></button>
</td>
<td><button class="btn btn-success"><a style="color:#fff;" href="update.php/?ID=<?php echo $row['id']; ?>">Update</a></button></td>
<td><button class="btn btn-danger delete_data"><a style="color:#fff;" href="delete.php/?ID=<?php echo $row['id']; ?>">Delete</a></button></td>
</tr>
<?php }
} else {
echo "0 results";
}
?>
</tbody>
</table>
</div>
</section>
<script>
$(".delete_data").click(function(){
if (!confirm("Do you want to delete")){
return false;
}
});
</script>
</body>
</html>
Update data in database
Database के data को update करने के लिए update.php file create की गई है!.
update.php file
<html lang="en" dir="ltr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title> CRUD operation in php </title>
<link rel="stylesheet" href="../style.css">
<link rel="stylesheet" href="style.css">
<!-- icons CDN Link -->
<link href='https://unpkg.com/boxicons@2.0.7/css/boxicons.min.css' rel='stylesheet'>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.3/jquery.validate.min.js"></script>
</head>
<body>
<?php
include('inc/dbconn.php');
if( isset( $_GET['ID'] ) ){
$select_query = "SELECT * FROM `register` WHERE id='".$_GET['ID']."' ";
$select_res = mysqli_query($conn, $select_query);
if (mysqli_num_rows($select_res) > 0) {
$row = mysqli_fetch_assoc($select_res);
}
?>
<section class="home-section">
<div class="container">
<div class="row">
<div class="col-lg-10 col-xl-9 mx-auto">
<div class="card flex-row my-5 border-0 shadow rounded-3 overflow-hidden">
<div class="card-img-left d-none d-md-flex">
<!-- Background image for card set in CSS! -->
</div>
<div class="card-body p-4 p-sm-5">
<h5 class="card-title text-center mb-5 fw-light fs-5">User Data Update</h5>
<form action="#" method="POST" class="updateform_data" enctype="multipart/form-data">
<div class="form-floating mb-3">
<label for="floatingInputUsername">Username</label>
<input type="text" class="form-control" name="username" value="<?php echo $row['username'] ?>" placeholder="myusername" required autofocus>
</div>
<div class="form-floating mb-3">
<label for="floatingInputEmail">Phone Number</label>
<input type="text" class="form-control" name="phone" value="<?php echo $row['phone'] ?>"placeholder="Enter your phone ">
</div>
<hr>
<div class="form-floating mb-3">
<label for="file">Upload Your Image</label>
<input type="file" name="image" class="form-control" value="" id="file">
<input type="hidden" name="old_img" value="<?php echo $row['image']; ?>">
<div>
<?php
if( $row['image'] ){
echo "<img src='../uploads/". $row['image'] ."'' width='250px;' height=''250px;>";
}
?>
</div>
</div>
<div class="d-grid mb-2">
<input name="submit" class="btn btn-lg btn-primary btn-login fw-bold text-uppercase" type="submit" value="Update">
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</section>
</body>
</html>
Database के data को कैसे update कर sakte है!. app निचे code में देख सकते है!.
<?php
include('inc/dbconn.php');
session_start();
if( !isset ( $_SESSION["Login"] ) ){
header("Location: login.php");
exit();
}
if( isset( $_POST['submit'] ) ){
$id = $_GET['ID'];
$username = $_POST['username'];
$phone = $_POST['phone'];
if( !empty( $_FILES['image']['name'] ) ){
$file_name = $_FILES['image']['name'];
$temp_name = $_FILES['image']['tmp_name'];
}else{
$file_name = $_POST['old_img'];
}
$allow = array("jpg", "jpeg", "gif", "png");
$todir = 'uploads/';
if( $file_name ){
$info = explode('.', strtolower( $_FILES['image']['name']) );
if ( in_array( end($info), $allow) ) {
if( !file_exists( $todir.'/'.$file_name ) ) {
if ( move_uploaded_file( $_FILES['image']['tmp_name'], $todir . basename($_FILES['image']['name'] ) ) ) {
//echo "Image uploaded successfully!";
}
}
} else {
//echo "Image Not uploaded successfully!";
}
}
$updated_at = date("Y-m-d H:i:s");
$update_qury = "UPDATE `register` SET `username` ='".$username."', `phone`= '".$phone."',`image`='".$file_name."', `updated_at`='".$updated_at."' WHERE id = '".$id."' ";
// $inser_qury = "INSERT INTO `register` (`username`, `email`, `phone`, `password`,`image`,`created_at`,`updated_at`) VALUES ('".$username."', '".$email."', '".$phone."', '".$password."','".$file_name."','".$created_at."','".$updated_at."')";
$query_res = mysqli_query($conn, $update_qury);
if( $query_res ){
echo "Data Update successfully!";
}
}// end of if issert funcion here
?>
Update data fullsource code
<?php
include('inc/dbconn.php');
session_start();
if( !isset ( $_SESSION["Login"] ) ){
header("Location: login.php");
exit();
}
if( isset( $_POST['submit'] ) ){
$id = $_GET['ID'];
$username = $_POST['username'];
$phone = $_POST['phone'];
if( !empty( $_FILES['image']['name'] ) ){
$file_name = $_FILES['image']['name'];
$temp_name = $_FILES['image']['tmp_name'];
}else{
$file_name = $_POST['old_img'];
}
$allow = array("jpg", "jpeg", "gif", "png");
$todir = 'uploads/';
if( $file_name ){
$info = explode('.', strtolower( $_FILES['image']['name']) );
if ( in_array( end($info), $allow) ) {
if( !file_exists( $todir.'/'.$file_name ) ) {
if ( move_uploaded_file( $_FILES['image']['tmp_name'], $todir . basename($_FILES['image']['name'] ) ) ) {
//echo "Image uploaded successfully!";
}
}
} else {
//echo "Image Not uploaded successfully!";
}
}
$updated_at = date("Y-m-d H:i:s");
$update_qury = "UPDATE `register` SET `username` ='".$username."', `phone`= '".$phone."',`image`='".$file_name."', `updated_at`='".$updated_at."' WHERE id = '".$id."' ";
// $inser_qury = "INSERT INTO `register` (`username`, `email`, `phone`, `password`,`image`,`created_at`,`updated_at`) VALUES ('".$username."', '".$email."', '".$phone."', '".$password."','".$file_name."','".$created_at."','".$updated_at."')";
$query_res = mysqli_query($conn, $update_qury);
if( $query_res ){
echo "Data Update successfully!";
}
}// end of if issert funcion here
include('header.php');
if( isset( $_GET['ID'] ) ){
$select_query = "SELECT * FROM `register` WHERE id='".$_GET['ID']."' ";
$select_res = mysqli_query($conn, $select_query);
if (mysqli_num_rows($select_res) > 0) {
$row = mysqli_fetch_assoc($select_res);
?>
<section class="home-section">
<div class="container">
<div class="row">
<div class="col-lg-10 col-xl-9 mx-auto">
<div class="card flex-row my-5 border-0 shadow rounded-3 overflow-hidden">
<div class="card-img-left d-none d-md-flex">
<!-- Background image for card set in CSS! -->
</div>
<div class="card-body p-4 p-sm-5">
<h5 class="card-title text-center mb-5 fw-light fs-5">User Data Update</h5>
<form action="#" method="POST" class="updateform_data" enctype="multipart/form-data">
<div class="form-floating mb-3">
<label for="floatingInputUsername">Username</label>
<input type="text" class="form-control" name="username" value="<?php echo $row['username'] ?>" placeholder="myusername" required autofocus>
</div>
<div class="form-floating mb-3">
<label for="floatingInputEmail">Phone Number</label>
<input type="text" class="form-control" name="phone" value="<?php echo $row['phone'] ?>"placeholder="Enter your phone ">
</div>
<hr>
<div class="form-floating mb-3">
<label for="file">Upload Your Image</label>
<input type="file" name="image" class="form-control" value="" id="file">
<input type="hidden" name="old_img" value="<?php echo $row['image']; ?>">
<div>
<?php
if( $row['image'] ){
echo "<img src='../uploads/". $row['image'] ."'' width='250px;' height=''250px;>";
}
?>
</div>
</div>
<div class="d-grid mb-2">
<input name="submit" class="btn btn-lg btn-primary btn-login fw-bold text-uppercase" type="submit" value="Update">
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</section>
<?php
}
}
?>
Delete a data from the database
Database से data को delete करने के लिए delete.php file है!.
delete.php file
<?php
include('inc/dbconn.php');
session_start();
if( !isset ( $_SESSION["Login"] ) ){
header("Location: login.php");
exit();
}
include('header.php');
include('sidebar.php');
if( isset( $_GET['ID'] ) ){
$delete_query = "DELETE FROM `register` WHERE id='".$_GET['ID']."'";
$delete_res = mysqli_query($conn, $delete_query);
if( $delete_res ){
echo "Record delete successfully!";
}
header("Location: http://localhost/Mytest/crud/dashboard.php");
}
?>
