Simple Crud operations in php using mysql Example

Today, We want to share with you crud operation in php.In this post we will show you creating a complete PHP PDO CRUD Create, edit, update and delete posts with MySQL database, hear for how to make the beginnings of a very simple database app, using PHP and MySQL. we will give you demo and example for implement.In this post, we will learn about Select Insert Update Delete in PHP MySQLi with an example.

PHP MySQL CRUD Application

PHP and MySqli CRUD Operation Tutorial
PHP and MySqli CRUD Operation Tutorial

What is CRUD?

CRUD stands for Create, Read, Update, Delete operations with database table records. Most online web applications as well as Company projects perform some kind of CRUD functionality, you need to multiple time the CRUD Operations for every object in your system.

Files includes in this tutorials

  • members_app.sql: Contain the database table structure.
  • config_database.php: Used for database connection.
  • index.php: Used to Get the member from database.
  • list.php: Used to Get the member of particular member.
  • edit.php: Used to edit the member.
  • member_store.php: Used to insert the new member.
PHP - MySQLi Insert Update Delete CRUD Operation using AngularJS
PHP – MySQLi Insert Update Delete CRUD Operation using AngularJS

Step 1– Create a database

We’ll create a database with a “members_table” table, we’ll be able to manipulate these members_table in our CRUD app, the “members_table” table will contain names, emails, Mobile numbers, etc.

Read Also:  PHP MySQLi Insert Update Delete Query Builder

CREATE TABLE `members_table` (
  `ID` int(10) NOT NULL,
  `member_name` varchar(200) DEFAULT NULL,
  `member_fullname` varchar(200) DEFAULT NULL,
  `conatct_no` bigint(10) DEFAULT NULL,
  `Email` varchar(200) DEFAULT NULL,
  `mem_location` mediumtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 2– Create a database connection

config_database.php

<?php
$link=mysqli_connect("localhost", "root", "", "members_app");
if(mysqli_connect_errno())
{
echo "Connection Fail".mysqli_connect_error();
}
?>

Step 3– Create a HTML form for data insertion

<form  method="POST">
<h2>Fill Data</h2>
<p class="hint-text">Fill below form.</p>
 <div class="form-group">

<div class="row">
<div class="col"><input type="text" class="form-control" name="member_name" placeholder="First Name" required="true"></div>
<div class="col"><input type="text" class="form-control" name="member_fullname" placeholder="Last Name" required="true"></div>
</div>        	
</div>

<div class="form-group">
<input type="text" class="form-control" name="mobile_number" placeholder="Enter your Mobile Number" required="true" maxlength="10" pattern="[0-9]+">
        </div>

<div class="form-group">
<input type="email" class="form-control" name="email" placeholder="Enter your Email id" required="true">

</div>

<div class="form-group">
<textarea class="form-control" name="location" placeholder="Enter Your mem_location" required="true"></textarea>
</div>        

<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Submit</button>
</div>
</form>

Step 4– data insertion

member_store.php

<?php 
//Databse Connection file
include('config_database.php');
if(isset($_POST['submit']))
  {
  	//getting the post values
    $member_name=$_POST['member_name'];
    $member_fullname=$_POST['member_fullname'];
    $mobile_no=$_POST['mobile_number'];
    $email=$_POST['email'];
    $add=$_POST['location'];
   
  // Query for data insertion
     $sql_qq=mysqli_query($link, "insert into members_table(member_name,member_fullname, conatct_no, Email, mem_location) value('$member_name','$member_fullname', '$mobile_no', '$email', '$add' )");
    if ($sql_qq) {
    echo "<script>alert('You have successfully inserted the data');</script>";
    echo "<script > document.location ='index.php'; </script>";
  }
  else
    {
      echo "<script>alert('Something Went Wrong. Please try again');</script>";
    }
}
?>

Step 5– List / Fetch the record from the database

index.php

<table class="table table-striped table-hover">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Name</th>                       
                        <th>Email</th>
                        <th>Mobile Number</th>
                        <th>Created Date</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                     <?php
$ret=mysqli_query($link,"select * from members_table");
$cnt=1;
$row=mysqli_num_rows($ret);
if($row>0){
while ($row=mysqli_fetch_array($ret)) {

?>
<!--Fetch the Records -->
                    <tr>
                        <td><?php echo $cnt;?></td>
                        <td><?php  echo $row['member_name'];?> <?php  echo $row['member_fullname'];?></td>
                        <td><?php  echo $row['Email'];?></td>                        
                        <td><?php  echo $row['conatct_no'];?></td>
                        <td> <?php  echo $row['created_at'];?></td>
                        <td>
<a href="list.php?viewid=<?php echo htmlentities ($row['ID']);?>" class="view" title="Member View" data-toggle="tooltip"><i class="material-icons"></i></a>
<a href="edit.php?member_id=<?php echo htmlentities ($row['ID']);?>" class="edit" title="Member Edit" data-toggle="tooltip"><i class="material-icons"></i></a>                       
<a href="index.php?delid=<?php echo ($row['ID']);?>" class="delete" title="Delete" data-toggle="tooltip" onclick="return confirm('Do you really want to Delete this Member?');"><i class="material-icons"></i></a>
</td>
 </tr>
<?php 
$cnt=$cnt+1;
} } else {?>
<tr>
<th style="text-align:center; color:red;" colspan="6">No Record Found</th>
</tr>
<?php } ?>                 
              
</tbody>
 </table>

Step 6 – List / Fetch the particular record

list.php

<table cellpadding="0" cellspacing="0" border="0" class="display table table-bordered" id="hidden-table-info">
               
<tbody>
<?php
$vid=$_GET['viewid'];
$ret=mysqli_query($link,"select * from members_table where ID =$vid");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {

?>
 <tr>
    <th>Member Name</th>
    <td><?php  echo $row['member_name'];?></td>
    <th>Last Name</th>
    <td><?php  echo $row['member_fullname'];?></td>
  </tr>
  <tr>
    <th>Email Address</th>
    <td><?php  echo $row['Email'];?></td>
    <th>Mobile Number</th>
    <td><?php  echo $row['conatct_no'];?></td>
  </tr>
  <tr>
    <th>Location</th>
    <td><?php  echo $row['mem_location'];?></td>
    <th>Creation Date</th>
    <td><?php  echo $row['created_at'];?></td>
  </tr>
<?php 
$cnt=$cnt+1;
}?>
                 
</tbody>
</table>

Step 7 –Edit/ Update the particular record

edit.php

<form  method="POST">
 <?php
$member_edit_id=$_GET['member_id'];
$ret=mysqli_query($link,"select * from members_table where ID='$member_edit_id'");
while ($row=mysqli_fetch_array($ret)) {
?>
<h2>Update </h2>
<p class="hint-text">Update your info.</p>
<div class="form-group">
<div class="row">
<div class="col"><input type="text" class="form-control" name="member_name" value="<?php  echo $row['member_name'];?>" required="true"></div>
				<div class="col"><input type="text" class="form-control" name="member_fullname" value="<?php  echo $row['member_fullname'];?>" required="true"></div>
	</div>        	
        </div>

<div class="form-group">
<input type="text" class="form-control" name="mobile_number" value="<?php  echo $row['conatct_no'];?>" required="true" maxlength="10" pattern="[0-9]+">
        </div>

<div class="form-group">
<input type="email" class="form-control" name="email" value="<?php  echo $row['Email'];?>" required="true">
</div>
		
<div class="form-group">
<textarea class="form-control" name="location" required="true"><?php  echo $row['mem_location'];?></textarea>
</div>        
<?php 
}?>
<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Update</button>
        </div>
    </form>

Step 7.1 Update the Member record.

edit.php

<?php 
//Database Connection
include('config_database.php');
if(isset($_POST['submit']))
  {
  	$member_edit_id=$_GET['member_id'];
	//Getting Post Values
    $member_name=$_POST['member_name'];
    $member_fullname=$_POST['member_fullname'];
    $mobile_no=$_POST['mobile_number'];
    $email=$_POST['email'];
    $add=$_POST['location'];

    //Query for Member updation
     $sql_qq=mysqli_query($link, "update  members_table set member_name='$member_name',member_fullname='$member_fullname', conatct_no='$mobile_no', Email='$email', mem_location='$add' where ID='$member_edit_id'");
     
    if ($sql_qq) {
    echo "<script>alert('You have successfully update the Member');</script>";
    echo "<script > document.location ='index.php'; </script>";
  }
  else
    {
      echo "<script>alert('Something Went Wrong. Please try again');</script>";
    }
}
?>

Step 8 – Member deletion from the database

<?php
//database connection  file
include('config_database.php');

//Member deletion
if(isset($_GET['delid']))
{
	$rid=intval($_GET['delid']);
	$sql=mysqli_query($link,"delete from members_table where ID=$rid");
	echo "<script>alert('Data deleted');</script>"; 
	echo "<script>window.location.href = 'index.php'</script>";     
} 
?>

I hope you get an idea about how to create CRUD operation using PHP and MySQLi..
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Read Also:  How to convert date format in PHP?

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  Drag And Drop Multiple File Uploading using DropzoneJS and PHP Example
  2. Read Also:  jQuery Delete Multiple Rows using checkbox PHP with MySQL
  3. Read Also:  vuejs Manipulate Numbers Money Format
  4. Read Also:  Wordpress Next Previous Article/post using CSS
  5. Read Also:  Ng Events using Angular Example with demo
  6. Read Also:  css disable button
  7. Read Also:  Vuejs loop each Example with Demo
  8. Read Also:  Advanced jQuery Ajax Pagination PHP MySQL using jQuery
  9. Read Also:  PHP CRUD Create, edit, update and delete Records with MySQL database
  10. Read Also:  PHP and MySqli CRUD Operation Tutorial
CLOSEX