In this tutorial, I will tell how to insert, view, edit, and delete record from database using PHP and Mysqli. This tutorial is the second section of Simple User Registration & Login Script in MySQLi and PHP in this first section, and I explained how to create simple user registration and login using PHP, and if you don’t know how to create user registration and user login form. So please first check this tutorial Simple User Registration & Login Script in MySQLi and PHP after that, let’s come back to this segment.
I am using PHP 5.6 for the following tutorial. I did not check this tutorial on PHP 7, and please make sure you are using PHP 5.6 to avoid any unwanted errors.
Steps for Creating an Insert, View, Edit and Delete Record from Database Using MySQLi and PHP
Now, I hope that you have already created user registration and login forms, which I created in Simple User Registration & Login Script in MySQLi and PHP. Now I will create another table to keep records in it, update dashboard.php file and add four new pages which are insert.php, view.php, edit.php, and delete.php, check the following steps:
- Create Another Table for Records
- Update Dashboard File
- Create Insert Page
- Make a View Page
- Create Edit/Update Page
- Create Delete Page
1. Create another Table for Records
Execute the below SQL query:
CREATE TABLE IF NOT EXISTS `new_record` ( `id`int(11) NOT NULL AUTO_INCREMENT, `trn_date`datetime NOT NULL, `name`varchar(50) NOT NULL, `age`int(11) NOT NULL, `submittedby`varchar(50) NOT NULL, PRIMARY KEY (`id`) );
2. Update the Dashboard Page
Update dashboard.php file and paste the below-given code in it.
<?php
require('db.php');
include("auth.php");
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Dashboard - Secured Page</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="form">
<p>Welcome to Dashboard.</p>
<p><a href="index.php">Home</a><p>
<p><a href="insert.php">Insert New Record</a></p>
<p><a href="view.php">View Records</a><p>
<p><a href="logout.php">Logout</a></p>
</div>
</body>
</html>3. Creating an Insert Page
Create a page with the name insert.php and paste the following code in it.
<?php
require('db.php');
include("auth.php");
$status = "";
if(isset($_POST['new']) && $_POST['new']==1){
$trn_date = date("Y-m-d H:i:s");
$name =$_REQUEST['name'];
$age = $_REQUEST['age'];
$submittedby = $_SESSION["username"];
$ins_query="insert into new_record
(`trn_date`,`name`,`age`,`submittedby`)values
('$trn_date','$name','$age','$submittedby')";
mysqli_query($con,$ins_query)
or die(mysql_error());
$status = "New Record Inserted Successfully.
</br></br><a href='view.php'>View Inserted Record</a>";
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert New Record</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="form">
<p><a href="dashboard.php">Dashboard</a>
| <a href="view.php">View Records</a>
| <a href="logout.php">Logout</a></p>
<div>
<h1>Insert New Record</h1>
<form name="form" method="post" action="">
<input type="hidden" name="new" value="1" />
<p><input type="text" name="name" placeholder="Enter Name" required /></p>
<p><input type="text" name="age" placeholder="Enter Age" required /></p>
<p><input name="submit" type="submit" value="Submit" /></p>
</form>
<p style="color:#FF0000;"><?php echo $status; ?></p>
</div>
</div>
</body>
</html>4. Creating a View Page
Create a page with the name view.php and paste the given code in it.
<?php
require('db.php');
include("auth.php");
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>View Records</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="form">
<p><a href="index.php">Home</a>
| <a href="insert.php">Insert New Record</a>
| <a href="logout.php">Logout</a></p>
<h2>View Records</h2>
<table width="100%" border="1" style="border-collapse:collapse;">
<thead>
<tr>
<th><strong>S.No</strong></th>
<th><strong>Name</strong></th>
<th><strong>Age</strong></th>
<th><strong>Edit</strong></th>
<th><strong>Delete</strong></th>
</tr>
</thead>
<tbody>
<?php
$count=1;
$sel_query="Select * from new_record ORDER BY id desc;";
$result = mysqli_query($con,$sel_query);
while($row = mysqli_fetch_assoc($result)) { ?>
<tr><td align="center"><?php echo $count; ?></td>
<td align="center"><?php echo $row["name"]; ?></td>
<td align="center"><?php echo $row["age"]; ?></td>
<td align="center">
<a href="edit.php?id=<?php echo $row["id"]; ?>">Edit</a>
</td>
<td align="center">
<a href="delete.php?id=<?php echo $row["id"]; ?>">Delete</a>
</td>
</tr>
<?php $count++; } ?>
</tbody>
</table>
</div>
</body>
</html>5. Creating an Edit/Update Page
Create a page with the name edit.php and paste the given code in it.
<?php
require('db.php');
include("auth.php");
$id=$_REQUEST['id'];
$query = "SELECT * from new_record where id='".$id."'";
$result = mysqli_query($con, $query) or die ( mysqli_error());
$row = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Update Record</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="form">
<p><a href="dashboard.php">Dashboard</a>
| <a href="insert.php">Insert New Record</a>
| <a href="logout.php">Logout</a></p>
<h1>Update Record</h1>
<?php
$status = "";
if(isset($_POST['new']) && $_POST['new']==1)
{
$id=$_REQUEST['id'];
$trn_date = date("Y-m-d H:i:s");
$name =$_REQUEST['name'];
$age =$_REQUEST['age'];
$submittedby = $_SESSION["username"];
$update="update new_record set trn_date='".$trn_date."',
name='".$name."', age='".$age."',
submittedby='".$submittedby."' where id='".$id."'";
mysqli_query($con, $update) or die(mysqli_error());
$status = "Record Updated Successfully. </br></br>
<a href='view.php'>View Updated Record</a>";
echo '<p style="color:#FF0000;">'.$status.'</p>';
}else {
?>
<div>
<form name="form" method="post" action="">
<input type="hidden" name="new" value="1" />
<input name="id" type="hidden" value="<?php echo $row['id'];?>" />
<p><input type="text" name="name" placeholder="Enter Name"
required value="<?php echo $row['name'];?>" /></p>
<p><input type="text" name="age" placeholder="Enter Age"
required value="<?php echo $row['age'];?>" /></p>
<p><input name="submit" type="submit" value="Update" /></p>
</form>
<?php } ?>
</div>
</div>
</body>
</html>6. Creating a Delete Page
Create a page with name delete.php and paste the below code in it.
<?php
require('db.php');
$id=$_REQUEST['id'];
$query = "DELETE FROM new_record WHERE id=$id";
$result = mysqli_query($con,$query) or die ( mysqli_error());
header("Location: view.php");
?>If you found this tutorial helpful, share it with your friends, developers groups, and leave your comment.
Also, read our previous blog- Simple User Registration & Login Script in PHP and MySQLi
