Building a simple crud operation with a Search engine for storing student records using PHP & MySQL
what is a PHP crud operation ?
PHP CRUD operation refers to performing Create, Read, Update, and Delete operations on a database using PHP. It is a common approach in web development for managing data in a database.
Here’s a brief explanation of each operation:
Create : Creating new records or entries in a database. This involves inserting data into the database tables.
Read : Retrieving or reading data from the database. This operation allows you to fetch and display data from the database tables.
Update : Modifying existing records in the database. It involves updating the values of specific fields or columns in the database tables.
Delete : Removing or deleting records from the database. This operation allows you to remove specific data entries from the database tables.
CRUD operations are fundamental for building dynamic web applications that interact with a database. They provide the basic functionality for managing data and allow users to create, view, edit, and delete records in the application.
The source code can be accessed here.
Now , let’s begin.
I will be building a simple application for collecting Student records using MySQL and PHP.
First of all , you need to install a code editor(such as vs code), MySQL database and xampp server.
we have to create the Database for storing the student records before doing anything else.
The SQL command below is used in creating a Database.
create database crud;After creating the Database the next thing to create is the TABLE which will be holding all our data.
A table is like a compartment in our database consisting of column(s) and row (s) for storing our records.
Our table will be consisting of 5 columns which are : name, phone, age, gender and faculty.
This is the SQL command to create the Table and the column :
create table students (
id int primary key auto_increment,
name varchar(100) not null,
phone varchar(100) not null,
age varchar(100) not null,
gender varchar(500) not null,
faculty varchar(100) not null
);The structure of the table should look like this after executing the SQL command :
Next step, go ahead and set up a new folder. I’m gonna call it “CRUD OPERATION” but you can call yours whatever you want.
The file path is given below:
C:\xampp\htdocs\CRUD OPERATION
Go ahead and open that folder in the code editor and create the necessary files and folder.
this is my project folder,
The next thing now is to connect it to our database using PHP.
Create a new file and name it “config.php”. Here is the code to connect to the database.
// config.php
<?php
$serverName = 'localhost';
$userName = 'root';
$password = ''; // if do you have a MySQL password put it
$dbName = 'crud';
// create connection
$conn = new mysqli($serverName, $userName, $password, $dbName);
// check connection
if($conn->connect_error) {
die("connection failed" . $conn->connect_error);
}
else {
echo "connection successfully <br>";
}
?>The “$serverName”, “$userName”, “$password”, and “$dbName” variables hold the necessary information to connect to the MySQL server. Modify these values according to your specific database configuration. The new “mysqli()” statement creates a new MySQLi object, representing the database connection. It takes the server name, username, password, and database name as parameters. The “connect_error” property is used to check if the connection to the database was successful. If there is an error, the “die()” function is called, terminating the script execution and displaying the error message. If the connection is successful, the “connection successfully” message is displayed in the browser.
Now let’s create the student registration form for collecting the student data. I name it “create.php”.
Here is the code.
// create.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Student Records</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<section>
<h1>Student Registration Form</h1>
<form action="process.php" method="post">
<div class="inputbox">
<label for="">Full Name : </label>
<input type="text" name="name" placeholder="Enter Full Name" required>
</div>
<div class="inputbox">
<label for="">Mobile Number : </label>
<input type="text" name="phone" placeholder="Enter Mobile Number" required>
</div>
<div class="inputbox">
<label for="">Age : </label>
<input type="number" name="age" placeholder="Enter Student Age" required>
</div>
<div class="inputbox">
<label for="">Gender : </label>
<select name="gender">
<option value="">Select Gender</option>
<option value="Male">Male</option>
<option value="Female">Female</option>
<option value="Others">Others</option>
</select>
</div>
<div class="inputbox">
<label for="">Faculty : </label>
<select name="faculty">
<option value="">Select Faculty</option>
<option value="Computing">Computing</option>
<option value="Business">Business</option>
<option value="Engineering">Engineering</option>
<option value="Humanities & Science">Humanities & Science</option>
<option value="Architecture">Architecture</option>
</select>
</div>
<div class="inputbox">
<input type="submit" value="Submit" name="create" class="formbtn">
</div>
</form>
</section>
</body>
</html>
The form uses the POST method and submits data to a PHP script called “process.php” using the action attribute of the <form> tag.
the action attribute specifies the URL or file where the form data will be submitted for processing. in this code, the form data will be sent to the “process.php” file for processing.
Here is the “process.php” code,
// process.php
<?php
include "config.php";
if(isset($_POST['create'])) {
$name = $_POST['name'];
$phone = $_POST['phone'];
$age = $_POST['age'];
$gender = $_POST['gender'];
$faculty = $_POST['faculty'];
$sql = "INSERT INTO students(name, phone, age, gender, faculty) VALUES('$name','$phone','$age','$gender','$faculty')";
$result = mysqli_query($conn,$sql);
if($result) {
header('location:view.php');
}
else {
die(mysqli_error($conn));
}
}
if(isset($_POST['update'])) {
$id = $_POST['id'];
$name = $_POST['name'];
$phone = $_POST['phone'];
$age = $_POST['age'];
$gender = $_POST['gender'];
$faculty = $_POST['faculty'];
$sql = "UPDATE students SET name = '$name', phone = '$phone', age = '$age', gender = '$gender', faculty = '$faculty' WHERE id='$id'";
$result = mysqli_query($conn,$sql);
if($result) {
header('location:view.php');
}
else {
die(mysqli_error($conn));
}
}
$conn->close();
?>
The code includes the “config.php” file, which presumably contains the database connection details and sets up the $conn variable as the database connection object.
this code handles the creation and updating of student records in the database based on the data submitted through the registration and update forms. It ensures proper error handling and redirects to the appropriate pages after successful operations.
Now let’s create a “view.php” file, Here is the code.
// view.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>View Student Details</title>
<link rel="stylesheet" href="css/view.css">
</head>
<body>
<section>
<div class="tableviewhead">
<h1>Student Registration List</h1>
<div class="left">
<form action="search.php" method="post">
<input type="text" placeholder="search data" name="search">
<button name="submit">Search</button>
</form>
<a href="./create.php">Add</a>
</div>
</div>
<table>
<thead>
<th>ID</th>
<th>Full Name</th>
<th>Contact Number</th>
<th>Age</th>
<th>Gender</th>
<th>Faculty</th>
<th colspan="2">Action</th>
</thead>
<tbody>
<?php
include "config.php";
$sql = "SELECT * FROM students";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["phone"]; ?></td>
<td><?php echo $row["age"]; ?></td>
<td><?php echo $row["gender"]; ?></td>
<td><?php echo $row["faculty"]; ?></td>
<td>
<a href="read.php?id=<?php echo $row["id"]; ?>" target="_blank" class="read">Read More</a>
<a href="update.php?id=<?php echo $row["id"]; ?>" target="_blank" class="update">Update</a>
<a href="delete.php?id=<?php echo $row["id"]; ?>" target="_blank" class="delete">Delete</a>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
</section>
</body>
</html>
This PHP page displays a table view of student records retrieved from the database. It provides options to search for data, add new records, and perform read, update, and delete operations on individual records.
Now let’s create a “update.php” file, Here is the code.
// update.php
<?php
include "config.php";
$id = $_GET['id'];
$sql = "SELECT * FROM students WHERE id=$id";
$result = mysqli_query($conn,$sql);
$row = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<section>
<h1>Update Student Registration Form</h1>
<form action="process.php" method="post">
<div class="inputbox">
<label for="">Full Name : </label>
<input type="text" name="name" value='<?php echo $row['name']; ?>'>
</div>
<div class="inputbox">
<label for="">Mobile Number : </label>
<input type="text" name="phone" value='<?php echo $row['phone']; ?>'>
</div>
<div class="inputbox">
<label for="">Age : </label>
<input type="number" name="age" value='<?php echo $row['age']; ?>'>
</div>
<div class="inputbox">
<label for="">Gender : </label>
<select name="gender">
<option value="">Select Gender</option>
<option value="Male" <?php if($row['gender'] == "Male"){echo "selected";} ?>>Male</option>
<option value="Female" <?php if($row['gender'] == "Female"){echo "selected";} ?>>Female</option>
<option value="Others" <?php if($row['gender'] == "Others"){echo "selected";} ?>>Others</option>
</select>
</div>
<div class="inputbox">
<label for="">Faculty : </label>
<select name="faculty">
<option value="">Select Faculty</option>
<option value="Computing" <?php if($row['faculty'] == "Computing"){echo "selected";} ?>>Computing</option>
<option value="Business" <?php if($row['faculty'] == "Business"){echo "selected";} ?>>Business</option>
<option value="Engineering" <?php if($row['faculty'] == "Engineering"){echo "selected";} ?>>Engineering</option>
<option value="Humanities & Science" <?php if($row['faculty'] == "Humanities & Science"){echo "selected";} ?>>Humanities & Science</option>
<option value="Architecture" <?php if($row['faculty'] == "Architecture"){echo "selected";} ?>>Architecture</option>
</select>
</div>
<div class="inputbox">
<input type="hidden" name="id" value='<?php echo $row['id']; ?>'>
</div>
<div class="inputbox">
<input type="submit" value="Update" name="update" class="formbtn">
</div>
</form>
</section>
</body>
</html>
Now let’s create a “read.php” file, Here is the code.
// read.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>More Details</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<?php
if(isset($_GET["id"])) {
$id = $_GET["id"];
include "config.php";
$sql = "SELECT * FROM students WHERE id=$id";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
?>
<div class="formview">
<div class="list">
<h2>Full Name</h2>
<p><?php echo $row["name"]; ?></p>
</div>
<div class="list">
<h2>Mobile Number</h2>
<p><?php echo $row["phone"]; ?></p>
</div>
<div class="list">
<h2>Age</h2>
<p><?php echo $row["age"]; ?></p>
</div>
<div class="list">
<h2>Gender</h2>
<p><?php echo $row["gender"]; ?></p>
</div>
<div class="list">
<h2>Faculty</h2>
<p><?php echo $row["faculty"]; ?></p>
</div>
</div>
<?php
}
?>
</body>
</html>
This PHP page displays the detailed information of a student record based on the provided “id” parameter. It retrieves the record from the database using the “config.php” file, and the information is displayed in a structured format using HTML elements.
Now let’s create a “delete.php” file, Here is the code.
// delete.php
<?php
include "config.php";
$id = $_GET['id'];
$sql = "DELETE FROM students WHERE id=$id";
$result = mysqli_query($conn, $sql);
if($result) {
header('location:view.php');
}
else {
die(mysqli_error($conn));
}
?>This code deletes a specific student record from the database table based on the provided “id” parameter. After the deletion, it redirects the user to the “view.php” page to display the updated student records.
Now let’s create a “search.php” file, Here is the code.
// search.php
<?php
include 'config.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Student Record data Search</title>
<style>
body {
background-color: #1f242d;
color: #a7a0a0;
}
table tr {
text-align: center;
}
</style>
</head>
<body>
<table border="1" width="100%">
<?php
if(isset($_POST['submit'])) {
$search = $_POST['search'];
$sql = "SELECT * FROM students WHERE id LIKE '$search' or name LIKE '%$search%'";
$result = mysqli_query($conn, $sql);
if($result) {
if(mysqli_num_rows($result) > 0) {
echo '<thead>
<tr>
<th>ID</th>
<th>Full Name</th>
</tr></thead>';
while($row = mysqli_fetch_assoc($result)) {
echo '<tbody>
<tr>
<td><a href="search.details.php?data='.$row['id'].'">'.$row['id'].'</a></td>
<td>'.$row['name'].'</td>
</tr></tbody>';
}
}
else {
echo '<h2>Data not found</h2>';
}
}
}
?>
</table>
</body>
</html>This code allows users to search for student records based on their ID or name. The matching records are displayed in a table, and each ID is linked to a separate page for detailed information. If no matching records are found, a message is displayed.
The ID is wrapped in an anchor tag <a> that links to a separate page called “search.details.php” and passes the ID as a parameter in the URL.
Now let’s create a “search.details.php” file, Here is the code.
// search.details.php
<?php
include 'config.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Student Record data Search Details</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<?php
$data = $_GET['data'];
$sql = "SELECT * FROM students WHERE id=$data";
$result = mysqli_query($conn, $sql);
if($result) {
$row = mysqli_fetch_assoc($result);
echo '<div class="formview">
<div class="list">
<h2>Full Name</h2>
<p>'.$row['name'].'</p>
</div>
<div class="list">
<h2>Mobile Number</h2>
<p>'.$row['phone'].'</p>
</div>
<div class="list">
<h2>Age</h2>
<p>'.$row['age'].'</p>
</div>
<div class="list">
<h2>Gender</h2>
<p>'.$row['gender'].'</p>
</div>
<div class="list">
<h2>Faculty</h2>
<p>'.$row['faculty'].'</p>
</div>
</div>';
}
?>
</body>
</html>This code retrieves the details of a specific student record based on the ID passed through the URL parameter. It displays the student’s full name, mobile number, age, gender, and faculty in a formatted view. The code assumes that the “config.php” file is properly configured to establish the database connection.
Now let’s create CSS files,
This “style.css” file linked the “create.php”, “update.php”, “read.php” and “search.details.php”
/* style.css */
@import url('https://fonts.googleapis.com/css2?family=Poppins:ital,wght@0,100;0,200;0,300;0,400;1,100;1,200;1,300&display=swap');
* {
margin: 0;
padding: 0;
font-family: 'poppins', sans-serif;
box-sizing: border-box;
text-decoration: none;
list-style: none;
scroll-behavior: smooth;
}
body {
background-color: #1f242d;
color: #fff;
}
section{
margin-bottom: 5rem;
}
section h1 {
text-align: center;
letter-spacing: 3px;
font-size: 2.5rem;
font-weight: bold;
color: #a7a0a0;
text-shadow: 2px 2px 2px #ddd;
margin-bottom: 2rem;
}
section form {
position: relative;
max-width: 800px;
width: 100%;
margin: auto;
padding: 25px;
font-size: 20px;
letter-spacing: 2px;
background-color: #fff;
color: #282d33;
border-radius: 8px;
box-shadow: 0 0 15px #1f242d;
}
section form .inputbox {
width: 100%;
margin-top: 20px;
}
section form .inputbox label {
color: #282d33;
}
section form .inputbox input,
section form .inputbox select {
position: relative;
height: 50px;
width: 100%;
outline: none;
font-size: 1rem;
color: #282d33;
margin-top: 8px;
border: 1px solid #ddd;
border-radius: 6px;
padding: 0 15px;
}
section form .inputbox .formbtn {
background: #a7a0a0;
color: #1f242d;
font-weight: 900;
letter-spacing: 2px;
transition: all .45s ease;
}
section form .inputbox .formbtn:hover {
transform: scale(0.9);
cursor: pointer;
}
.formview {
text-align: center;
letter-spacing: 2px;
background-color: #ddd;
color: #282d33;
max-width: 800px;
margin: 10rem auto 5rem;
border-radius: 8px;
box-shadow: 0 0 15px #1f242d;
}
.formview .list {
display: block;
padding: 20px;
}This “view.css” file is linked to the “view.php”
/* view.css */
@import url('https://fonts.googleapis.com/css2?family=Poppins:ital,wght@0,100;0,200;0,300;0,400;1,100;1,200;1,300&display=swap');
* {
margin: 0;
padding: 0;
font-family: 'poppins', sans-serif;
box-sizing: border-box;
text-decoration: none;
list-style: none;
scroll-behavior: smooth;
}
body {
background-color: #1f242d;
color: #fff;
}
section{
margin-bottom: 5rem;
}
section .tableviewhead {
display: flex;
align-items: center;
justify-content: space-around;
margin-bottom: 4rem;
}
section .tableviewhead h1 {
letter-spacing: 3px;
font-size: 2.5rem;
font-weight: bold;
color: #a7a0a0;
text-shadow: 2px 2px 2px #ddd;
}
section .tableviewhead .left{
display: flex;
}
section .tableviewhead .left form {
margin-right: 20px;
}
section .tableviewhead .left form input,
section .tableviewhead .left form button {
position: relative;
height: 30px;
font-size: 1rem;
color: #282d33;
outline: none;
border: 1px solid #ddd;
border-radius: 6px;
padding: 0 15px;
}
section .tableviewhead .left a {
background-color: #fff;
color: #282d33;
padding: 10px 24px;
border-radius: 5px;
margin-left: 20px;
transition: all .45s ease;
}
section .tableviewhead a:hover {
background-color: #282d33;
color: #ddd;
transform: scale(0.9);
}
table {
width: 100%;
text-align: center;
}
table thead {
background: #eee;
color: #1f242d;
}
table th {
font-size: 1.5rem;
padding: 1rem;
}
table tbody td {
font-size: 1.4rem;
padding: 1rem;
border-bottom: 2px solid #a7a0a0;
}
tbody tr td a {
display: flex;
align-items: center;
justify-content: center;
}
tbody tr td .read {
background-color: #643ce7;
padding: 5px 4px;
border-radius: 5px;
margin: 10px auto;
color: #1f242d;
transition: all .45s ease;
}
tbody tr td .update {
background-color: #27ae60;
padding: 5px 4px;
border-radius: 6px;
margin: 10px auto;
color: #1f242d;
transition: all .45s ease;
}
tbody tr td .delete {
background-color: crimson;
padding: 5px 4px;
border-radius: 6px;
margin: 10px auto;
color: #1f242d;
transition: all .45s ease;
}