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