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

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 :)

Author Details

Banner

Perekodan

Sistem Pendaftaran

Post Top Ad