CRUD operations in PHP with MySQLi Object Oriented Interface

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