ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Add, Edit, Delete and View data in database using PHP MYSQLI

Updated on September 15, 2017
Rustom Codilan profile image

Rustom Codilan is an office worker and a developer at the same time. He loves to spend his vacant time to published info-tech.

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;

}

?>

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.

  1. Create Procedure
  2. Read Procedure
  3. Update Procedure
  4. 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.

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>

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?

See results

© 2017 Rustom Codilan

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)