When I have googled to find a solution for CURD Operation all the time it showing they have required jQuery ajax. So I decided to write a simple code. This will be the most simple form of PHP MySQL Create Read Update and Delete option in the PHP Language
So I am not boring you Let’s go to the coding 🙂
First, Let’s set up the table in MySQL
CREATE TABLE `tblemp` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblemp` -- INSERT INTO `tblemp` (`id`, `name`, `age`) VALUES (1, 'Anvar Freelancer', '27');
I know you guys can do this with the help of PHPMyAdmin
In the Second Stage, we will prepare the Home Page with a table
<!DOCTYPE html> <html> <head> <title>Simple CRUD Operation with PHP MySQL</title> </head> <body> <h2>Employee Details</h2> <table border="2"> <tr> <td>Sr.No.</td> <td>Full Name</td> <td>Age</td> <td>Edit</td> <td>Delete</td> </tr> <tr> <td>1</td> <td>Anvar Freelancer</td> <td>27</td> <td><a href="index.php?id=1">Edit</a></td> <td><a href="index.php?del=1">Delete</a></td> </tr> </table> </body> </html>
This is the simplest form for showing the contents on the Homepage. Technically Read Operation will work from here.
Let’s connect to our database and table from a single line of code
Here it is
<?php
$mysqli = new mysqli("localhost","Username","Password","database");
Done now let’s check the PHP MySQL connectivity has any issues
<?php
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
Oops! If you face any issues in the connectivity please comment below I will fix it for you.
R: Read from MySQL database
<?php
$sql = "SELECT * from tblemp";
$result = $mysqli-> query($sql); // fetch data
while($data = $result->fetch_array(MYSQLI_ASSOC)){
?>
<tr>
<td><?php echo $data['id']; ?></td>
<td><?php echo $data['name']; ?></td>
<td><?php echo $data['age']; ?></td>
<td><a href="index.php?id=<?php echo $data['id']; ?>">Edit</a></td>
<td><a href="index.php?del=<?php echo $data['id']; ?>">Delete</a></td>
</tr>
<?php
}
C: Create with PHP
Here is the create form and you can place it below the table
<form method="POST" action="index.php"> <input type="text" name="name" value="<?php echo $data['name'] ?>" placeholder="Enter Full Name" Required> <input type="text" name="age" value="<?php echo $data['age'] ?>" placeholder="Enter Age" Required> <input type="submit" name="add" value="Add"> </form>
Once the submit happened
<?php
if(isset($_POST['add'])){ // when click on Add button
$name = $_POST['name'];
$age = $_POST['age'];
$sql ="insert into tblemp set name='$name', age='$age'";
$result = $mysqli -> query($sql);
if($result){
$mysqli -> close(); // Close connection
header("location:index.php"); // redirects to Main page
exit;
}else{
$mysqli->error;
}
}
U: Update the table with PHP MySQL
In the update function, we need to get the ID of the row in which you are going to update. So we can pass this through $_GET[‘id’] Method.
So here it is, First we will call the id and we will fetch the corresponding row from the database.
<?php
if(isset($_GET['id'])){
$id = $_GET['id'];
$sql = "select * from tblemp where id='$id'";
$result = $mysqli-> query($mysqli,$sql); // select query
$data = $result->fetch_array($result); // fetch data
?>
<form method="POST" action="index.php">
<input type="text" name="name" value="<?php echo $data['name'] ?>" placeholder="Enter Full Name" Required>
<input type="text" name="age" value="<?php echo $data['age'] ?>" placeholder="Enter Age" Required>
<input type="hidden" name="id" value="<?php echo $data['id'] ?>">
<input type="submit" name="update" value="Update">
</form>
Now create the function that will happen once the update button is click
<?php
if(isset($_POST['update'])){ // when click on Update Link
$id = $_POST['id'];
$name = $_POST['name'];
$age = $_POST['age'];
$sql = "update tblemp set name='$name', age='$age' where id='$id'";
$result = mysqli_query($mysqli,$sql);
if($result){
$mysqli -> close(); // Close connection
header("location:index.php"); // redirects to Main page
exit;
}else{
$mysqli->error;
}
}
D: Delete from the table
This delete I have set for the demonstration only you have to set value 1 or 2 in the real programming.
Because once we use this code the data will be deleted from our table and it will be not recoverable.
If you set 1 or 0 methods the data will be in the database and it’s a good programming practice.
Here also will get the corresponding delete row id from the GET Method and We will delete it my MySQL Query.
<?php
if(isset($_GET['del'])){ // when click on Delete Link
$id = $_GET['del'];
$sql = "DELETE FROM `tblemp` WHERE `tblemp`.`id`='$id'";
$result = mysqli_query($mysqli,$sql);
if($result){
$mysqli -> close(); // Close connection
header("location:index.php"); // redirects to Main page
exit;
}else{
$mysqli->error;
}
}
In the last, I will give you a bonus lesson that will clear the table
In this, we will clear the database and it will be perfect 😛 and Good System of CRUD of PHP and MYSQL
<a href="index.php?clear">Clear the Table</a>
<?php
if(isset($_GET['clear'])){ // when click on Clear Link
$sql = "TRUNCATE `mt`.`tblemp`";
$result = mysqli_query($mysqli,$sql);
if($result)
{
$mysqli -> close(); // Close connection
header("location:index.php"); // redirects to Main page
exit;
}else{
$mysqli->error;
}
}
I hope this will be helpful to learn CRUD Operation in PHP MySQL
Are you looking for the full source code of PHP MySQL CRUD Operation?
I can give you but you will not practice it properly. So here I am giving a detailed explanation of the above code
<?php // MySQL connection // MySQL connection Validation // PHP Add function // PHP Update Function // PHP Delete Function // HTML Start form here // Read Operaion // Edit & Delete Option // Clear database
