Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • CRUD operations with MySQLi procedural functions

    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 2.86k
    Comment on it

    In previous posts we have learned about creating a connection to a MySQL database with procedural and object oriented way, we also learn how to run a query with mysqli. This article demonstrates how to perform different CRUD operations with MySQLi procedural functions.

    CRUD

    CRUD represents the four basic operations of the database.

    C : C stands for Create (INSERT), which defines creating or inserting new data into the database.

    R : R stands for Read (SELECT), which defines reading or retrieving records from database.

    U : U stands for Update (UPDATE), update selected records of database.

    D : D stands for Delete (DELETE), delete or remove the selected records from database.

     

    For performing above operations first we have to create a connection to MySQL database and have to create a table to perform the above operations on it.

     

    Create Connection

    <?php
    //create connection
    $con = @mysqli_connect($host_name, $username, $password, $database);
    
    // check connection errors 
    if (!$con) {
      die('Connect Error: ' . mysqli_connect_errno());
    }
    ?>

     

    Create Table

    Below is the code to create a table users.

    <?php
    $query = "CREATE TABLE users
    (
      id int AUTO_INCREMENT PRIMARY KEY,
      first_name varchar(80),
      last_name varchar(80),
      email varchar(150),
      city varchar(50)
    )";
    
    //------execute query--------------
    if (mysqli_query($con, $query)) {
    echo "Table created successfully";
    } else {
    echo "Error : ".mysqli_error($con);
    }
    ?> 
    
    Output : Table created successfully

     

    INSERT

    INSERT statement is used for inserting new records into a MySQL table.

     

    Syntax : INSERT INTO `table_name` (Field1, Field2, Field3,.....) VALUES (Value1, Value2, Value3,.....)

     

    Example : Let insert one row into users table.

     

    <?php
    //--------Insert statement---------
    $query = "INSERT INTO users (id, first_name, last_name, email, city) VALUES (1, 'Rohit', 'Kumar', 'rohit.kumar25@gmail.com', 'New Delhi')";
    
    if (mysqli_query($con, $query)) {
        echo "New record added successfully";
    } else {
        echo "Error: ". mysqli_error($con);
    }
    ?>
    Output : New record added successfully

     

    SELECT

    The SELECT statement is used for retrieving records from MySQL table.

     

    Syntax : SELECT column_name(one or more) FROM table_name

     

    Note :  To fetch all records use * instead of column names.

    Example : Retrieve all records from the users table.

     

    <?php
    $query = "SELECT * FROM users";
    $result = mysqli_query($con, $query);
    
    if (mysqli_num_rows($result) > 0) {
        //print data of each row
        while($row = mysqli_fetch_assoc($result)) {
            echo "id: " . $row["id"]."<br>";
            echo "first_name: " . $row["first_name"]."<br>";
            echo "last_name: " . $row["last_name"]."<br>";
            echo "email: " . $row["email"]."<br>";
            echo "city: " . $row["city"];
        }
    } else {
        echo "No record exists";
    }
    ?>

    Output :

    id: 1
    first_name: Rohit
    last_name: Kumar
    email: rohit.kumar25@gmail.com
    city: New Delhi

     

    UPDATE

    UPDATE statement is used to update the existing record of the table.

     

    Syntax : UPDATE table_name SET column1=value1, column2=value2,... WHERE condition_column = condition_value

     

    Example :

     

    <?php
    $query = "UPDATE users SET city='Mumbai' WHERE id=1";
    if (mysqli_query($con, $query)) {
        echo "Record updated successfully";
    } else {
        echo "Error: " . mysqli_error($con);
    }
    echo "<br>";
    $query = "SELECT city FROM users WHERE id = 1";
    $result = mysqli_query($con, $query);
    
    if (mysqli_num_rows($result) > 0) {
        //print data of each row
        while($row = mysqli_fetch_assoc($result)) {
            echo "city: " . $row["city"];
        }
    } else {
        echo "No record exists";
    }
    ?>

    Output :

    Record updated successfully

    city: Mumbai

     

    DELETE

    DELETE statement is used for deleting records from a table.

     

    Syntax : DELETE FROM table_name WHERE column_name = value

     

    Example : Delete user record with id = 1.

     

    <?php
    $query = "DELETE FROM users WHERE id=1";
    if (mysqli_query($con, $query)) {
        echo "Record deleted successfully";
    } else {
        echo "Error : " . mysqli_error($con);
    }
    echo "<br/><br/>";
    $query = "SELECT * FROM users";
    $result = mysqli_query($con, $query);
    
    if (mysqli_num_rows($result) > 0) {
        //print data of each row
        while($row = mysqli_fetch_assoc($result)) {
            echo "city: " . $row["city"];
        }
    } else {
        echo "No record exists";
    }
    ?>

    Output :

    Record deleted successfully

    No record exists

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: