Easy and Simple Add, Edit, Delete MySQL Table Rows using PHP/MySQLi Tutorial

Submitted by nurhodelta_17 on Saturday, December 19, 2020 - 16:59.

Language

In this tutorial, I will show you how to create simple add, edit, and delete functions using PHP, MySQLi. This tutorial does not include a good design but will give you an idea of the basic functions in PHP and basic queries in MySQLi.

Creating our Database

First, we're going to create a database that contains our data.
Open phpMyAdmin.
Click databases, create a database, and name it as "basic_command".
After creating a database, click the SQL and paste the below code. See the image below for detailed instructions.

CREATE TABLE `user` (
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL,
  `lastname` VARCHAR(30) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Creating our Connection

The next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

<?php
$conn = mysqli_connect("localhost","root","","basic_command");
 
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

Creating our Table and Add Form

Then, we will create our table and "add form" and name it as "index.php". This table will show the data in our database and the add form will add rows to our database. To create the table and form, open your HTML code editor and paste the code below after the tag.

<!DOCTYPE html>
<html>
<head>
<title>Basic MySQLi Commands</title>
</head>
<body>
        <div>
                <form method="POST" action="add.php">
                        <label>Firstname:</label><input type="text" name="firstname">
                        <label>Lastname:</label><input type="text" name="lastname">
                        <input type="submit" name="add">
                </form>
        </div>
        <br>
        <div>
                <table border="1">
                        <thead>
                                <th>Firstname</th>
                                <th>Lastname</th>
                                <th></th>
                        </thead>
                        <tbody>
                                <?php
                                        include('conn.php');
                                        $query=mysqli_query($conn,"select * from `user`");
                                        while($row=mysqli_fetch_array($query)){
                                                ?>
                                                <tr>
                                                        <td><?php echo $row['firstname']; ?></td>
                                                        <td><?php echo $row['lastname']; ?></td>
                                                        <td>
                                                                <a href="edit.php?id=<?php echo $row['userid']; ?>">Edit</a>
                                                                <a href="delete.php?id=<?php echo $row['userid']; ?>">Delete</a>
                                                        </td>
                                                </tr>
                                                <?php
                                        }
                                ?>
                        </tbody>
                </table>
        </div>
</body>
</html>

Creating our Add Script

This script will add the data inputted by the user to our database upon submission and we name it as "add.php". To create the script, open your HTML code editor and paste the code below after the tag.

<?php
        include('conn.php');
        
        $firstname=$_POST['firstname'];
        $lastname=$_POST['lastname'];
                
        mysqli_query($conn,"insert into `user` (firstname,lastname) values ('$firstname','$lastname')");
        header('location:index.php');
        
?>

Creating our Edit Form

This form will enable the user to edit the selected row. We name this form as "edit.php". To create the form, open your HTML code editor and paste the code below after the tag.

<?php
        include('conn.php');
        $id=$_GET['id'];
        $query=mysqli_query($conn,"select * from `user` where userid='$id'");
        $row=mysqli_fetch_array($query);
?>
<!DOCTYPE html>
<html>
<head>
<title>Basic MySQLi Commands</title>
</head>
<body>
        <h2>Edit</h2>
        <form method="POST" action="update.php?id=<?php echo $id; ?>">
                <label>Firstname:</label><input type="text" value="<?php echo $row['firstname']; ?>" name="firstname">
                <label>Lastname:</label><input type="text" value="<?php echo $row['lastname']; ?>" name="lastname">
                <input type="submit" name="submit">
                <a href="index.php">Back</a>
        </form>
</body>
</html>

Creating our Edit Script

This script will update our database depending on user input and upon submission. We name this form as "update.php". To create the script, open your HTML code editor and paste the code below after the tag.

<?php
        include('conn.php');
        $id=$_GET['id'];
        
        $firstname=$_POST['firstname'];
        $lastname=$_POST['lastname'];
        
        mysqli_query($conn,"update `user` set firstname='$firstname', lastname='$lastname' where userid='$id'");
        header('location:index.php');
?>

Creating our Delete Script

Lastly, we create our delete script and name it as "delete.php". This script will delete the row selected by our user. To create the script, open your HTML code editor and paste the code below after the tag.

<?php
        $id=$_GET['id'];
        include('conn.php');
        mysqli_query($conn,"delete from `user` where userid='$id'");
        header('location:index.php');
?>

Demo

Happy Coding :)

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.