Performing basic database operations (DML) in PHP using the mysqli procedural method involves writing SQL queries and executing them using the mysqli_query function. Here’s a guide to performing Insert, Delete, Update, and Select operations:
Step 1: Set Up the Database and Table
Assume we have a database named my_database and a table named users with the following structure:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE,
age INT NOT NULL
);
Step 2: Database Connection
First, create a PHP script to establish a connection to the database.
<?php
$servername = “localhost”;
$username = “my_user”;
$password = “my_password”;
$database = “my_database”;
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
?>
Step 3: Insert Data
To insert data into the users table:
<?php
// Insert data
$sql = “INSERT INTO users (name, email, age) VALUES (‘John Doe’, ‘john.doe@example.com’, 30)”;
if (mysqli_query($conn, $sql)) {
echo “New record created successfully”;
} else {
echo “Error: ” . $sql . “<br>” . mysqli_error($conn);
}
?>
Step 4: Select Data
To retrieve data from the users table:
<?php
// Select data
$sql = “SELECT id, name, email, age FROM users”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo “id: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Email: ” . $row[“email”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results”;
}
?>
Step 5: Update Data
To update data in the users table:
<?php
// Update data
$sql = “UPDATE users SET age = 31 WHERE email = ‘john.doe@example.com'”;
if (mysqli_query($conn, $sql)) {
echo “Record updated successfully”;
} else {
echo “Error updating record: ” . mysqli_error($conn);
}
?>
Step 6: Delete Data
To delete data from the users table:
<?php
// Delete data
$sql = “DELETE FROM users WHERE email = ‘john.doe@example.com'”;
if (mysqli_query($conn, $sql)) {
echo “Record deleted successfully”;
} else {
echo “Error deleting record: ” . mysqli_error($conn);
}
?>
Complete Example Script
Here’s a complete PHP script performing all the basic database operations:
php
Copy code
<?php
$servername = “localhost”;
$username = “my_user”;
$password = “my_password”;
$database = “my_database”;
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
// Insert data
$insert_sql = “INSERT INTO users (name, email, age) VALUES (‘John Doe’, ‘john.doe@example.com’, 30)”;
if (mysqli_query($conn, $insert_sql)) {
echo “New record created successfully<br>”;
} else {
echo “Error: ” . $insert_sql . “<br>” . mysqli_error($conn) . “<br>”;
}
// Select data
$select_sql = “SELECT id, name, email, age FROM users”;
$result = mysqli_query($conn, $select_sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo “id: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Email: ” . $row[“email”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results<br>”;
}
// Update data
$update_sql = “UPDATE users SET age = 31 WHERE email = ‘john.doe@example.com'”;
if (mysqli_query($conn, $update_sql)) {
echo “Record updated successfully<br>”;
} else {
echo “Error updating record: ” . mysqli_error($conn) . “<br>”;
}
// Delete data
$delete_sql = “DELETE FROM users WHERE email = ‘john.doe@example.com'”;
if (mysqli_query($conn, $delete_sql)) {
echo “Record deleted successfully<br>”;
} else {
echo “Error deleting record: ” . mysqli_error($conn) . “<br>”;
}
// Close the connection
mysqli_close($conn);
?>
This script demonstrates how to perform basic Insert, Select, Update, and Delete operations on a MySQL database using the mysqli procedural method in PHP.