In this tutorial, I’ll demonstrate how to use PHP and MySQLi to develop straightforward add, edit, and delete methods. Although this tutorial’s design is poor, it will provide you with a general grasp of PHP’s fundamental operations and MySQLi’s fundamental queries.
Creating our Database
First, create a database to hold your data.<br>Open phpMyAdmin.<br>Click Database, create a database, and name it “basic_command”.<br>After creating the database, click SQL and paste the following 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 acts as a bridge between the form and the database. To create the file, open your HTML code editor and paste the following code 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
The next step is to create a database connection and save it as ‘conn.php’. This file acts as a bridge between the form and the database. To create the file, open your HTML code editor and paste the following code 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 adds the data entered by the user on submission to the database. We’ll call this “add.php”. To create the script, open your HTML code editor and paste the following code 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 allows the user to edit the selected row. Call this form “edit.php”. To create the form, open your HTML code editor and paste the following code 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 updates the database based on user input and submissions. We’ll call this form “update.php”. To create the script, open your HTML code editor and paste the following code 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
Finally, create a delete script and call it “delete.php”. This script deletes the rows selected by the user. To create the script, open your HTML code editor and paste the following code after the tag.
<?php
$id=$_GET['id'];
include('conn.php');
mysqli_query($conn,"delete from `user` where userid='$id'");
header('location:index.php');
?>
