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
- // Check connection
- {
- }
- ?>
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');
- ?>
- <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'];
- ?>
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'];
- ?>
- <!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'");
- ?>
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');
- ?>
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.
Comments
Nice post! This is really
Nice post! This is really helpful to used my business site development. I absolutely like this post. It just made my work easier.
Thanks.
Please don't write code, it…
Please don't write code, it could end bad for everyone :)
Hello i'm under the water, can you help me?
Add new comment
- Add new comment
- 71257 views