Contact me See Resume

CRUD operations using PHP and mySQLi - nareshonline.com

CRUD operations using PHP and mySQLi

Catagory : PHP and mySQLi Posted : November, 10, 2018

CRUD operations using PHP and mySQLi

In this tutorial we discuss about CRUD operations using PHP and mySQLi by procedural way. CRUD stands for Create, Read, Update and Delete. We use CRUD operation to make communication between user and database (client and server) as per requirements.

CRUD operations using PHP and mySQLi

File Structure :

In this tutorial we will create two files as :

  1. index.php
  2. crud.php

Index.php

First of all we have to provide form with sufficient input fields. Where user can submit the data.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>CRUD Operation uisng Procedural PHP</title>
    <style>
        p {
            width: 160px;
            display: inline-block;
        }
    </style>
</head>
<body style="font-family:arial;">
   <h2>Add Students Details</h2>
    <form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method="post">
        <p>Student Name :</p><input type="text" name="name" /><br>
        <p>Roll Number :</p><input type="text" name="rollNum" /><br>
        <p>Email Address :</p><input type="text" name="email" /><br>
        <p></p><input type="submit" name="submit" value="Submit">
    </form>
</body>
</html>

Now Include crud.php in at the bottom in index.php

<?php include('crud.php');?>

crud.php

Now we will create our second file crud.php

In crud.php first of all we have to connect our database by storing the database connection into a local variable for ease of communicate.

// passing connection parameter in local variable
$host   = "localhost";  // server name
$user   = "root";       // user name
$paswd  = "";           // your password if don't have any password keep it blank;
$db     = "nol_blogs";  // database name

Making connection to database

$con = mysqli_connect($host, $user, $pawd, $db) or die ("Connection failed: " . mysqli_connect_error());

Insert Records in table

Now we will insert the data in database by checking submit button.

// collecting data from user submitted by user through form.
// Insert data in table named students
// checking for submit button submits

$error = "";    // variable for storing errors

if(isset($_POST['submit'])){
    // checking name field
    if(!empty($_POST['name'])){
        $name = $_POST['name'];
    }else{
        echo "please enter student name"."<br>";
        $error++;
    }
    // checking roll number field
    if(!empty($_POST['rollNum'])){
        $rollName  = $_POST['rollNum'];
    }else{
        echo "please enter student roll number"."<br>";
        $error++;
    }
    // checking email address
    if(!empty($_POST['email'])){
        $email  = $_POST['email'];
    }else{
        echo "please enter student roll number"."<br>";
        $error++;
    }
    
    // checking for any error occurred 
    if(!$error){
        $ins = "INSERT INTO students (name, rollNumber, email) VALUES ('$name', '$rollName', '$email')";
        $ins_Query = mysqli_query($con, $ins);
        echo "Data inserted successfully";
    }else{
        echo "Process Failed";
    }
} // end of submit button checking

Display records from database

We will display inserted data in table form.

<h2>Students Data</h2>
<table border="1">
    <tr>
        <td>Students Name</td>
        <td>Studets Roll Number</td>
        <td>Students Email</td>
        <td colspan="2">Edit / Delete</td>
        
    </tr>

<?php
$sel = "SELECT * from students";
$sel_Query = mysqli_query($con, $sel);
while($row = mysqli_fetch_array($sel_Query)){
    echo "
        <tr>
            <td>".$row['name']."</td><td>".$row['rollNumber']."</td>
            <td>".$row['email']."</td>
            <td><a href='index.php?update=".$row['id']."'>Update</a></td>
            <td><a href='index.php?del=".$row['id']."'>Delete</a></td>
        </tr>
    ";
}
?>
</table>

Update Records

When we are updating the existing records we have provide a form, where user can make required changes and submit. For better user experience we can provide old data from database to user.

When user click on update button we will receive a global variable with url by $_GET() method. So we can easily understand which data is going to be update.

    if(isset($_GET['update'])){
    $edit = $_GET['update'];
    $update_sel = "SELECT * FROM students WHERE id = '$edit' ";
    $update_query = mysqli_query($con, $update_sel);
    $row = mysqli_fetch_array($update_query);
    ?>
    <h2>Update Students Details</h2>
    <form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method="post">
<!--       There are many ways to update records in table. I am using one of the simplest way to upadate records by input field type hidden. -->
        <!-- create a hidden input -->
        <input type="hidden" name="id" value="<?php echo $row['id'] ;?>" /><br>
        <p>Student Name :</p><input type="text" name="name" value="<?php echo $row['name'] ;?>" /><br>
        <p>Roll Number :</p><input type="text" name="rollNum" value="<?php echo $row['rollNumber'] ;?>" /><br>
        <p>Email Address :</p><input type="text" name="email" value="<?php echo $row['email'] ;?>" /><br>
        <p></p><input type="submit" name="update" value="Update Records">
    </form>
    <?php
}

After receiving the data which to be update, we check the authenticity of data which is going to update in database.

if(isset($_POST['update'])){
    $id = $_POST['id'];
    // checking name field
    if(!empty($_POST['name'])){
        $name = $_POST['name'];
    }else{
        echo "please enter student name"."<br>";
        $error1++;
    }
    // checking roll number field
    if(!empty($_POST['rollNum'])){
        $rollName  = $_POST['rollNum'];
    }else{
        echo "please enter student roll number"."<br>";
        $error1++;
    }
    // checking email address
    if(!empty($_POST['email'])){
        $email  = $_POST['email'];
    }else{
        echo "please enter student roll number"."<br>";
        $error1++;
    }
    
    // checking for any error occurred, if there are no error, so now we can update records in our table by running some mySQLi queries. 
    if(!$error1){
        $upd = "UPDATE students SET name = '$name', rollNumber = '$rollName', email = '$email' WHERE id = '$id'";
        $upd_Query = mysqli_query($con, $upd);
        header('location:index.php');
    }
}

Delete Data from Table

Deleting the particular data from database according to user by catching the particular ID of record.

if(isset($_GET['del'])){
    echo $del = $_GET['del'];
    $dlt = "DELETE FROM students WHERE id = '$del'";
    $del_query = mysqli_query($con, $dlt);
    header('location:index.php');
}

This is a simple tutorial to understanding CRUD operation of mySQLi. Where user can directly update or delete to any records, but it can be very dangerous for our database. So first we should check user permissions to do this task.

 

Leave a Reply

You have to agree to the comment policy.

  1. Pingback: Cialis prices