How to Add, Edit, Delete and View data in database using PHP MYSQLI
Introduction
This article will help you to add, edit, delete and view data in database in easy and simple steps. Follow this tutorial and start your first CRUD (Create, Read, Update, and Delete) coding.
To start this, we must create first our database in localhost. In my procedure I prefer to use CMD in performing transactions in my localhost rather than using localhost directly because it helped us to further enhance our SQL knowledge and skills. But don’t worry I will also include how to create database in localhost directly, but first we should start learning through CMD.
To create a database, open CMD and type cd C: / to change the directory to drive C: /.
After that, type cd xampp and press enter. It will change your directory into C:/xampp. If you are done changing the directory we need to change it again into C: /xampp/mysql just repeat the same action we done before. Type cd mysql then press enter. We’re too close to create our database. Type cd bin to change again our directory, after we reach the bin directory simply type mysql –h localhost –u root –p if you don’t have a password in your localhost you leave –p or don’t type it. Then type this simple SQL code to create your database, create database databasename;. You can assign any database name as you wish. Then you’re done. We can start now our main lesson.
We cannot insert, update, delete and view data in the database unless we are already connected. It is very easy to connect to the database just follow this simple MYSQLI code.
config.php (connect to database)
<?php
$host = "localhost"; //localhost is your hostname; you can also use its IP Address (127.0.0.1)
$username = "root"; //your default user will be root
$password = ""; //Leave password blank if you don’t have assigned password to your database
$dbname = "databasename"; //Use your preferred databasename
$db = new mysqli($host, $username, $password, $dbname);
if($db->connect_error) {
echo "Connection Failed".$db->connect_error;
}
?>See video Tutorial
- How to Connect to database using PHP OOP - YouTube
I'm gonna show you how to connect to database using PHP Mysqli Object Oriented Programming. Please watch and subscribe to my youtube channel for more tutoria...
Save your database connection as config.php. After establishing your connection to the database you can start coding to insert, edit, delete and view your data in the database. We must follow the basic procedure in coding CRUD transaction. This will be the scenario in coding your first CRUD transaction.
- Create Procedure
- Read Procedure
- Update Procedure
- Delete Procedure
Let’s create first your HTML file. We need a HTML file because it will serve as your interface to display and store your data into the database. You can add CSS to your HTML file to make it more presentable to your audience or end users. Your HTML file should be saves as .PHP so we can interact to our SQL queries. Name your HTML file as sample.php.
sample.php (HTML page)
<!DOCTYPE html> <html lang = ‘en’> <head> <title>PHP MYSQLI CRUD</title> </head> <body> <form action = “add.php” method = “POST”> <input type = “text” name = “fname”> <input type = “text” name = “mname”> <input type = “text” name = "lname”> <input type = “submit” name = “save” value = “Submit”> <form> </body> </html>
After creating the interface of the system, you can start your Create Procedure. Let’s start coding your insert query. Create another file and save it as class.php this will be the file that will perform all of the procedure and processes. It covers the class and functions of your queries.
class.php (Class and Function take action)
<?php
class crud{
public function insert($fname, $mname, $lname) {
global $db;
$sql = "INSERT into person SET fname = '$fname', mname = '$mname', lname = '$lname'";
$result = $db->query($sql);
if($result) {
return $result;
}
else {
return false;
}
}
public function read() {
global $db;
$sql = "SELECT * FROM person";
$result = $db->query($sql);
if($result->num_rows > 0) {
$list = '';
$i = 0;
while($row = $result->fetch_assoc()) {
$list[$i] = $row;
$i++;
}
return $list;
}
}
public function readedit($id) {
global $db;
$sql = "SELECT * FROM person WHERE pid = '$id'";
$result = $db->query($sql);
if($result->num_rows > 0) {
$list = '';
$i = 0;
while($row = $result->fetch_assoc()) {
$list[$i] = $row;
$i++;
}
return $list;
}
}
public function update($id, $fname, $mname, $lname) {
global $db;
$sql = "UPDATE person SET fname = '$fname', mname = '$mname', lname = '$lname' WHERE pid = '$id'";
$result = $db->query($sql);
if($result) {
return $result;
}
else {
return false;
}
}
public function delete($id) {
global $db;
$sql = "DELETE FROM person WHERE pid = '$id'";
$result = $db->query($sql);
if($result) {
return $result;
}
else {
return false;
}
}
}
?>Let’s start creating your process file. Name your file as add.php, this will process or gather the information you inserted on your HTML file. The information gathered will be received by your class.php file.
add.php (Process for storing/ adding data)
<?php
require_once('class.php');
require_once('config.php');
$fname = $_POST['fname'];
$mname = $_POST['mname'];
$lname = $_POST['lname'];
if(!empty($fname) && !empty($mname) && !empty($lname)) {
$add = crud::insert($fname, $mname, $lname);
header("LOCATION: read.php");
}
?>We are done creating our CREATE Procedure, let’s proceed to READ Procedure. This procedure allows us to display information from the database. We are pulling out the information we inserted and then display it to our HTML file. The code for displaying the data is very simple and easy. See the code below.
See video Tutorial
- How to insert data into database using PHP MYSQLI OOP - YouTube
How to insert data into database using PHP MYSQLI OOP. This video shows you on how to insert data into the database. Please Click subscribe button for more v...
read.php (HTML page for displaying data)
<?php
require_once('class.php');
require_once('config.php');
$list = crud::read();
?>
<!DOCTYPE html>
<html lang = 'en'>
<head>
<title>PHP MYSQLI CRUD</title>
</head>
<body>
<a href = "sample.php">Add</a>
<table>
<thead>
<tr>
<th>Firstname</th >
<th >Firstname</th >
<th >Firstname</th >
</tr>
</thead>
<tbody>
<?php
if($list > 0) {
foreach($list as $key => $disp) {
?>
<tr>
<td><?=$disp['fname'];?></td>
<td><?=$disp['mname'];?></td>
<td><?=$disp['lname'];?></td>
<td><a href = 'edit.php?id=<?=$disp['pid'];?>'>Edit</a></td>
<td><a href = 'delete.php?id=<?=$disp['pid'];?>'>Delete</a></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</body>
</html>See video Tutorial
- How to view data from database using PHP MYSQLI OOP - YouTube
How to view data from database using PHP MYSQLI OOP. This video shows you on how to view data from the database. Please Click subscribe button for more video...
We’re almost done! We already inserted and display records in the database. And then, let’s proceed to Update and Delete Procedure. To update an information from the database we need to get the information of data from the database. We need to use the Get Method in order to get the exact value of information we needed to update. We need another HTML file to display the data.
edit.php (Edit page)
<?php
require_once('class.php');
require_once('config.php');
$id = $_GET['id'];
$list = crud::readedit($id);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP MYSQLI CRUD</title>
</head>
<body>
<form action = "add.php" method = "POST">
<?php
foreach($list as $key => $disp) {
?>
<input type = "text" name = "fname" value = '<?=$disp['fname']?>'>
<input type = "text" name = "mname" value = '<?=$disp['mname']?>'>
<input type = "text" name = "lname" value = '<?=$disp['lname']?>'>
<?php
}
?>
<input type = "submit" name = "save" value = "Submit">
<form>
</body>
</html>
After creating the HTML file we need to create a process file of the update function. The code will look like this.
update.php (Process for updating data)
<?php
require_once('class.php');
require_once('config.php');
$id = $_POST['id'];
$fname = $_POST['fname'];
$mname = $_POST['mname'];
$lname = $_POST['lname'];
if(!empty($fname) && !empty($mname) && !empty($lname) && !empty($id)) {
$add = crud::update($id, $fname, $mname, $lname);
header("LOCATION: read.php");
}
?>Then to delete an information from the database we must do the same approach we do in updating the information, we need to get the unique id using the GET Method. The difference of this procedure is only the query. But most of its elements is the same as Update Procedure. See the code below.
delete.php (Process for deleting data)
<?php
require_once('class.php');
require_once('config.php');
$id = $_GET['id'];
$add = crud::delete($id);
header("LOCATION: read.php");
?>This lesson is teaching only the basic procedure of coding the necessary queries in web programming. There’s a lot of ways to create web projects but this will be your starting point to build your own professional websites. DONE!!!!
Feedback
Does this article is very helpful to you?
© 2017 Rustom Codilan
