PHP MySQL crud operation with code

In this tutorial we are going to present you crud (create, read, update and delete) operations. Here is the easiest PHP crud operation. You can modify some code against SQL injection and vulnerability. Here is a sample code to understand the PHP concept easily.

We will be using PHP MySQL database to store data. We did all the work step by step In the early tutorial, we demonstrated PHP MySQL insert data into MySQL database.

Now we want to execute the full PHP MySQL crud application.

So guys first we execute the insert query, which means we will insert the data in PHP MySQL database then we will execute the display query to display all the inserted data. Then after we execute the update query, in this query we will modify the data to be displayed. Finally, we will execute the deleted query to remove the number of rows from the database based on ID.

So guys, let’s start creating PHP MySQL crud operation with a live example.

PHP MySQL how to do crud operations.

In these tutorials, we will create the following files and folders

  • Db_connection:(folder) Creating folder and placing database file config.php inside db_connection.
  • Config: database connection file.
  • Index: Landing page for inserting data.
  • Insert: PHP logic for inserting data into the database.
  • Edit: To edit existing records based on id.
  • Delete: Deleting records from the database on the basis of ID.

Now we will do all the operations step by step.

Step: 1 database

First, we will need a database to put in the data. For that, produce a database in PHP MySQL.

If you use xampp then follow the same task.

After creating the database, create a table and execute the following query.

The code of the PHP MySQL table is given below

CREATE TABLE `crud_operation` (
  `id` int(10) NOT NULL,
  `fname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `lname` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now the PHP MySQL database task is complete.

Step: 2 database connection file

Now we connect the database table with PHP code.

So guys it is not hard, it is easy to connect PHP MySQL database with PHP.

We create a separate connection file, so we can easily modify it if we need any changes in the future.

Create a new config named file inside htdocs.

The code of config.php is given below.

Config.php

<?php
$server_name='localhost';
$db_user='database username';//xampp default root
$db_password='passsword';
$db_name='database name';//PHP MySQL database name

$conn=mysqli_connect($server_name,$db_user,$db_password,$db_name);
if(!$conn){
    die(mysqli_error());
}
?>

In the above code, we have used a simple PHP MySQL connection file to connect to the database. It’s not good practice, just understand using simple code.

Step: 3 user input

Now, we are creating an INSERT query to insert the data into the MySQL database.

First, we will create a user input HTML form using HTML code.

Creating a new file named index.php which will be our landing page.

The code of index.php is given below.

Index.php

<html>
    <head>
        <title>crud operation Json data show in android RecyclerView</title>
    </head>
    <body>
        <form action="insert.php" method="POST">
            First Name:-<input type="text" name="fnamet" placeholder="First Name"/><br >
            Last Name:-<input type="text" name="lnamet" placeholder="Last Name"/><br >
            <input type="submit" name="insert"/><br >
        </form>
        <a href="select.php">Display Record</a>
    </body>
</html>

In the above example, we have used a simple HTML form to handle the insert query. We do not use any validation. If you advance in PHP then do some validation for security.

Step: 4 insert data into PHP MySQL

Now we are creating a PHP MySQL script to store the data in the database.

Creating a new file named insert.php.

When clicking on submit button, execute the insert query and insert data into the database.

The code of insert.php code is given below.

Insert.php

<?php
require("db_connection/config.php");
if(isset($_POST['insert'])){
    $fname=$_POST['fnamet'];
    $lname=$_POST['lnamet'];
    
    $query="INSERT INTO crud_operation(fname,lname)VALUES('$fname','$lname')";
    $result=mysqli_query($conn,$query);
}
header("location:index.php");
mysqli_close($conn);
?>

PHP MySQL crud operation-insert data into PHP MySQL database

Step: 3 display data

After inserting the data, now we want to display all the rows and columns.

Create a new file named select.php as we will retrieve all the data from the database and display it.

The code of select.php is given below.

Select.php

<?php
require("db_connection/config.php");
$query="SELECT *FROM crud_operation";
$result=mysqli_query($conn,$query);

echo "<table border='1' cellspacing='0' cellpadding='10'>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Edit</th>
<th>Delete</th></tr>";

while($row=mysqli_fetch_array($result)){
    echo"<tr>";
    echo"<td>".$row['id']."</td>";
    echo"<td>".$row['fname']."</td>";
    echo"<td>".$row['lname']."</td>";
    echo"<td><a href='edit.php?id=$row[id]'>Edit</a></td>";
    echo"<td><a href='delete.php?id=$row[id]'>Delete</a></td>";
    echo"</tr>";
}
echo"</table><p>";
echo"<a href='index.php'>New Record Insert</a>";
?>

In the above code, we put two links, one for edit data and the other for delete data.

When clicking on the edit link, redirect to the edit.php file and we can edit data. If you click on the delete link, the data will be deleted from the database.

PHP MySQL crud operation-select data from PHP MySQL database

Step: 4 edit

Now we are going to create a new PHP file to update the data.

If your data is wrongly inserted in the database then don’t worry because will retrieve the data from here and update it by update.

In this operation, we will execute an update query. Before triggering the update query, we need to get the data from the database.

We are executing the following query to select the data to update.

Fetch data for update

<?php
$id=$_GET['id'];
$query="SELECT *FROM crud_operation where id='$id'";
$result=mysqli_query($conn,$query);

while($row=mysqli_fetch_array($result)){
    $fname=$row['fname'];
    $lname=$row['lname'];
}
?>

Now we execute an update query for updating data.

<?php
require("db_connection/config.php");
if(isset($_POST['update'])){
    $id=$_POST['id'];
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    
    $query="UPDATE crud_operation SET fname='$fname',lname='$lname' where id='$id'";
    $result=mysqli_query($conn,$query)or die("could not connect");
    header("location:select.php");
}
?>

When clicking the edit button, specifically fetching the column and set it based on the id in the textbox.

So we will create HTML textbox and update the data.

<html>
    <header>
        <title>Update data</title>
    </header>
    <form action="" method="POST">
        First Name:-<input type="text" name="fname" placeholder="upldate first name" value="<?php echo $fname;?>"required/><br>
        Last Name:-<input type="text" name="lname" placeholder="update last name" value="<?php echo $lname;?>"required/><br >
        <input type="hidden" name="id" value="<?php echo $_GET['id'];?>"/><br >
        <input type="submit" name="update"/>
    </form>
</html>

The full code of edit.php is given below.

Edit.php

<?php
require("db_connection/config.php");
if(isset($_POST['update'])){
    $id=$_POST['id'];
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    
    $query="UPDATE crud_operation SET fname='$fname',lname='$lname' where id='$id'";
    $result=mysqli_query($conn,$query)or die("could not connect");
    header("location:select.php");
}
?>

<?php
$id=$_GET['id'];
$query="SELECT *FROM crud_operation where id='$id'";
$result=mysqli_query($conn,$query);

while($row=mysqli_fetch_array($result)){
    $fname=$row['fname'];
    $lname=$row['lname'];
}
?>

<html>
    <header>
        <title>Update data</title>
    </header>
    <form action="" method="POST">
        First Name:-<input type="text" name="fname" placeholder="upldate first name" value="<?php echo $fname;?>"required/><br>
        Last Name:-<input type="text" name="lname" placeholder="update last name" value="<?php echo $lname;?>"required/><br >
        <input type="hidden" name="id" value="<?php echo $_GET['id'];?>"/><br >
        <input type="submit" name="update"/>
    </form>
</html>

Step: 5 delete data

We executed the INSERT, UPDATE, SELECT queries. Eventually, create a delete named file to delete the number of columns based on the id attribute.

The following query we’ll execute inside delete.php

Delete.php.

<?php
require("db_connection/config.php");
$id=$_GET['id'];
$query="DELETE FROM crud_operation WHERE id='$id' ";
$result=mysqli_query($conn,$query);
header("location:select.php");
?>

Our PHP crud operation is complete.

Now we will connect it in android recyclerview. Mean JSON parsing and showing data in android recyclerview.

We recommend you to add some validation for security purposes, as here only crud operation is given for practice purposes and understanding only.

Note: In this post, if you find any mistakes or bugs and errors, feel free to contact or give feedback to improve the quality. Great if you give your feedback about this tutorial and other tutorials.

Leave a Reply