Introduction

Creating, reading, updating, and deleting (CRUD) operations are the backbone of any dynamic web application. PHP, being a versatile and widely-used server-side scripting language, provides extensive capabilities to perform these operations. This blog will guide you through the detailed process of making CRUD requests in PHP, covering functions, constants, classes, and the use of CLI commands. By the end, we will also develop a mini project to solidify our understanding.

Setting Up the Environment

Before we dive into the code, let's set up our environment. Ensure you have the following installed on your system:

• PHP (7.4 or later)

• MySQL or MariaDB

• Composer (for dependency management)

• A code editor (such as VSCode or PHPStorm)

Step-by-Step Setup

1. Install PHP: Download and install PHP from the official website.

2. Install MySQL: Download and install MySQL from the official website.

3. Install Composer: Download and install Composer from the official website.

PHP Basics

PHP Syntax

PHP scripts are executed on the server. The basic syntax of PHP includes:

<?php
// PHP code goes here
?>

PHP Constants

Constants are like variables except that once they are defined, they cannot be changed or undefined.

<?php
define("SITE_NAME", "My Awesome Site");
echo SITE_NAME; // Outputs: My Awesome Site
?>

PHP Functions

Functions are reusable pieces of code that perform a specific task.

<?php
function greet($name) {
    return "Hello, " . $name;
}
echo greet("World"); // Outputs: Hello, World
?>

PHP Classes

Classes are blueprints for objects, providing a structure for data and methods to manipulate that data.

<?php
class Person {
    private $name;

    public function __construct($name) {
        $this->name = $name;
    }

    public function getName() {
        return $this->name;
    }
}

$person = new Person("John Doe");
echo $person->getName(); // Outputs: John Doe
?>

Connecting to the Database

Using MySQLi

MySQLi provides a procedural and object-oriented interface to interact with MySQL databases.

Procedural MySQLi

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Object-Oriented MySQLi

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Using PDO

PDO (PHP Data Objects) provides a uniform interface for accessing different databases.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Creating CRUD Operations

Create Operation (INSERT)

The CREATE operation inserts new records into a database table.

MySQLi Procedural

<?php
$sql = "INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

MySQLi Object-Oriented

<?php
$sql = "INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

PDO

<?php
try {
    $sql = "INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
    $conn->exec($sql);
    echo "New record created successfully";
}
catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
?>

Read Operation (SELECT)

The READ operation fetches data from the database.

MySQLi Procedural

<?php
$sql = "SELECT id, firstname, lastname FROM Users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

MySQLi Object-Oriented

<?php
$sql = "SELECT id, firstname, lastname FROM Users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

PDO

<?php
$sql = "SELECT id, firstname, lastname FROM Users";
foreach ($conn->query($sql) as $row) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
?>

Update Operation (UPDATE)

The UPDATE operation modifies existing records in a database table.

MySQLi Procedural

<?php
$sql = "UPDATE Users SET lastname='Doe' WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}
?>

MySQLi Object-Oriented

<?php
$sql = "UPDATE Users SET lastname='Doe' WHERE id=1";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}
?>

PDO

<?php
$sql = "UPDATE Users SET lastname='Doe' WHERE id=1";

$stmt = $conn->prepare($sql);

if ($stmt->execute()) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $stmt->errorInfo();
}
?>

Delete Operation (DELETE)

The DELETE operation removes records from a database table.

MySQLi Procedural

<?php
$sql = "DELETE FROM Users WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}
?>

MySQLi Object-Oriented

<?php
$sql = "DELETE FROM Users WHERE id=1";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>

PDO

<?php
$sql = "DELETE FROM Users WHERE id=1";

$stmt = $conn->prepare($sql);

if ($stmt->execute()) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $stmt->errorInfo();
}
?>

Building a CLI Application

Introduction to PHP CLI

PHP CLI (Command Line Interface) allows you to run PHP scripts from the command line, making it easier to develop scripts for tasks like automation and maintenance.

Creating a CLI Command Structure

To create a CLI application, we need to define a structure to handle different commands.

<?php
if (php_sapi_name() !== 'cli') {
    die("This script can only be run from the command line.");
}

$command = $argv[1] ?? null;

switch ($command) {
    case 'create':
        // Handle create command
        break;
    case 'read':
        // Handle read command
        break;
    case 'update':
        // Handle update command
        break;
    case 'delete':
        // Handle delete command
        break;
    default:
        echo "Unknown command.\n";
        break;
}
?>

Mini Project: Simple Task Manager

Project Overview

We will create a simple task manager application that allows users to create, read, update, and delete tasks using PHP CLI.

Setting Up the Project

1. Create a Project Directory:

    mkdir task-manager
    cd task-manager
   

2. Create a Composer File:

    {
        "name": "task/manager",
        "require": {
            "php": ">=7.4"
        }
    }
   

   Run composer install to initialize the project.

Creating the Database and Tables

Create a database and a tasks table.

CREATE DATABASE task_manager;
USE task_manager;

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Implementing CRUD Operations

Database Connection

Create a file db.php for database connection.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "task_manager";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

CRUD Operations

Create a file tasks.php for task-related functions.

<?php
require 'db.php';

function createTask($title, $description) {
    global $conn;
    $sql = "INSERT INTO tasks (title, description) VALUES (:title, :description)";
    $stmt = $conn->prepare($sql);
    $stmt->execute(['title' => $title, 'description' => $description]);
    echo "Task created successfully.\n";
}

function readTasks() {
    global $conn;
    $sql = "SELECT * FROM tasks";
    foreach ($conn->query($sql) as $row) {
        echo "ID: " . $row['id'] . " | Title: " . $row['title'] . " | Description: " . $row['description'] . "\n";
    }
}

function updateTask($id, $title, $description) {
    global $conn;
    $sql = "UPDATE tasks SET title = :title, description = :description WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->execute(['id' => $id, 'title' => $title, 'description' => $description]);
    echo "Task updated successfully.\n";
}

function deleteTask($id) {
    global $conn;
    $sql = "DELETE FROM tasks WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->execute(['id' => $id]);
    echo "Task deleted successfully.\n";
}
?>

CLI Commands

Update the CLI script to use the task functions.

<?php
require 'tasks.php';

if (php_sapi_name() !== 'cli') {
    die("This script can only be run from the command line.");
}

$command = $argv[1] ?? null;

switch ($command) {
    case 'create':
        $title = $argv[2] ?? 'Untitled Task';
        $description = $argv[3] ?? '';
        createTask($title, $description);
        break;
    case 'read':
        readTasks();
        break;
    case 'update':
        $id = $argv[2] ?? null;
        $title = $argv[3] ?? 'Updated Task';
        $description = $argv[4] ?? '';
        if ($id) {
            updateTask($id, $title, $description);
        } else {
            echo "Task ID is required for update.\n";
        }
        break;
    case 'delete':
        $id = $argv[2] ?? null;
        if ($id) {
            deleteTask($id);
        } else {
            echo "Task ID is required for delete.\n";
        }
        break;
    default:
        echo "Unknown command.\n";
        break;
}
?>

Testing the Application

1. Create a Task:

    php script.php create "My First Task" "This is a description of my first task."
   

2. Read Tasks:

    php script.php read
   

3. Update a Task:

    php script.php update 1 "Updated Task" "This is an updated description."
   

4. Delete a Task:

    php script.php delete 1
   

Conclusion

In this comprehensive guide, we covered the basics of PHP, how to connect to a database using MySQLi and PDO, and how to perform CRUD operations. We also built a simple task manager CLI application to demonstrate these concepts in practice. With these skills, you are well-equipped to handle database interactions in PHP, whether for web applications or command-line scripts. Happy coding!