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
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.
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.
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.