Skip to main content

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

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

  1. First, we're going to create a database that contains our data.
  2. Open phpMyAdmin.
  3. Click databases, create a database, and name it as "basic_command".
  4. After creating a database, click the SQL and paste the below code. See the image below for detailed instructions.
  1. CREATE TABLE `user` (
  2. `userid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `firstname` VARCHAR(30) NOT NULL,
  4. `lastname` VARCHAR(30) NOT NULL,
  5. PRIMARY KEY (`userid`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
add

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.
  1. <?php
  2. $conn = mysqli_connect("localhost","root","","basic_command");
  3.  
  4. // Check connection
  5. {
  6. echo "Failed to connect to MySQL: " . mysqli_connect_error();
  7. }
  8. ?>

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.
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>Basic MySQLi Commands</title>
  5. </head>
  6. <body>
  7. <div>
  8. <form method="POST" action="add.php">
  9. <label>Firstname:</label><input type="text" name="firstname">
  10. <label>Lastname:</label><input type="text" name="lastname">
  11. <input type="submit" name="add">
  12. </form>
  13. </div>
  14. <br>
  15. <div>
  16. <table border="1">
  17. <thead>
  18. <th>Firstname</th>
  19. <th>Lastname</th>
  20. <th></th>
  21. </thead>
  22. <tbody>
  23. <?php
  24. include('conn.php');
  25. $query=mysqli_query($conn,"select * from `user`");
  26. while($row=mysqli_fetch_array($query)){
  27. ?>
  28. <tr>
  29. <td><?php echo $row['firstname']; ?></td>
  30. <td><?php echo $row['lastname']; ?></td>
  31. <td>
  32. <a href="edit.php?id=<?php echo $row['userid']; ?>">Edit</a>
  33. <a href="delete.php?id=<?php echo $row['userid']; ?>">Delete</a>
  34. </td>
  35. </tr>
  36. <?php
  37. }
  38. ?>
  39. </tbody>
  40. </table>
  41. </div>
  42. </body>
  43. </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.
  1. <?php
  2. include('conn.php');
  3.  
  4. $firstname=$_POST['firstname'];
  5. $lastname=$_POST['lastname'];
  6.  
  7. mysqli_query($conn,"insert into `user` (firstname,lastname) values ('$firstname','$lastname')");
  8. header('location:index.php');
  9.  
  10. ?>

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.
  1. <?php
  2. include('conn.php');
  3. $id=$_GET['id'];
  4. $query=mysqli_query($conn,"select * from `user` where userid='$id'");
  5. $row=mysqli_fetch_array($query);
  6. ?>
  7. <!DOCTYPE html>
  8. <html>
  9. <head>
  10. <title>Basic MySQLi Commands</title>
  11. </head>
  12. <body>
  13. <h2>Edit</h2>
  14. <form method="POST" action="update.php?id=<?php echo $id; ?>">
  15. <label>Firstname:</label><input type="text" value="<?php echo $row['firstname']; ?>" name="firstname">
  16. <label>Lastname:</label><input type="text" value="<?php echo $row['lastname']; ?>" name="lastname">
  17. <input type="submit" name="submit">
  18. <a href="index.php">Back</a>
  19. </form>
  20. </body>
  21. </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.
  1. <?php
  2. include('conn.php');
  3. $id=$_GET['id'];
  4.  
  5. $firstname=$_POST['firstname'];
  6. $lastname=$_POST['lastname'];
  7.  
  8. mysqli_query($conn,"update `user` set firstname='$firstname', lastname='$lastname' where userid='$id'");
  9. header('location:index.php');
  10. ?>

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.
  1. <?php
  2. $id=$_GET['id'];
  3. include('conn.php');
  4. mysqli_query($conn,"delete from `user` where userid='$id'");
  5. header('location:index.php');
  6. ?>

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

Submitted bynoobstudent (not verified)on Tue, 12/19/2017 - 06:37

Every thing is good but there is a little thing that made the edit and delete links not appear. You forgot to make a td for both of them, other then that it´s perfect. Thanks!!
Submitted byPhp Developer (not verified)on Thu, 05/24/2018 - 18:36

Nice post! This is really helpful to used my business site development. I absolutely like this post. It just made my work easier. Thanks.

Add new comment