CRUD Operation in PHP MySQLi with Code. Select Insert Update Delete in PHP MySQLi

September 17, 2018 html5 and css3, javascript, mysql, php, php and mysql

CRUD Operation in PHP MySQL with Code. Select Insert Update Delete in PHP MySQLi

Welcome, all we will see how we can perform CRUD operation using PHP and MYSQL.
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');

?>

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>

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');

?>

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>