Table of Contents
Tutorial: Easy and Simple Add, Edit, Delete MySQL Table Rows in PHP with Source Code
In this tutorial, I will show you how to create a simple add, edit and delete functions in MySQL Table Rows using PHP with MySQLi database. This tutorial does not include a good design but will give you an idea of the essential tasks in PHP and basic queries in MySQLi.
Creating a Database
First, we’re going to create a database that contains our data.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as “basic_command”.
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.
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 a Connection
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 a Table and Add Form
Next step is to 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 a 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');
?>Happy Coding!
Download Here
