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

Friday, 6 July 2018

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

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"); 

?>

Friday, 6 July 2018

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

1. Create Another Table for Records

2. Update Dashboard Page

3. Create Insert Page

4. Create View Page

5. Create Edit/Update Page

6. Create Delete Page

0 comments:

Post a Comment

Total Pageviews

Achievement

Live Traffic

Subscribe To

Followers

About Me

I V RAMANA

Recent Comments

Categories

Popular Posts

Hot Topics

Video

News

Comments

Recent

Bottom Ad [Post Page]

Recent Posts

Mysql - How to reset the administrator password in ISPConfig 3

Socialize

Blog Archive

Search This Blog

Post Top Ad

Archive

Post Bottom Ad

Author Details

About Me

Tags

Full width home advertisement

Pages

Post Page Advertisement [Top]

Climb the mountains