MySQLi procedural basic CRUD operation
Here, I will discuss that how can I perform crud operation in a procedural way using MySQLi.A dynamic website needs to perform a crud operation imagine if we have a web section there we regularly update content dynamically in that case we need to add content or edit our previous content and also sometimes need to delete previously added content. Nowadays many websites are used using MySQL database when using PHP. Here I want to guide a basic crud operation.
Note: This is not a secure code to use on a commercial website it is only for learning purposes.
So in this article, I am going to present Create-Read-Update-Delete Operation / Query (it is also called CRUD Operation).
CRUD represents that perform four basic operations of the database. Let's see basic syntax which will use further.
CRUD represents that perform four basic operations of the database. Let's see basic syntax which will use further.
C: C stands for Create (INSERT), which performs an action that can create or insert new data into the database table.
INSERT INTO table_name (table_filed1,)table_filed2) VALUES ('filed1_value','filed2_value' );
R: R stands for reading (SELECT), which performs an action that can read or retrieve records from the database
Syntax:
SELECT * FROM table_name;
U: U stands for Update (UPDATE), we can update selected records of the database under conditions.
Syntax:
Update table_name SET table_filed1 = 'filed1_value', table_filed2 = 'filed2_value' WHERE id='selected_id';
D : D stands for Delete (DELETE), delete or remove the selected records from the database.
Syntax:
DELETE FROM table_name WHERE id='selected_id';
let's have a look on file structure:
First, we need a database table where we can store data and will perform the above operation run bellow commands or just import SQL file from the downloaded folder.
CREATE TABLE `st_detail` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `st_detail` (`id`, `name`, `email`) VALUES
(9, 'abdul raza ', '[email protected]'),
(11, 'abdul raza ', '[email protected]');
ALTER TABLE `st_detail`
ADD PRIMARY KEY (`id`);
ALTER TABLE `st_detail`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
Now we will create our main home page structure for this I will use bootstrap
Note: Bootstrap is the world’s most popular front-end open-source framework for making a user-friendly website
Create index.php file
<?php
require_once('connection.php');
?>
<!doctype html>
<html dir="ltr">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.10.0/css/all.min.css" integrity="sha512-PgQMlq+nqFLV4ylk1gwUOgm6CtIIXkKwaIHp/PAIWHzig/lKZSEGKEysh0TCVbHJXCLN7WetD8TFecIky75ZfQ==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<title></title>
</head>
<body>
<div class="container">
<div class="row py-5">
<div class="col-md-10 offset-md-1 text-end pb-3 pt-5">
<a href="add.php" class="btn btn-info">Add new record</a>
</div>
<div class="col-md-10 offset-md-1 border p-3">
<table class="table table-striped table-hover">
<thead class="table-light">
<tr>
<th>Sn </th>
<th>Action </th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php
$query = "SELECT * FROM st_detail";
$result = mysqli_query($con, $query);
$num_rows = mysqli_num_rows($result);
$num = 0;
if ($num_rows > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$num++;
?>
<tr>
<th><?php echo $num; ?></th>
<th>
<a href="edit.php?id=<?php echo $row['id']; ?>"><i class="fas fa-edit"></i></a>
<a href="delete.php?id=<?php echo $row['id']; ?> " onClick="return confirm('Are u sure your are oing to delete this and this action can not be undone...')"><i class="fas fa-trash"></i></a>
</th>
<th><?php echo $row['name']; ?></th>
<th><?php echo $row['email']; ?></th>
</tr>
<?php
}
}
?>
</tbody>
</table>
</div>
</div>
</div>
<!-- Option 1: Bootstrap Bundle with Popper -->
<!-- <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script> -->
</body>
</html>
The output of the above code we have already seen above in the first screenshot
Now we need to create a connection file that will connect our database
Create connection.php
<?php
define("HOST", "localhost");
define("USER", "bfriendly_u");
define("PASS", "12345");
define("DBNAME", "bootstrapfriendly_demo");
$con = mysqli_connect(HOST, USER, PASS, DBNAME);
if (!$con) {
echo "connection error";
}
Now include the above code in the index.php file
Then I will create add.php for adding a new record
<?php
require_once('connection.php');
$name = $email = $msg = "";
if (isset($_POST['submit'])) {
if (isset($_POST['name'])) {
$name = $_POST['name'];
}
if (isset($_POST['email'])) {
$email = $_POST['email'];
}
$query = "INSERT INTO st_detail (name, email ) VALUES ('$name', '$email' )";
$result = mysqli_query($con, $query);
if ($result) {
$msg = '<div class="alert alert-success" role="alert">
Weldone! Reocrd added successfully
</div>';
} else {
$msg = '<div class="alert alert-warning" role="alert">
Error occurs while updating the query
</div>';
}
}
?>
<!doctype html>
<html lang="eng">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
<title></title>
</head>
<body>
<div class="container">
<div class="row py-5">
<div class="col-md-12 border p-3">
<span><?php echo $msg; ?></span>
<h2>Add new record:</h2>
<form action="" method="post">
<div class="mb-3">
<label for="exampleInputPassword1" class="form-label">Name</label>
<input type="text" class="form-control" id="exampleInputPassword1" name="name">
</div>
<div class="mb-3">
<label for="exampleInputEmail1" class="form-label">Email address</label>
<input type="email" class="form-control" id="exampleInputEmail1" name="email">
</div>
<button type="submit" name="submit" class="btn btn-primary">Save changes</button>
</form>
</div>
<div class="col-md-12 text-center pt-5 ">
<a href="index.php" class="btn btn-info">Go back</a>
</div>
</div>
</div>
<!-- Option 1: Bootstrap Bundle with Popper -->
<!-- <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script> -->
</body>
</html>
Now create an edit.php page and paste bellow code
<?php
require_once('connection.php');
$id = $_GET['id'];
$name = $email = $msg = "";
if (isset($_POST['submit'])) {
if (isset($_POST['name'])) {
$name = $_POST['name'];
}
if (isset($_POST['email'])) {
$email = $_POST['email'];
}
$query = "UPDATE st_detail SET name = '$name', email = '$email' WHERE id= '$id'";
$result = mysqli_query($con, $query);
if ($result) {
$msg = '<div class="alert alert-success" role="alert">
Weldone! Updated successfully
</div>';
"";
} else {
$msg = '<div class="alert alert-warning" role="alert">
Error occurs while updating the query
</div>';
}
}
?>
<!doctype html>
<html lang="ar" dir="ltr">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
<title></title>
</head>
<body>
<div class="container">
<div class="row py-5">
<div class=" col-md-10 offset-md-1 border p-3">
<span><?php echo $msg; ?></span>
<?php
$query = "SELECT * FROM st_detail WHERE id= '$id'";
$result = mysqli_query($con, $query);
$num = 0;
while ($row = mysqli_fetch_assoc($result)) {
$num++;
?>
<h2>Edit student record:</h2>
<form action="" method="post">
<div class="mb-3">
<label for="exampleInputPassword1" class="form-label">Name</label>
<input type="text" class="form-control" id="exampleInputPassword1" name="name" value="<?php echo $row['name']; ?>">
</div>
<div class="mb-3">
<label for="exampleInputEmail1" class="form-label">Email address</label>
<input type="email" class="form-control" id="exampleInputEmail1" name="email" value="<?php echo $row['email']; ?>">
</div>
<button type="submit" name="submit" class="btn btn-primary">Save changes</button>
</form>
<?php
}
?>
</div>
<div class="col-md-12 text-center pt-5 ">
<a href="index.php" class="btn btn-info">Go back</a>
</div>
</div>
</div>
<!-- Option 1: Bootstrap Bundle with Popper -->
<!-- <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script> -->
</body>
</html>
Now create delete.php and paste bellow code
<?php
require_once('connection.php');
$id = $_GET['id'];
$msg = "";
if (isset($id)) {
$query = "DELETE FROM st_detail WHERE id= '$id'";
$result = mysqli_query($con, $query);
if ($result) {
$msg = '<div class="alert alert-success" role="alert">
Weldone! Updated successfully
</div>';
"";
} else {
$msg = '<div class="alert alert-warning" role="alert">
Error occurs while updating the query
</div>';
}
}
?>
<!doctype html>
<html lang="ar" dir="ltr">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
<title></title>
</head>
<body>
<div class="container">
<div class="row py-5">
<div class="col-md-12 border p-3">
<span><?php echo $msg; ?></span>
</div>
<div class="col-md-12 text-center pt-5 ">
<a href="index.php" class="btn btn-info">Go back</a>
</div>
</div>
</div>
<!-- Option 1: Bootstrap Bundle with Popper -->
<!-- <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script> -->
</body>
</html>