CRUD operations with MySQLi procedural functions

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

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