Posted on by

Simple CRUD operations in PHP using MYSQL – [Procedural]

In this tutorial we will see how to create simple CRUD operations in PHP using MYSQL. I will use procedural mysqli functions to create, read, update and delete. The reason of using procedural mysqli functions is to give PHP beginners a quick overview of how crud operation works in PHP. I will try my best to explain all the aspects of this topic in very simple way. So let’s start with very basic.

PHP CRUD Operations:

CRUD is an acronym of Create, Read, Update, and Delete. PHP provides a set of functions for each database which php supports. I will be using MYSQL database and PHP has different set of functions to achieve crud operations in mysql. In short we will be doing following things.

  • Create: Create a new record in mysql database
  • Read: Read record(s) from mysql database
  • Update: Update single record in mysql database
  • Delete: Delete single record in mysql database

PHP functions for MYSQL:

Following are the PHP functions we will be using to achieve our goal.

  • mysqli_connect(host, dbuser,dbpass,database): is used to connect to database
  • mysqli_query(db_connection, sql_query): is used to run query
  • mysqli_affected_rows(db_connection): returns the number of affected rows from previous SQL statement
  • mysqli_insert_id(db_connection): returns the auto generated id from last query
  • mysqli_error(): returns the description of the last error

PHP CRUD Example:

Now let’s jump into the coding part. I am going to use blog example in which I will be creating following PHP files and folder.

  • Config.php: This file has a Database Connection
  • Create.php: This file will use to save new blog
  • Edit.php: This file will use to edit blog
  • Index.php: This file will use to display all blogs. And this file will also have delete blog code.
  • Css: This folder has style.css file

PHP CRUD Example Source Code

Create Database:

First of all lets create a database;

1
2
3
4
 
Create Database demo;
use demo;
 

Create Database Table:

Now create blog table. You can copy paste below code to your phpmyadmin or any sql client.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE `blog` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`blog_content` TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`author` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`created` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
 

Insert Records in Database:

Let’s add some dummy blogs.
1
2
3
4
5
6
7
8
9
 
INSERT INTO `blog` VALUES (1, 'Blog post 1', 'Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum.\r\nLorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum', 'Edgar', '2021-05-01 10:34:15');
INSERT INTO `blog` VALUES (2, 'Blog post 2', 'Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum.', 'Ignatius', '2021-05-01 01:34:57');
INSERT INTO `blog` VALUES (3, 'Blog post 3', 'Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum.', 'Florencio', '2021-05-02 01:35:15');
INSERT INTO `blog` VALUES (4, 'Blog post 4', 'Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum.', 'David', '2021-05-02 20:35:37');
INSERT INTO `blog` VALUES (5, 'Blog post 5', 'Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum.', 'Gregory', '2021-05-02 00:35:49');
INSERT INTO `blog` VALUES (6, 'Blog post 6', 'Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum.', 'Theo', '2021-05-03 01:36:08');
INSERT INTO `blog` VALUES (7, 'Blog post 7', 'This is the content of blog post 7', 'Rowan', '2021-05-03 01:36:23');
 

Database Connection File (config.php):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
<?php
 
$host = 'localhost';
$user = 'root';
$password = '';
$db = 'demo';
 
$con = mysqli_connect($host,$user,$password,$db);
 
if(!$con)
{
die(mysqli_error());
}
?>
 

Create Blog (create.php):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
<?php
session_start();
require_once('config.php');
if(isset($_POST['submit']))
{
$error_msg = [];
if(isset($_POST['title'],$_POST['content'],$_POST['author']) && !empty($_POST['title']) && !empty($_POST['content']) && !empty($_POST['author']))
{
$title = filter_var(trim($_POST['title']),FILTER_SANITIZE_STRING);
$content = filter_var(trim($_POST['content']),FILTER_SANITIZE_STRING);
$author = filter_var(trim($_POST['author']),FILTER_SANITIZE_STRING);
$created = date('Y-m-d H:i:s');
$sql = "insert into blog (title, blog_content, author, created) values ('$title','$content','$author','$created')";
$rs = mysqli_query($con,$sql);
if(mysqli_affected_rows($con) == 1)
{
$lastInsertedID = mysqli_insert_id($con);
$_SESSION['success_msg'] = 'Blog has been added successfully';
header('location:edit.php?id='.$lastInsertedID);
exit();
}
else
{
$error_msg[] = 'Unable to save blog' ;
}
}
else
{
if(!isset($_POST['title']) || empty($_POST['title']))
{
$error_msg[] = 'Title is required' ;
}
if(!isset($_POST['content']) || empty($_POST['content']))
{
$error_msg[] = 'Content is required' ;
}
if(!isset($_POST['author']) || empty($_POST['author']))
{
$error_msg[] = 'Author is required' ;
}
}
}
?>
 
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Add - Demo Simple CRUD Operations in PHP using MYSQLi (Procedural)</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="container">
<h2>Add Blog</h2>
<?php
if(isset($_SESSION['success_msg']))
{
echo '<div class="success-msg">'.$_SESSION['success_msg'].'</div>';
unset($_SESSION['success_msg']);
}
if(isset($error_msg) && !empty($error_msg))
{
foreach($error_msg as $error)
{
echo '<div class="error-msg">'.$error.'</div>';
}
}
?>
<div class="align-center">
<form action="" method="POST">
<div class="form-group">
<label for="title">Title</label>
<input type="text" name="title" placeholder="Enter Title" id="title">
</div>
<div class="form-group">
<label for="content">Content</label>
<textarea name="content" id="content"></textarea>
</div>
<div class="form-group">
<label for="author">Author</label>
<select name="author" id="author">
<option value="">Select Author</option>
<option value="Edgar">Edgar</option>
<option value="Ignatius">Ignatius</option>
<option value="Florencio">Florencio</option>
<option value="David">David</option>
<option value="Gregory">Gregory</option>
<option value="Theo">Theo</option>
<option value="Rowan">Rowan</option>
<option value="Justen">Justen</option>
</select>
</div>
<div class="form-group">
<button type="submit" name="submit">Submit</button>
<a href="index.php" class="back-link" style="float:right"><< Back</a>
</div>
</form>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
</body>
</html>
 

Read Records (index.php):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
<?php
session_start();
require_once('config.php');
if(isset($_GET['action'],$_GET['id']) && $_GET['action'] == 'delete')
{
$id = intval(trim($_GET['id']));
$sql = 'delete from blog where id = '.$id;
$deleteRs = mysqli_query($con,$sql);
if(mysqli_affected_rows($con) == 0)
{
$_SESSION['error_msg'] = 'Unable to delete record';
header('location:index.php');
exit();
}
else
{
$_SESSION['success_msg'] = 'Record has been deleted successfully';
header('location:index.php');
exit();
}
}
$sql = 'select * from blog order by id desc';
$rs = mysqli_query($con,$sql);
?>
 
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Demo Simple CRUD Operations in PHP using MYSQLi (Procedural)</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="container">
<h2>Blogs</h2>
<a href="create.php" class="add-new">Add New</a>
<?php
if(isset($_SESSION['success_msg']))
{
echo '<div class="success-msg">'.$_SESSION['success_msg'].'</div>';
unset($_SESSION['success_msg']);
}
if(isset($_SESSION['error_msg']))
{
echo '<div class="error-msg">'.$_SESSION['error_msg'].'</div>';
unset($_SESSION['error_msg']);
}
?>
<table class="table">
<tr>
<th>#</th>
<th>Title</th>
<th>Author</th>
<th>Created</th>
<th>Action</th>
</tr>
<?php
while($row = mysqli_fetch_assoc($rs))
{
$created = date('d-m-Y',strtotime($row['created']));
?>
<tr>
<td> <?php echo $row['id']?> </td>
<td> <?php echo $row['title']?> </td>
<td> <?php echo $row['author']?> </td>
<td> <?php echo $created?> </td>
<td>  
<a href="edit.php?id=<?php echo $row['id']?>" >Edit</a> |
<a href="index.php?action=delete&id=<?php echo $row['id']?>" class="delete-record">Delete</a>
</td>
</tr>
<?php
}
?>
</table>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>
$(document).ready(function(){
$(".delete-record").click(function(e){
e.preventDefault();
var confirmBox = confirm('Are you Sure?');
if(confirmBox == true)
{
var getHref = $(this).attr('href');
window.location.href=getHref;
}
});
});
</script>
</body>
</html>
 

Edit Blog (edit.php):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
<?php
session_start();
require_once('config.php');
if(!isset($_GET['id']))
{
header('location:index.php');
exit();
}
if(isset($_POST['submit']))
{
$error_msg = [];
if(isset($_POST['title'],$_POST['content'],$_POST['author']) && !empty($_POST['title']) && !empty($_POST['content']) && !empty($_POST['author']))
{
$id = intval(trim($_GET['id']));
$title = filter_var(trim($_POST['title']),FILTER_SANITIZE_STRING);
$content = filter_var(trim($_POST['content']),FILTER_SANITIZE_STRING);
$author = filter_var(trim($_POST['author']),FILTER_SANITIZE_STRING);
$sql = "update blog set title = '".$title."', blog_content='".$content."', author='".$author."' where id = ".$id;
$rs = mysqli_query($con,$sql);
if(mysqli_affected_rows($con) == 1)
{
$_SESSION['success_msg'] = 'Blog has been updated successfully';
header('location:edit.php?id='.$id);
exit();
}
else
{
$error_msg[] = 'Unable to save blog' ;
}
}
else
{
if(!isset($_POST['title']) || empty($_POST['title']))
{
$error_msg[] = 'Title is required' ;
}
if(!isset($_POST['content']) || empty($_POST['content']))
{
$error_msg[] = 'Content is required' ;
}
if(!isset($_POST['author']) || empty($_POST['author']))
{
$error_msg[] = 'Author is required' ;
}
}
}
$sql = 'select * from blog where id = '.$_GET['id'];
$rs = mysqli_query($con,$sql);
$row = mysqli_fetch_assoc($rs);
?>
 
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Edit - Demo Simple CRUD Operations in PHP using MYSQLi (Procedural)</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="container">
<h2>Edit Blog</h2>
<?php
if(isset($_SESSION['success_msg']))
{
echo '<div class="success-msg">'.$_SESSION['success_msg'].'</div>';
unset($_SESSION['success_msg']);
}
if(isset($error_msg) && !empty($error_msg))
{
foreach($error_msg as $error)
{
echo '<div class="error-msg">'.$error.'</div>';
}
}
?>
<div class="align-center">
<form action="" method="POST">
<div class="form-group">
<label for="title">Title</label>
<input type="text" name="title" placeholder="Enter Title" id="title" value="<?php echo $row['title'];?>">
</div>
<div class="form-group">
<label for="content">Content</label>
<textarea name="content" id="content"><?php echo $row['blog_content'];?></textarea>
</div>
<div class="form-group">
<label for="author">Author</label>
<select name="author" id="author">
<option value="">Select Author</option>
<option <?php echo ($row['author'] == 'Edgar')?'selected="selected"':''; ?> value="Edgar">Edgar</option>
<option <?php echo ($row['author'] == 'Ignatius')?'selected="selected"':''; ?> value="Ignatius">Ignatius</option>
<option <?php echo ($row['author'] == 'Florencio')?'selected="selected"':''; ?> value="Florencio">Florencio</option>
<option <?php echo ($row['author'] == 'David')?'selected="selected"':''; ?> value="David">David</option>
<option <?php echo ($row['author'] == 'Gregory')?'selected="selected"':''; ?> value="Gregory">Gregory</option>
<option <?php echo ($row['author'] == 'Theo')?'selected="selected"':''; ?> value="Theo">Theo</option>
<option <?php echo ($row['author'] == 'Rowan')?'selected="selected"':''; ?> value="Rowan">Rowan</option>
<option <?php echo ($row['author'] == 'Justen')?'selected="selected"':''; ?> value="Justen">Justen</option>
</select>
</div>
<div class="form-group">
<button type="submit" name="submit">Submit</button>
<a href="index.php" class="back-link" style="float:right"><< Back</a>
</div>
</form>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
</body>
</html>
 

Delete records (index.php):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
<?php
if(isset($_GET['action'],$_GET['id']) && $_GET['action'] == 'delete')
{
$id = intval(trim($_GET['id']));
$sql = 'delete from blog where id = '.$id;
$deleteRs = mysqli_query($con,$sql);
if(mysqli_affected_rows($con) == 0)
{
$_SESSION['error_msg'] = 'Unable to delete record';
header('location:index.php');
exit();
}
else
{
$_SESSION['success_msg'] = 'Record has been deleted successfully';
header('location:index.php');
exit();
}
}
?>
 

 

More from WDB24.com

Posted in PHP

Published by Ahsan Zameer

I am a web developer and love to search new stuff on web. Trying different approaches and ideas to make web developing more interesting and enjoyable.

Leave a Reply

Your email address will not be published. Required fields are marked *