Today we are going to implement simple CRUD operation. which is require everywhere in development and this will help you to begin your development projects.
- C – Create : – To insert data into database (INSERT SQL Query).
- R – Read : – To read data from database (SELECT SQL Query).
- U – Update : – To update data in database (UPDATE SQL Query).
- D – Delete : – To delete data in database (DELETE SQL Query).
Create database table
Creating MySQL Table
CREATE TABLE `crud` (
`firstname` varchar(30) NOT NULL,
`id` int(11) NOT NULL,
`lastname` varchar(30) NOT NULL,
`email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `crud`
ADD PRIMARY KEY (`id`);
ALTER TABLE `crud`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT
Let’s create one common database connection file and use it everywhere
config.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "test";
$conn = mysqli_connect($servername, $username, $password, $db);
// Check The Connection
if (mysqli_connect_errno() ){
echo "Database Connection Failed: " . mysqli_connect_error();
die;
}
mysqli_set_charset($conn,"utf8");
?>
We will use bootstrap to make UI looks great. create one index.php file. we can display the data using select query and feed data to table. display every field from table and action cols we can edit, delete operation.
Create index.php file
<?php
include('config.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="form-group">
<a href="operation.php" class="btn btn-success">Add</a>
</div>
</div>
<?php
$Sql = "SELECT * FROM crud";
$result = mysqli_query($conn, $Sql);
if (mysqli_num_rows($result) > 0) {
echo "<div class='row'><div class='table-responsive'><table id='myTable' class='table table-striped table-bordered'>
<thead><tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Action</th>
</tr></thead><tbody>";
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>
<td>" . $row['firstname']."</td>
<td>" . $row['lastname']."</td>
<td>" . $row['email']."</td>
<td><a href='operation.php?id=".$row['id']."&action=EDIT'><button class='btn btn-primary btn-xs'><span class='glyphicon glyphicon-pencil'></span></button></a> <a href='operation.php?id=".$row['id']."&action=DELETE'> <button class='btn btn-danger btn-xs'><span class='glyphicon glyphicon-trash'></span></button></a></td>
</tr>";
}
echo "</tbody></table></div></div>";
} else {
echo "you have no records";
}
?>
</div>
</body>
</html>Create one operation.php file. edit, add and delete all operation we do in one file. we use action in query string and define EDIT and DELETE parameter. If there is no query string we do Insert in database
Create operation.php file
<?php
include('config.php');
if(isset($_POST['submit']) && $_POST['submit']=='Update'){
$update=mysqli_query($conn,"update crud set firstname='".$_POST['firstname']."',lastname='".$_POST['lastname']."',email='".$_POST['email']."' where id='".$_GET['id']."'");
if($update){
header('location:index.php');
exit;
}
}
if(isset($_POST['submit']) && $_POST['submit']=='Save'){
$add=mysqli_query($conn,"insert into crud(firstname,lastname,email) values('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')");
if($add){
header('location:index.php');
exit;
}
}
$firstname = '';
$lastname = '';
$email = '';
$btn_val = 'Save';
if(isset($_GET['action']) && !empty($_GET['id'])){
if($_GET['action']=='EDIT'){
$sql = "SELECT * FROM crud WHERE id='".$_GET['id']."'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$email = $row['email'];
$btn_val = 'Update';
}
if($_GET['action']=='DELETE'){
$delete=mysqli_query($conn, "DELETE FROM crud WHERE id='".$_GET['id']."'");
if($delete){
header('location:index.php');
exit;
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<div>
<form class="form-horizontal" method="post" enctype="multipart/form-data">
<div class="form-group">
<div class="col-md-12">
<label for="register-form-name">First Name : </label>
<input type="text" name="firstname" value="<?php echo $firstname; ?>" class="form-control" / required>
</div>
</div>
<div class="form-group">
<div class="col-md-12">
<label for="register-form-name">last Name : </label>
<input type="text" name="lastname" value="<?php echo $lastname; ?>" class="form-control" / required>
</div>
</div>
<div class="form-group">
<div class="col-md-12">
<label for="register-form-name">Email : </label>
<input type="text" name="email" value="<?php echo $email; ?>" class="form-control" / required>
</div>
</div>
<div class="form-group">
<div class="col-md-4">
<input type="submit" name="submit" class="btn btn-success" value="<?php echo $btn_val; ?>"/>
</div>
</div>
</form>
</div>
</div>
</body>
</html>