In this article, I will explain a simple and basic system to add, edit, delete and view of Employee data using PHP and MySQL.
CRUD stands for create, read, update and delete.
Create means inserting data into the database using INSERT MYSQL Query.
Read means reading data from database using SELECT MYSQL Query.
Update means updating records using UPDATE MYSQL Query.
Finally, Delete means deleting data from database using MYSQL Query.
first, we have to create the database for storing this Employee info.
MYSQL Query for create database.For example, our database name is "qa_db017".
CREATE DATABASE `qa_db017` ;MYSQL Query for creating users table for storing Employees info.
here our table name is qa_users
CREATE TABLE `qa_users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`emp_name` VARCHAR( 100 ) NOT NULL ,
`emp_salary` INT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;Database connection code in PHP.
we save our file as db_config.php
code in this file as shown below.
<?php
$dbHost = 'localhost';
$dbName = 'qa_db017';
$dbUsername = 'root';
$dbPassword = '';
$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);
?>List of all Employees in Home Page
index.php
<?php
include_once("db_config.php");
$result = mysqli_query($con, "SELECT * FROM qa_users ORDER BY id DESC");
?><html>
<head>
<title>Emplyee List</title>
</head>
<body>
<a href="add_emp.html">Add New Employee</a><br/><br/>
<table width='80%' border=0>
<tr bgcolor='#CCCCCC'>
<td>Employee Name</td>
<td>Employee Salary</td>
<td>Actions</td>
</tr>
<?php
while($res = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>".$res['emp_name']."</td>";
echo "<td>".$res['emp_salary']."</td>";
echo "<td><a href=\"view_emp.php?id=$res[id]\">View</a> | <a href=\"edit_emp.php?id=$res[id]\">Edit</a> | <a href=\"delete_emp.php?id=$res[id]\" onClick=\"return confirm('Are you sure you want to delete this Emplyee Info?')\">Delete</a></td>";
}
?>
</table>
</body>
</html>
1) C – Create : add_emp.html and add_emp.php – To insert data into database (INSERT MYSQL Query).
Add Employee form:
save this page as add_emp.html
<html>
<head>
<title>Add Employee Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form action="add_emp.php" method="post" name="emp_frm">
<table width="25%" border="0">
<tr>
<td>Employee Name</td>
<td><input type="text" name="emp_name"></td>
</tr>
<tr>
<td>Employee Salary</td>
<td><input type="text" name="emp_salary"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Add"></td>
</tr>
</table>
</form>
</body>
</html>From this form, users can enter Employee Name & Employee Salary info.
By using post method we will get these details in add_emp.php and store into qa_users table.
see the code for insert Employee info as shown below.
save this file as add_emp.php
<html>
<head>
<title>Add Employee Data</title>
</head>
<body>
<?php
include_once("db_config.php");
if(isset($_POST['Submit'])) {
$emp_name = mysqli_real_escape_string($con, $_POST['emp_name']);
$emp_salary = mysqli_real_escape_string($con, $_POST['emp_salary']);
if(empty($emp_name) || empty($emp_salary)) {
if(empty($emp_name)) {
echo "<font color='red'>Employee Name field is empty.</font><br/>";
}
if(empty($emp_salary)) {
echo "<font color='red'>Employee Salary field is empty.</font><br/>";
}
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} else {
$sql="INSERT INTO qa_users(emp_name,emp_salary) VALUES('$emp_name','$emp_salary')";
$result = mysqli_query($con,$sql);
echo "<font color='green'>Employee Data added successfully.";
echo "<br/><a href='index.php'>View Result</a>";
}
}
?>
</body>
</html>
2)R – Read: view_emp.php – To read data from the database (SELECT MYSQL Query).
<?php
include_once("db_config.php");
$id = $_GET['id'];
$result = mysqli_query($con, "SELECT * FROM qa_users WHERE id=$id");
$res = mysqli_fetch_array($result);
?>
<html>
<head>
<title>View Employee Info</title>
</head>
<body>
<a href="index.php">Home</a><br/><br/>
<table align="center" border="1">
<tr bgcolor='#CCCCCC'><td colspan="2"><?php echo $res['emp_name'];?>'s Info</td></tr>
<tr>
<td>Employee Name</td><td><?php echo $res['emp_name'];?></td></tr>
<tr>
<td>Employee Salary</td><td><?php echo $res['emp_salary'];?></td>
</tr>
</table>
</body>
</html>
3)U – Update : edit_emp.php – To update data in database (UPDATE MYSQL Query).
<?php
include_once("db_config.php");
if(isset($_POST['update']))
{
$id = mysqli_real_escape_string($con, $_POST['id']);
$emp_name = mysqli_real_escape_string($con, $_POST['emp_name']);
$emp_salary = mysqli_real_escape_string($con, $_POST['emp_salary']);
if(empty($emp_name) || empty($emp_salary)) {
if(empty($emp_name)) {
echo "<font color='red'>Employee Name field is empty.</font><br/>";
}
if(empty($emp_salary)) {
echo "<font color='red'>Employee Salary field is empty.</font><br/>";
}
} else {
$result = mysqli_query($con, "UPDATE qa_users SET emp_name='$emp_name',emp_salary='$emp_salary' WHERE id=$id");
header("Location: index.php");
}
}
?>
<?php
$id = $_GET['id'];
$result = mysqli_query($con, "SELECT * FROM qa_users WHERE id=$id");
while($res = mysqli_fetch_array($result))
{
$emp_name = $res['emp_name'];
$emp_salary = $res['emp_salary'];
}
?>
<html>
<head>
<title>Edit Employee Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form name="form1" method="post" action="edit.php">
<table border="0">
<tr>
<td>Employee Name</td>
<td><input type="text" name="emp_name" value="<?php echo $emp_name;?>"></td>
</tr>
<tr>
<td>Employee Salary</td>
<td><input type="text" name="emp_salary" value="<?php echo $emp_salary;?>"></td>
</tr>
<tr>
<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>
4) D – Delete: delete_emp.php – To delete data in the database (DELETE MYSQL Query).
<?php
include("config.php");
$id = $_GET['id'];
$result = mysqli_query($con, "DELETE FROM qa_users WHERE id=$id");
header("Location:index.php");
?>That's it, you are done, you can post comment/queries related to this article below.
Buy us a coffee
Become a Patron