CRUD Operation in PHP MySQL with Code. Select Insert Update Delete in PHP MySQLi
First of all, I already made a detailed video on it if you haven't watch it.
Below is the video link.
What is CRUD Operation and it is the most important part for any programmers?
CRUD full form is CREATE, READ, UPDATE & DELETE. So it is clear we have to first CREATE a database and a form. Which we can READ here we refer to both programmers and users those who interact with our sites. There must an option where the user can UPDATE the form details and also DELETE it.
All the features if available then that is called a perfect CRUD operation and this is what mostly backend developer deal with.
First, we will see the Database Part. So here is the con.php file.
You have to make sure that your username and password is set or not. I mean I never set any username and password for my localhost. So that is the reason why you can see in mysqli_connect, I have written localhost and root, here the root is the username that is by default if you never set by your own. And there is no need to write password because it's empty by default but if you have one add it after the root.
Also, make sure that your database name must me crudyoutube because below you can see I have written crudyoutube. If you want to write other names simply change the database name on PHPMyAdmin and add that here.
<?php
$con = mysqli_connect('localhost','root');
mysqli_select_db($con,'crudyoutube');
?>
$con = mysqli_connect('localhost','root');
mysqli_select_db($con,'crudyoutube');
?>
Fine, Here is the Insert.php file. Where we are actually inserting the Name and Password.
As you can see all the CDN link are already here in the code below. So no need to take from anywhere else just copy and paste it.
<?php
include 'conn.php';
if(isset($_POST['done'])){
$username = $_POST['username'];
$password = $_POST['password'];
$q = " INSERT INTO `crudtable`(`username`, `password`) VALUES ( '$username', '$password' )";
$query = mysqli_query($con,$q);
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="col-lg-6 m-auto">
<form method="post">
<br><br><div class="card">
<div class="card-header bg-dark">
<h1 class="text-white text-center"> Insert Operation </h1>
</div><br>
<label> Username: </label>
<input type="text" name="username" class="form-control"> <br>
<label> Password: </label>
<input type="text" name="password" class="form-control"> <br>
<button class="btn btn-success" type="submit" name="done"> Submit </button><br>
</div>
</form>
</div>
</body>
</html>
include 'conn.php';
if(isset($_POST['done'])){
$username = $_POST['username'];
$password = $_POST['password'];
$q = " INSERT INTO `crudtable`(`username`, `password`) VALUES ( '$username', '$password' )";
$query = mysqli_query($con,$q);
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="col-lg-6 m-auto">
<form method="post">
<br><br><div class="card">
<div class="card-header bg-dark">
<h1 class="text-white text-center"> Insert Operation </h1>
</div><br>
<label> Username: </label>
<input type="text" name="username" class="form-control"> <br>
<label> Password: </label>
<input type="text" name="password" class="form-control"> <br>
<button class="btn btn-success" type="submit" name="done"> Submit </button><br>
</div>
</form>
</div>
</body>
</html>
Display.php file
This is the main display page where we will show all the interface that we want too. We want to have a nice table where the user can see their Names, Password, Delete Button and Update Button.
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
</head>
<body>
<div class="container">
<div class="col-lg-12">
<br><br>
<h1 class="text-warning text-center" > Display Table Data </h1>
<br>
<table id="tabledata" class=" table table-striped table-hover table-bordered">
<tr class="bg-dark text-white text-center">
<th> Id </th>
<th> Username </th>
<th> Password </th>
<th> Delete </th>
<th> Update </th>
</tr >
<?php
include 'conn.php';
$q = "select * from crudtable ";
$query = mysqli_query($con,$q);
while($res = mysqli_fetch_array($query)){
?>
<tr class="text-center">
<td> <?php echo $res['id']; ?> </td>
<td> <?php echo $res['username']; ?> </td>
<td> <?php echo $res['password']; ?> </td>
<td> <button class="btn-danger btn"> <a href="delete.php?id=<?php echo $res['id']; ?>" class="text-white"> Delete </a> </button> </td>
<td> <button class="btn-primary btn"> <a href="update.php?id=<?php echo $res['id']; ?>" class="text-white"> Update </a> </button> </td>
</tr>
<?php
}
?>
</table>
</div>
</div>
<script type="text/javascript">
$(document).ready(function(){
$('#tabledata').DataTable();
})
</script>
</body>
</html>
Delete.php file
Here If anytime user wants to delete any field by simply click on the delete button, the user can do it. Is it that simple?
<?php
include 'conn.php';
$id = $_GET['id'];
$q = " DELETE FROM `crudtable` WHERE id = $id ";
mysqli_query($con, $q);
header('location:display.php');
?>
include 'conn.php';
$id = $_GET['id'];
$q = " DELETE FROM `crudtable` WHERE id = $id ";
mysqli_query($con, $q);
header('location:display.php');
?>
Update.php file
Here If anytime user wants to update any field by simply click on the update button, the user can do it. Is it that simple? You have to simply copy it and paste. This is the last part and I hope my video and source code helps you.
<?php
include 'conn.php';
if(isset($_POST['done'])){
$id = $_GET['id'];
$username = $_POST['username'];
$password = $_POST['password'];
$q = " update crudtable set id=$id, username='$username', password='$password' where id=$id ";
$query = mysqli_query($con,$q);
header('location:display.php');
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="col-lg-6 m-auto">
<form method="post">
<br><br><div class="card">
<div class="card-header bg-dark">
<h1 class="text-white text-center"> Update Operation </h1>
</div><br>
<label> Username: </label>
<input type="text" name="username" class="form-control"> <br>
<label> Password: </label>
<input type="text" name="password" class="form-control"> <br>
<button class="btn btn-success" type="submit" name="done"> Submit </button><br>
</div>
</form>
</div>
</body>
</html>
include 'conn.php';
if(isset($_POST['done'])){
$id = $_GET['id'];
$username = $_POST['username'];
$password = $_POST['password'];
$q = " update crudtable set id=$id, username='$username', password='$password' where id=$id ";
$query = mysqli_query($con,$q);
header('location:display.php');
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="col-lg-6 m-auto">
<form method="post">
<br><br><div class="card">
<div class="card-header bg-dark">
<h1 class="text-white text-center"> Update Operation </h1>
</div><br>
<label> Username: </label>
<input type="text" name="username" class="form-control"> <br>
<label> Password: </label>
<input type="text" name="password" class="form-control"> <br>
<button class="btn btn-success" type="submit" name="done"> Submit </button><br>
</div>
</form>
</div>
</body>
</html>
