CRUD Operations in PHP MySQLi | Create, Read, Update and Delete | E-CODEC

 

CRUD Operations in PHP MySQLi

In this tutorial we will learn PHP CRUD Operations with PHP and MySQLi.

I'm happy to post here my insight and methods utilized in PHP and MySQL. As you all mindful that PHP and MySQL are the most broadly utilized open source for sites. the present great many sites and application are utilizing these free software's.

Actually I feel that this could be next level for front end developer by utilizing PHP and MySQL a HTML designer can make a site more unique. Allow us to talk about How to Fetch data from database using PHP MySQL, Its straight forward and exceptionally valuable for an essential site dynamic client dynamic enlistment. 

Each innovative can execute its essential construction to their site. Presently Just follow these basic advances and you will find that your first dynamic functional PHP registration form with database connection entry on every form fill up.

So here is the code and my work. I hope you are gonna love this and share whit your friends too. So let's start learning How to Fetch data from database using PHP MySQL from scratch. So let's start.

In this tutorial you will learn to Display data from MySQL Database using PHP coding, subscribe E-CODEC channel to watch more videos on website designing tutorials for beginners.

I hope you enjoyed this video tutorial. If you had any doubts, then please comment them below. And if you enjoyed this tutorial, then please hit the like button below and subscribe my channel. Thank you..

what is CRUPD Operations

CRUD is an acronym for insert, select, Delete and Update. CRUD operations are basic data manipulation for database.

We have already learned how to fetch data, insert, delete, login system and update operations in previous chapters. 

In this tutorial we will create a simple HTML form to perform all these operations on a database table at once place.

Video tutorial

Step 1:- Create a Database Table

To Execute the SQL query to create a table named tutorials (in my case) you can use another database name also, inside you MySQLi database. We will use this table for our future operations.

 CREATE TABLE `datainsert` (  
  `id` int(11) NOT NULL,  
  `name` varchar(150) NOT NULL,  
  `email` varchar(150) NOT NULL,  
  `mnumber` int(11) NOT NULL,  
  `address` varchar(200) NOT NULL,  
  `state` varchar(50) NOT NULL,  
  `pincode` varchar(10) NOT NULL,  
  `create_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  

Step 2:- Create a database connection file

After the creating a table, we need create a PHP script in order to connect to the our MySQLi Database server. So let's create a file named "connection.php" (in my case) you can take another also, put the following code given below.

After this connection file include our web pages using include function.

 <?php   
 //connection.php  
 $conn=mysqli_connect('localhost','root','','tutorials');  
 /*if ($conn) {  
      echo "Connection successfully";  
 }else{  
      echo "Something Error";  
 }  
 */  
 ?>  

Step 3:- Creating the other landing page

First we need to create a HTML form for perform CRUD Operations. HTML form we will use to insert data into database table. Second step we will need a HTML Table for our CRUD Operations that contains a data grid showing the records from the tutorials database table. It also has action for each record like delete, edit/update and delete it.

To insert data into database we need a form so create a file named "index.php" and copy and paste the code given below.

 <?php   
 include "connection.php";  
 $msg="";  
 $uname="";  
 $email="";  
 $mnumber="";  
 $address="";  
 $state="";  
 $pin="";  
 if (isset($_GET['id'])) {  
      $id=$_GET['id'];  
      $select=mysqli_query($conn,"select * from datainsert where id='$id'");  
      $data=mysqli_fetch_assoc($select);  
      $uname=$data['name'];  
      $email=$data['email'];  
      $mnumber=$data['mnumber'];  
      $address=$data['address'];  
      $state=$data['state'];  
      $pin=$data['pincode'];  
 }  
 if (isset($_POST['submit'])) {  
      //echo "<pre>";  
      //print_r($_POST);  
      $uname=$_POST['uname'];  
      $email=$_POST['email'];  
      $mnumber=$_POST['mnumber'];  
      $address=$_POST['address'];  
      $state=$_POST['state'];  
      $pin=$_POST['pin'];  
      if (isset($_GET['id'])) {  
           $update=mysqli_query($conn,"UPDATE `datainsert` SET `name`='$uname',`email`='$email',`mnumber`='$mnumber',`address`='$address',`state`='$state',`pincode`='$pin' WHERE id='$id'");  
           if ($update) {  
                header("location:data_show.php");  
                die();  
           }  
      }else{  
           $insert=mysqli_query($conn,"INSERT INTO `datainsert`(`name`, `email`, `mnumber`, `address`, `state`, `pincode`, `create_on`) VALUES ('$uname','$email','$mnumber','$address','$state','$pin', NOW())");  
           if ($insert) {  
                $msg="Data inserted successfully";  
           }else{  
                $msg="Something Error, Try after sometime !";  
           }  
      }  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
 <head>  
      <meta charset="utf-8">  
      <title>CRUDE Operation in PHP MySQLi</title>  
      <style type="text/css">  
           *{  
                padding: 0;  
                margin: 0;  
                box-sizing: border-box;  
                font-family: 'verdana', sans-serif;  
           }  
           body{  
                width: 100%;  
                height: 100vh;  
                display: flex;  
                justify-content: center;  
                align-items: center;  
                background-color: #5d6d7d;  
           }  
           .container{  
                max-width: 500px;  
                width: 100%;  
                background-color: #ffff;  
           }  
           .container form{  
                width: 100%;  
                padding: 30px;  
           }  
           .container form .data-insert{  
                width: 100%;  
                padding: 12px 10px;  
                outline: none;  
                border: 1px solid #111;  
                margin: 8px 0px  
           }  
           .container form .sub_btn{  
                width: 100%;  
                padding: 10px 30px;  
                background-color: red;  
                color: #ffff;  
                font-size: 1em;  
                outline: none;  
                border: 0;  
                cursor: pointer;  
           }  
           .container h1{  
                display: block;  
                text-align: center;  
                padding: 15px 0px;  
           }  
      </style>  
 </head>  
 <body>  
 <div class="container">  
      <h1>Data Insert</h1>  
      <form method="post" action="">  
           <input type="text" name="uname" placeholder="Enter your name" class="data-insert" value="<?php echo $uname; ?>">  
           <input type="email" name="email" placeholder="Enter your email" class="data-insert" value="<?php echo $email; ?>">  
           <input type="text" name="mnumber" placeholder="Mobile Number" class="data-insert" value="<?php echo $mnumber; ?>">  
           <input type="text" name="address" placeholder="Enter your address" class="data-insert" value="<?php echo $address; ?>">  
           <input type="text" name="state" placeholder="Enter your state" class="data-insert" value="<?php echo $state; ?>">  
           <input type="text" name="pin" placeholder="Pin Code" class="data-insert" value="<?php echo $pin; ?>">  
           <input type="submit" name="submit" class="sub_btn" value="Submit">  
           <br>  
           <span><?php echo $msg; ?></span>  
      </form>  
 </div>  
 </body>  
 </html>  

To view data from database create a file named "show_data.php" source code given below.

 <?php   
 include "connection.php";  
 if (isset($_GET['id'])) {  
      $id=$_GET['id'];  
      $delete=mysqli_query($conn,"delete from datainsert where id='$id'");  
      if ($delete) {  
           header("location:data_show.php");  
           die();  
      }  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
 <head>  
      <meta charset="utf-8">  
      <title>Show Data from database in Table</title>  
      <style type="text/css">  
           *{  
                padding: 0;  
                margin: 0;  
                box-sizing: border-box;  
                font-family: 'verdana', sans-serif;  
           }  
           body{  
                display: flex;  
                justify-content: center;  
                align-items: center;  
                width: 100%;  
                height: 100vh;  
                background-color: #5d6d7d;  
           }  
           table{  
                border-collapse: collapse;  
           }  
           table th{  
                background-color: red;  
                padding: 8px 10px;  
                color: #fff;  
           }  
           table td{  
                background-color: #f3f3f3;  
                padding: 8px 10px;  
                color: #111;  
           }  
           .opt{  
                background-color: orange;  
                color: #fff;  
                font-size: 1em;  
                padding: 5px;  
                text-decoration: none;  
           }  
      </style>  
 </head>  
 <body>  
 <div class="container">  
      <table border="1" cellpadding="0">  
           <tr>  
                <th>#</th>  
                <th>Name</th>  
                <th>Email</th>  
                <th>Mobile Number</th>  
                <th>Address</th>  
                <th>State</th>  
                <th>Pin Code</th>  
                <th>Operation</th>  
           </tr>  
           <?php   
                include "connection.php";  
                $select=mysqli_query($conn,"select * from datainsert");  
                $num=mysqli_num_rows($select);  
                if ($num>0) {  
                     while($result=mysqli_fetch_assoc($select)){  
                          echo "  
                               <tr>  
                                    <td>".$result['id']."</td>  
                                    <td>".$result['name']."</td>  
                                    <td>".$result['email']."</td>  
                                    <td>".$result['mnumber']."</td>  
                                    <td>".$result['address']."</td>  
                                    <td>".$result['state']."</td>  
                                    <td>".$result['pincode']."</td>  
                                    <td>  
                                         <a href='?id=".$result['id']."' class='opt'>Delete</a>  
                                         <a href='form.php?id=".$result['id']."' class='opt'>Edit/Update</a>  
                                    </td>  
                               </tr>  
                          ";  
                     }  
                }  
           ?>  
      </table>  
 </div>  
 </body>  
 </html>  

Once tutorials table insert some records the show data page look something like the image given below.

Here a Front end developer can keep in touch with some own code in PHP, Read the code and comprehend this essential design line by line you will think that its extremely simple.

Conclusion

By following bit by bit strategy gave over a fundamental login form can be made. So best of luck for your first PHP code.

You can also learn:-  How to insert data into MySQL Database Table Using PHP

                                        JavaScript Bulb On / OFF Project Using HTML and CSS