A Simple Crude Application Function With MYSQL and PHP.
Jennifer Eze
Posted on September 16, 2022
Introduction
CRUD Operations are typically performed on databases, hence, in this PHP CRUD Operations tutorial, you will implement CRUD techniques on MySQL databases with the help of PHP.
CRUDE is an acronym for the four basic types of SQL commands: Create, Read, Update, Delete. Most applications have some kind of CRUD functionality, and we can assume that every programmer had to deal with CRUD at some point.
A CRUD application uses forms to get data into and out of a database.
We use CRUD apps every day. Most of the time, without notice. They keep us organized, they help digitize business processes, and they’re critical to application development.
What is a crude application?
CRUD apps are the user interface that we use to interact with databases. Broadly, CRUD apps consist of the database, the user interface, and the APIs.
Modern web applications have a user perform at least one of the following operations on a database – creating a new record, reading or viewing existing records, updating, or deleting a record. If you have worked with a database, you have most likely worked with CRUD without realizing it.
CRUD apps are used daily by several businesses and organizations to maintain their day-to-day workflow. HR uses CRUD apps to manage staff records and keep and track the keeping of employee leaves, and attendance.
Customer success uses CRUD apps to solve customer tickets, and so on. Take a look at the blogging sites. You as a user can create your post, read your post, update your post and select your post. Bloggs are classic CRUD apps.
How to Build a Crud Application.
This is a step-by-step procedure on how to implement a simple user crud operation app with validation. Using this crud app, you can learn how to insert, read, update and delete data from a database in PHP.
Follow the following steps to build a crud operation using PHP and MySQL;
- Step 1 – Create Database Table
- Step 2 – Create an index page(with a form)
- Step 3 – Create a config Page
- Step 4 – Create an action Page
- Step 5 – Create an update Page
- Step 6 – Create a Delete file.
Step 1: Create Database Table
First, we need to create a database and database table where we save the ’ data or records.
In the database, we will keep only four pieces of information in our table (people) – name,Date of birth, ‘food, and ‘friend.
- Database Name – yearbook
-
Table Name – people
CREATE TABLE users (
uid INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) As defined,
email VARCHAR(255) As defined,
dob VARCHAR(255) As defined,
food VARCHAR (255) As defined,
friend VARCHAR (255) As defined,
);
Step 2: Create Index File
our index will contain our form, from which users can log in.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="css/bootstrap.min.css" rel="stylesheet" >
<link rel="stylesheet" href="css/owl.carousel.min.css">
<link rel="stylesheet" href="css/owl.theme.default.min.css">
<link rel="stylesheet" type="text/css" href="css/aos.css">
<link rel="stylesheet" type="text/css" href="css/fontawesome/css/all.min.css">
<link rel="stylesheet" href="css/style.css">
<title>Hello, world!</title>
</head>
<body>
<section class="bg-dark py-5">
<div class="container">
<div class="row">
<div class="col-md-4 offset-md-4 bg-warning p-5">
<form action="action.php" method="post">
<div class="input-group mb-3">
<span>
<i class="fa fa-envelope"></i>
</span>
<input type="text" name="name" class="form-control" placeholder="full name">
</div>
<div class="input-group mb-3">
<span>
<i class="fa fa-calendar-alt"></i>
</span>
<input type="text" name="name" class="form-control" placeholder="Date of Birth"
</div>
<div class= "input-group mb-3">
<span>
<i class="fa fa-fish"></i>
</span>
<input type="text" name="name" class="form-control" placeholder="Forvourite food"
</div>
<div class="input-group mb-3">
<span>
<i class="fa fa-handshake"></i>
</span>
<input type="text" name="name" class="form-control" placeholder="best friend"
</div>
</form>
</div>
</div>
</div>
</section>
<script src="js/bootstrap.bundle.min.js" ></script>
<script src="Js/jquery.min.js"></script>
<script src="js/owl.carousel.min.js"></script>
<script src="js/aos.js"></script>
<script src="js/script.js"></script>
</body>
</html>
Step 3: Create Config File
The config file is enables us connect to the database.
<?php
$db= new mysqli ("localhost", "root", "", "yearbook");
/* server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD,);
// check connection.
?>
Step 4: Create an Action File
On your action page, you check for the submit button. we also collect all data from the form. we do the insert query as well. if you insert data, it will insert and redirect you back to the index page using the header location.
<?php
include_once("config.php");
if(isset($_POST['submit'])){
$name=$_POST['name'];
$DOB=$_POST['DOB'];
$food=$_POST['food'];
$friend=$_POST['friend'];
$db->query (INSERT INTO people(name,DOB,food,friend) VALUES ('$name', '$DOB', '$food','$friend');
header(location:index.php);
}
?>
THE READ SECTION
To read information from your database. you need to include your config.php in your index.php file. just before your Doctype using include_once(“config.php”). This must be done because one cannot read a database without the config.
The next step is to write a select query. Because we want to select all from people(which is the table name) we will write the following code.
<?php
include_once("config.php");
$list=db->query("SELECT *FROM people order by DESC LIMIT 10")
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="css/bootstrap.min.css" rel="stylesheet" >
<link rel="stylesheet" href="css/owl.carousel.min.css">
<link rel="stylesheet" href="css/owl.theme.default.min.css">
<link rel="stylesheet" type="text/css" href="css/aos.css">
<link rel="stylesheet" type="text/css" href="css/fontawesome/css/all.min.css">
<link rel="stylesheet" href="css/style.css">
<title>Hello, world!</title>
</head>
<body>
<section class="bg-dark py-5">
<div class="container">
<div class="row">
<div class="col-md-4 offset-md-4 bg-warning p-5">
<form action="action.php" method="post">
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-envelope"></i>
</span>
<input type="text" name="name" class="form-control" placeholder="full name">
</div>
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-calendar-alt"></i>
</span>
<input type="text" name="DOB" class="form-control" placeholder="Date of Birth">
</div>
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-fish"></i>
</span>
<input type="text" name="food" class="form-control" placeholder="Favourite food">
</div>
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-handshake"></i>
</span>
<input type="text" name="friend" class="form-control" placeholder="Best friend">
</div>
<input type="submit" name="submit" value="login" class="btn btn-dark col-12">
</form>
<?php
while ($row=$list->fetch_assoc()):?>
<div class="my-3">
<h3 class="text-muted"><i class="fa fa-user"></i><?=$row['name'];?></h3>
<p class="text-secondary">
<i class="fa fa-envelope text-dark"></i><?=$row['dob'];?>
<i class="fa fa-fish text-dark"></i><?=$row['food'];?>
<i class="fa fa-handshake text-dark"></i><?=$row['friend'];?>
<a href="delect.php?uid=<?=$row['uid']?> class="text-danger" title="delete"><i class="fa fa-trash"></i></a>
<a href="update.php?uid=<?=$row['uid']?>" class="badge bg-success badge-pill">update<i class="fa fa-edit"></i></a>
</p>
</div>
<?php endwhile;?>
</div>
</div>
</div>
</section>
<script src="js/bootstrap.bundle.min.js" ></script>
<script src="Js/jquery.min.js"></script>
<script src="js/owl.carousel.min.js"></script>
<script src="js/aos.js"></script>
<script src="js/script.js"></script>
</body>
</html>
The “list” above is called a resource. our objective is to convert the resources into an associative array using <?PHP while ($row =$list → fetch_assoc);
$row is now the associative array.
The above while loop brings everything to the database and displays it on the index page.
Step 4: Create an Update File
In our update file, we have a button called the update.php. but that button is a link to our index .php. if (isset($_GET)[‘uid’]; means the user is suspicious, then you kill the page. you must be aware of the previous information before you can do an update.
To get the previous information you do a select query. that will enable you to specify the person you to want to update. with the value attribute, the update change will be effected and changes will be made in the database.
<?php
include_once("config.php");
if(isset($_GET['uid'])){
$uid=$_GET['uid'];
}else{
die(header('location:index.php'));
}
$list=$db->query("SELECT * FROM people WHERE uid=$uid");
$person=$list->fetch_assoc()
//helps you read your database and put a limit to how much information that would
display.
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="css/bootstrap.min.css" rel="stylesheet" >
<link rel="stylesheet" href="css/owl.carousel.min.css">
<link rel="stylesheet" href="css/owl.theme.default.min.css">
<link rel="stylesheet" type="text/css" href="css/aos.css">
<link rel="stylesheet" type="text/css" href="css/fontawesome/css/all.min.css">
<link rel="stylesheet" href="css/style.css">
<title>Hello, world!</title>
</head>
<body>
<section class="bg-dark py-5">
<div class="container">
<div class="row">
<div class="col-md-4 offset-md-4 bg-warning p-5">
<form action="update_action.php" method="post">
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-envelope"></i>
</span>
<input type="text" name="name" class="form-control" placeholder="full name" value="<?=$person['name']?>">
</div>
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-calendar-alt"></i>
</span>
<input type="text" name="DOB" class="form-control" placeholder="Date of Birth" value="<?=$person ['dob']?>">
</div>
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-fish"></i>
</span>
<input type="text" name="food" class="form-control" placeholder="Favourite food" value="<?=$person['food']?>">
</div>
<div class="input-group mb-3">
<span class="input-group-text">
<i class="fa fa-handshake"></i>
</span>
<input type="text" name="friend" class="form-control" placeholder="Best friend" value="<?=$person['friend']?>">
</div>
<input type="hidden" name="uid" value="<?=$person['uid']?>">
<input type="submit" name="submit" value="login" class="btn btn-dark col-12">
</form>
</div>
</div>
</div>
</section>
<script src="js/bootstrap.bundle.min.js" ></script>
<script src="Js/jquery.min.js"></script>
<script src="js/owl.carousel.min.js"></script>
<script src="js/aos.js"></script>
<script src="js/script.js"></script>
</body>
</html>
Step 5: Create a Delete File
<?php
include_once("config.php");
if (isset($_GET['uid'])){
$uid=$_GET['uid'];
$db->query("DELETE FROM people WHERE uid='$uid'");
header("location: index.php");
}
?>
Conclusion
Finally, we have come to the completion of this tutorial and hopefully, you got value from it.
Thanks for reading, please hit the like, clap, or heart button to show some love, I will see you in the next tutorial…
We learned how to build a simple crude application with SQL and PHP.
If you have any questions, please leave them in the comments. Like and share, and till next time, all the best!
About the Author
I am Jennifer Eze, an enthusiastic developer with a passion for JavaScript, PHP, HTML & CSS.
I work as a freelancer, building websites for clients, and love writing technical tutorials to teach others what I do. I am eager to hear from you. Reach me on LinkedIn, Github, or my website.
Posted on September 16, 2022
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
