Hi! This tutorial will show you How to Create Simple CRUD Operations in PHP and MySQL. CRUD stands for CREATE, READ, UPDATE and DELETE operations performed on Database Management System. To be more precise, To insert records into database, Retrieve records from database, Edit or Update records in database and Delete records from database are the four primitive sql queries performed, together named as CRUD.
Building PHP and MySQL CRUD application is one of the basic skills required for a PHP Programmer and below we'll see about creating simple crud operations using PHP & MySQL.
Simple CRUD Operations using PHP & MySQL:
We'll need database for building CRUD Application. So first we'll create one.
Create MySQL Database:
This is the mysql database we are going to use for the PHP MySQL CRUD demo.
CREATE `db_crud`; USE `db_crud`; CREATE TABLE IF NOT EXISTS `tbl_language` ( `id` int(9) NOT NULL AUTO_INCREMENT, `lname` varchar(50) NOT NULL, `lcode` varchar(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6; INSERT INTO `tbl_language` (`id`, `lname`, `lcode`) VALUES (1, 'English', 'en'), (2, 'Afrikaans', 'af'), (3, 'Greek', 'el'), (4, 'Finnish', 'fi'), (5, 'Spanish', 'es');
Note: I have included some sample rows to the table. If you don't want, just create the table and skip the insert step.
Database Connectivity:
Here's the database connectivity file that establishes connection from php to mysql using MySQLi library. Please make the changes according to your need. For our CRUD application we'll need to open mysql connection across several files. So it's better to create a separate file and include it on the places we need.
dbconnect.php
<?php
//mysql database connection
$host = "localhost";
$user = "root";
$pass = "";
$db = "db_crud";
$con = mysqli_connect($host, $user, $pass, $db) or die("Error " . mysqli_error($con));
?>
1) CREATE (INSERT Records into Database)
The first process of CRUD is Create which means inserting new records into database. So we need to create user interface to add new language records for our 'tbl_language' table.
insert_language.php
<?php
include_once 'dbconnect.php';
// insert record
if (isset($_POST['submit'])) {
$name = mysqli_real_escape_string($con, $_POST['lname']);
$code = mysqli_real_escape_string($con, $_POST['lcode']);
if(mysqli_query($con, "INSERT INTO tbl_language(lname, lcode) VALUES('$name', '$code')")) {
$success = "Record inserted successfully!";
} else {
$error = "Error inserting record...";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP MySQL Simple CRUD | Insert Demo</title>
<meta content="width=device-width, initial-scale=1.0" name="viewport" >
<link rel="stylesheet" href="css/bootstrap.css" type="text/css" />
<style type="text/css">
form {
margin: 0 auto;
width: 60%;
}
</style>
</head>
<body>
<div class="container" style="margin-top: 20px;">
<div class="row">
<div class="col-xs-8 col-xs-offset-2">
<div class="panel panel-default">
<div class="panel-heading text-center">
<h3>PHP CRUD: Add Language</h3>
</div>
<div class="panel-body">
<form name="insertform" method="post" action="insert_language.php">
<div class="form-group">
<input type="text" name="lname" placeholder="Enter Language Name" required class="form-control" />
</div>
<div class="form-group">
<input type="text" name="lcode" placeholder="Language Code" required class="form-control" />
</div>
<div class="form-group">
<input type="submit" name="submit" value="Insert" class="btn btn-info btn-block" />
</div>
<span class="text-success"><?php if (isset($success)) { echo $success; } ?></span>
<span class="text-danger"><?php if (isset($error)) { echo $error; } ?></span>
</form>
</div>
<div class="panel-footer text-center">
<a href="index.php">« Back to index page</a>
</div>
</div>
</div>
</div>
</div>
<script src="js/jquery-1.10.2.js"></script>
<script src="js/bootstrap.js"></script>
</body>
</html>
Although we have three fields in 'tbl_language', the field 'id' is auto generated primary key. So we have included only two textboxes for name and code fields for the user to provide input.
2) READ (FETCH Records from Database)
The next one is the Read operation. Here we have to fetch records from the language table and display it in table format along with options to 'Update' and 'Delete' each row.
index.php
<?php
include_once 'dbconnect.php';
// fetch records
$sql = "SELECT * FROM tbl_language order by id";
$result = mysqli_query($con, $sql);
// delete record
if (isset($_GET['langid'])) {
$sql = "DELETE FROM tbl_language WHERE id=" . $_GET['langid'];
@mysqli_query($con, $sql);
header("Location: index.php");
}
?>
<!DOCTYPE html>
<html>
<head>
<meta content="width=device-width, initial-scale=1.0" name="viewport" >
<title>Simple CRUD Operations in PHP & Mysql | kodingmadesimple.com</title>
<link rel="stylesheet" href="css/bootstrap.css" type="text/css" />
</head>
<body>
<div class="container" style="margin-top: 20px;">
<div class="row">
<div class="col-xs-8 col-xs-offset-2">
<div class="panel panel-default">
<div class="panel-heading"><h3>PHP CRUD OPERATIONS by Kodingmadesimple.com</h3></div>
<div class="panel-body text-right">
<a href="insert_language.php" class="btn btn-default">Add Language</a>
</div>
<table class="table table-bordered table-hover">
<thead>
<tr>
<th>#</th>
<th>Language Name</th>
<th>ISO Code</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<?php
$cnt = 1;
while($row = mysqli_fetch_array($result)) { ?>
<tr>
<td><?php echo $cnt++; ?></td>
<td><?php echo $row['lname']; ?></td>
<td><?php echo $row['lcode']; ?></td>
<td><a href="update_language.php?langid=<?php echo $row['id']; ?>"><img src="images/edit-icon.png" alt="Edit" /></a></td>
<td><a href="javascript: delete_lang(<?php echo $row['id']; ?>)"><img src="images/delete-icon.png" alt="Delete" /></a></td>
</tr>
<?php } ?>
</tbody>
</table>
<div class="panel-footer"><?php echo mysqli_num_rows($result) . " records found"; ?></div>
</div>
</div>
</div>
</div>
<script src="js/jquery-1.10.2.js"></script>
<script src="js/bootstrap.js"></script>
<script type="text/javascript">
function delete_lang(id)
{
if (confirm('Confirm Delete?'))
{
window.location.href = 'index.php?langid=' + id;
}
}
</script>
</body>
</html>
3) UPDATE (Edit Records in Database)
And then is the Update process. This is where we modify the existing records in the database. In order to perform update, user has to click on the 'Edit' icon provided on a specific table row and will be taken to update form where the existing data will be automatically populated in the input fields. Then user has to modify the field values and click on 'Update' button to edit that specific record in database.
update_language.php
<?php
include_once 'dbconnect.php';
if (isset($_GET['langid'])) {
$sql = "SELECT * FROM tbl_language WHERE id=" . $_GET['langid'];
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result);
}
// update record
if (isset($_POST['submit'])) {
$id = mysqli_real_escape_string($con, $_POST['lid']);
$name = mysqli_real_escape_string($con, $_POST['lname']);
$code = mysqli_real_escape_string($con, $_POST['lcode']);
if(mysqli_query($con, "UPDATE tbl_language SET lname='$name', lcode='$code' WHERE id=" . $id)) {
$success = "Record updated successfully!";
} else {
$error = "Error updating record...";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP MySQL Simple CRUD | Update Demo</title>
<meta content="width=device-width, initial-scale=1.0" name="viewport" >
<link rel="stylesheet" href="css/bootstrap.css" type="text/css" />
<style type="text/css">
form {
margin: 0 auto;
width: 60%;
}
</style>
</head>
<body>
<div class="container" style="margin-top: 20px;">
<div class="row">
<div class="col-xs-8 col-xs-offset-2">
<div class="panel panel-default">
<div class="panel-heading text-center">
<h3>PHP CRUD: Update Language</h3>
</div>
<div class="panel-body">
<form name="insertform" method="post" action="update_language.php">
<div class="form-group">
<input type="hidden" name="lid" value="<?php if(isset($row['id'])) { echo $row['id']; } ?>" />
<input type="text" name="lname" placeholder="Enter Language Name" value="<?php if(isset($row['lname'])) { echo $row['lname']; } ?>" required class="form-control" />
</div>
<div class="form-group">
<input type="text" name="lcode" placeholder="Language Code" value="<?php if(isset($row['lcode'])) { echo $row['lcode']; } ?>" required class="form-control" />
</div>
<div class="form-group">
<input type="submit" name="submit" value="Update" class="btn btn-info btn-block" />
</div>
<span class="text-success"><?php if (isset($success)) { echo $success; } ?></span>
<span class="text-danger"><?php if (isset($error)) { echo $error; } ?></span>
</form>
</div>
<div class="panel-footer text-center">
<a href="index.php">« Back to index page</a>
</div>
</div>
</div>
</div>
</div>
<script src="js/jquery-1.10.2.js"></script>
<script src="js/bootstrap.js"></script>
</body>
</html>
4) DELETE (Delete Records from Database)
Delete is the final CRUD operation. This is about deleting existing table records. In order to delete a particular row or record, user has to click on the 'Delete' icon visible on the table row and that record will be deleted from the database after getting confirmation from the user to avoid accidental clicks.
We don't have separate file for delete process. It is handled in index.php file itself.
That's it. Now run index page and if you already have records inserted you will be shown with a page similar to this,
Now click on the 'Add Language' button at the top to insert new records. This will take you to the insert form.
Provide the necessary data and click 'Insert' button to add record. And the language table on the index page will be updated with the new record you just added.
To edit records, just click on the edit icon on any row and it will take you to the update form. The fields will be pre-populated with existing data like this,
Do the required changes and click on 'Update' button to complete updating the record.
Finally to delete record, click on the 'Delete' icon on the language table row and you will be asked for confirmation before delete like this,
Once you click 'OK', that specific row will be deleted and the language table will be refreshed to display only the existing records from DB. (Check this tutorial to delete multiple records at a time with checkbox in PHP.)
Note: This demo uses Twitter Bootstrap CSS for creating user interface which is optional.
Also Read: User Login and Signup System using PHP & MySQL
Don't Miss: Upload, View and Download Files in PHP & MySQL
That was all about building simple crud in php and mysql. I hope you like this PHP MySQL CRUD Operations tutorial. Meet you in another interesting post!
No comments:
Post a Comment