How to create CRUD Operation using PHP, MySQL

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

How to create CRUD Operation using PHP, MySQL

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>


Comments

No comments yet. Why don’t you start the discussion?

    Leave a Reply