Google Ads

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


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

In this tutorial, I will show you how to create a simple add, edit and delete functions using PHP, MySQLi. This tutorial does not include a good design but will give you an idea on 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.
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 our 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 our 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 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');
?>

 

 


Related Post

Save image from url directly on your server PHP Sc... Save image from url directly on your server PHP Script This code help you save image from url directly on your server. The first, we check broken ...
Performance Indicator System PHP Script Performance Indicator System PHP Script This system features a purchase reservation on the dealer side, and system management in both the adm...
Nextpost v4.2.0 (nulled) + Modules & Skins PH... Nextpost v4.2.0 (nulled) + Modules & Skins PHP Script Nextpost is an online marketing tool auto posting web application that allows you to auto...
Line Chart PHP Script Download Line Chart PHP Script Download Simplified line chart script uses points connected by lines to represent the data series in a graph. Features ...
B2Evolution v6.0.0-alpha Blog PHP Script B2Evolution v6.0.0-alpha Blog PHP Script Create a beautiful blog website using this php scrpt. b2evolution is an advanced weblog tool, i-e softw...

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *