Expert of PHP: Insert, View, Edit and Delete Record from Database Using PHP and MySQLi

In this tutorial i will explain how to insert, view, edit and delete record from database using PHP and Mysqli, basically this tutorial is a second part of Simple User Registration & Login Script in PHP and MySQLi, in this first part i explained how to create simple user registration and login using PHP and MySQLi, if you do not know how to create user registration and user login form so kindly first check this tutorial Simple User Registration & Login Script in PHP and MySQLi, after that now come back to this tutorial.

I am using PHP 5.6 for this tutorial, i didn’t check this tutorial on PHP 7, kindly make sure you are using PHP 5.6 to avoid any unexpected error.

Steps to Creating an Insert, View, Edit and Delete Record from Database Using PHP and MySQLi

Now i am assuming that you have already created user registration and login forms which i created in Simple User Registration & Login Script in PHP and MySQLi now i will create another table to keeping records in it, update dashboard.php file and add four more new pages which are insert.php, view.php, edit.php, and delete.php, follow the following steps:

Create Another Table for Records
Update Dashboard File
Create Insert Page
Create View Page
Create Edit/Update Page
Create Delete Page

1. Create Another Table for Records

Execute the below SQL query:

1

2

3

4

5

6

7

8

CREATE TABLE IF NOT EXISTS `new_record` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `trn_date` datetime NOT NULL,

 `name` varchar(50) NOT NULL,

 `age`int(11) NOT NULL,

 `submittedby` varchar(50) NOT NULL,

 PRIMARY KEY (`id`)

 );

2. Update Dashboard Page

Update dashboard.php file and paste the following code in it.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

<?php

require('db.php');

include("auth.php");

?>

<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8">

<title>Dashboard - Secured Page</title>

<link rel="stylesheet" href="css/style.css" />

</head>

<body>

<div class="form">

<p>Welcome to Dashboard.</p>

<p><a href="index.php">Home</a><p>

<p><a href="insert.php">Insert New Record</a></p>

<p><a href="view.php">View Records</a><p>

<p><a href="logout.php">Logout</a></p>

</div>

</body>

</html>

3. Create Insert Page

Create a page with name insert.php and paste the below code in it.

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

<?php

require('db.php');

include("auth.php");

$status = "";

if(isset($_POST['new']) && $_POST['new']==1){

    $trn_date = date("Y-m-d H:i:s");

    $name =$_REQUEST['name'];

    $age = $_REQUEST['age'];

    $submittedby = $_SESSION["username"];

    $ins_query="insert into new_record

    (`trn_date`,`name`,`age`,`submittedby`)values

    ('$trn_date','$name','$age','$submittedby')";

    mysqli_query($con,$ins_query)

    or die(mysql_error());

    $status = "New Record Inserted Successfully.

    </br></br><a href='view.php'>View Inserted Record</a>";

}

?>

<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8">

<title>Insert New Record</title>

<link rel="stylesheet" href="css/style.css" />

</head>

<body>

<div class="form">

<p><a href="dashboard.php">Dashboard</a> 

| <a href="view.php">View Records</a> 

| <a href="logout.php">Logout</a></p>

<div>

<h1>Insert New Record</h1>

<form name="form" method="post" action=""> 

<input type="hidden" name="new" value="1" />

<p><input type="text" name="name" placeholder="Enter Name" required /></p>

<p><input type="text" name="age" placeholder="Enter Age" required /></p>

<p><input name="submit" type="submit" value="Submit" /></p>

</form>

<p style="color:#FF0000;"><?php echo $status; ?></p>

</div>

</div>

</body>

</html>

4. Create View Page

Create a page with name view.php and paste the below code in it.

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

<?php

require('db.php');

include("auth.php");

?>

<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8">

<title>View Records</title>

<link rel="stylesheet" href="css/style.css" />

</head>

<body>

<div class="form">

<p><a href="index.php">Home</a> 

| <a href="insert.php">Insert New Record</a> 

| <a href="logout.php">Logout</a></p>

<h2>View Records</h2>

<table width="100%" border="1" style="border-collapse:collapse;">

<thead>

<tr>

<th><strong>S.No</strong></th>

<th><strong>Name</strong></th>

<th><strong>Age</strong></th>

<th><strong>Edit</strong></th>

<th><strong>Delete</strong></th>

</tr>

</thead>

<tbody>

<?php

$count=1;

$sel_query="Select * from new_record ORDER BY id desc;";

$result = mysqli_query($con,$sel_query);

while($row = mysqli_fetch_assoc($result)) { ?>

<tr><td align="center"><?php echo $count; ?></td>

<td align="center"><?php echo $row["name"]; ?></td>

<td align="center"><?php echo $row["age"]; ?></td>

<td align="center">

<a href="edit.php?id=<?php echo $row["id"]; ?>">Edit</a>

</td>

<td align="center">

<a href="delete.php?id=<?php echo $row["id"]; ?>">Delete</a>

</td>

</tr>

<?php $count++; } ?>

</tbody>

</table>

</div>

</body>

</html>

5. Create Edit/Update Page

Create a page with name edit.php and paste the below code in it.

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

<?php

require('db.php');

include("auth.php");

$id=$_REQUEST['id'];

$query = "SELECT * from new_record where id='".$id."'"; 

$result = mysqli_query($con, $query) or die ( mysqli_error());

$row = mysqli_fetch_assoc($result);

?>

<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8">

<title>Update Record</title>

<link rel="stylesheet" href="css/style.css" />

</head>

<body>

<div class="form">

<p><a href="dashboard.php">Dashboard</a> 

| <a href="insert.php">Insert New Record</a> 

| <a href="logout.php">Logout</a></p>

<h1>Update Record</h1>

<?php

$status = "";

if(isset($_POST['new']) && $_POST['new']==1)

{

$id=$_REQUEST['id'];

$trn_date = date("Y-m-d H:i:s");

$name =$_REQUEST['name'];

$age =$_REQUEST['age'];

$submittedby = $_SESSION["username"];

$update="update new_record set trn_date='".$trn_date."',

name='".$name."', age='".$age."',

submittedby='".$submittedby."' where id='".$id."'";

mysqli_query($con, $update) or die(mysqli_error());

$status = "Record Updated Successfully. </br></br>

<a href='view.php'>View Updated Record</a>";

echo '<p style="color:#FF0000;">'.$status.'</p>';

}else {

?>

<div>

<form name="form" method="post" action=""> 

<input type="hidden" name="new" value="1" />

<input name="id" type="hidden" value="<?php echo $row['id'];?>" />

<p><input type="text" name="name" placeholder="Enter Name" 

required value="<?php echo $row['name'];?>" /></p>

<p><input type="text" name="age" placeholder="Enter Age" 

required value="<?php echo $row['age'];?>" /></p>

<p><input name="submit" type="submit" value="Update" /></p>

</form>

<?php } ?>

</div>

</div>

</body>

</html>

6. Create Delete Page

Create a page with name delete.php and paste the below code in it.

1

2

3

4

5

6

7

<?php

require('db.php');

$id=$_REQUEST['id'];

$query = "DELETE FROM new_record WHERE id=$id"; 

$result = mysqli_query($con,$query) or die ( mysqli_error());

header("Location: view.php"); 

?>

Click here to Forgot Password Recovery (Reset) using PHP and MySQL

If you found this tutorial helpful so share it with your friends, developer groups and leave your comment.

Expert of PHP

Thursday, July 25, 2019

Insert, View, Edit and Delete Record from Database Using PHP and MySQLi