Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • CRUD operations in PHP with MySQLi Object Oriented Interface

    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 3.03k
    Comment on it

    My previous article CRUD operations with MySQLi procedural functions is demonstrating about performing CRUD operations on MySQL database. As we know that one of the best feature of MySQLi is that it also provides an object oriented interface, which is the most preferred approach these days for application development. So this article provides an Object Oriented version of the previous article to demonstrate how to use MySQLi object oriented interface for performing CRUD operations.

     

    1. Create Connection

    <?php
    // Create connection
    $con = @new mysqli($host_name, $username, $password, $database);
    
    //check for connection errors
    if ($con->connect_errno) {
        die('Connect Error: ' . $con->connect_error);
    }
    ?>

     

    2. Create Table

    Below is the code to create a table users.
     

    <?php
    //-----sql statement-------
    $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 (!$con->query($query)) {
        echo "Error in table creation : " . $con->error;
    } else {
         echo "Table created successfully";
    }
    
    ?>
    Output : Table created successfully

     

    3. INSERT Operation

    Insert one row into users table.

     

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

     

    4. SELECT Operation

    Select all records from the users table.

     

    <?php
    //------SQL statement-----------
    $query = "SELECT * FROM users";
    
    //--------execute query------------
    $result = $con->query($query);
    
    if ($result->num_rows > 0) {
    
        //print data of each row
        while($row = $result->fetch_assoc()) {
            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: Amit
    last_name: Verma
    email: amit.verma21@gmail.com
    city: Bhopal

     

    5. UPDATE Operation

    Update the city column for record with id = 1.

     

    <?php
    //---------SQL statement-------------
    $query = "UPDATE users SET city='Pune' WHERE id=1";
    
    //-----execute query------
    if (!$con->query($query)) {
        echo "Error : ". $con->error;
    } else {
        echo "Record updated successfully";
    }
    
    echo "<br>";
    
    //-------fetch records from table---------
    $query2 = "SELECT city FROM users";
    
    $result = $con->query($query2);
    
    if ($result->num_rows > 0) {
      //print data of each row
      while($row = $result->fetch_assoc()) {
        echo "city : ". $row["city"];
      }
    } else {
    echo "No record exists";
    }
    
    ?>

    Output :

    Record updated successfully
    city : Pune

     

    6. DELETE Operation

    Delete record from table whose id  = 1.

     

    <?php
    //---------SQL statement-------------
    $query = "DELETE FROM users WHERE id=1";
    
    //-----execute query------
    if (!$con->query($query)) {
        echo "Error : ". $con->error;
    } else {
        echo "Record deleted successfully";
    }
    
    echo "<br><br/>";
    
    //-------fetch records from table---------
    $query2 = "SELECT * FROM users";
    
    $result = $con->query($query2);
    
    if ($result->num_rows > 0) {
      //print data of each row
      while($row = $result->fetch_assoc()) {
        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: