RESTful APIs using PHP and MySQL

Good Day, Folks!

In this post, we’re diving into RESTful APIs using PHP and MySQL—a foundational concept in web development that powers countless modern applications. Let’s get started!

What Are RESTful APIs?

RESTful APIs are a widely used architecture style designed for secure and efficient data exchange over the internet. Applications like Spotify, Netflix, and banking platforms rely on RESTful APIs to provide seamless functionality.

The core idea revolves around using HTTP methods—GET, POST, PUT, and DELETE—to interact with a database. These methods form the backbone of CRUD operations (Create, Read, Update, Delete).

Building RESTful APIs with PHP and MySQL

The post focuses on implementing a simple RESTful API using PHP and MySQL. The API setup is divided into two key files:

api.php: Handles HTTP requests and routes them to appropriate actions.

<?php
// api.php
header("Content-Type: application/json");

// Include database connection
include 'db.php';

$method = $_SERVER['REQUEST_METHOD'];
$conn = connect();

// Handle different request methods
switch ($method) {
    case 'GET':
        handleGet($conn);
        break;
    case 'POST':
        handlePost($conn);
        break;
    case 'PUT':
        handlePut($conn);
        break;
    case 'DELETE':
        handleDelete($conn);
        break;
    default:
        echo json_encode(["message" => "Invalid request method"]);
        break;
}

// GET: Fetch all users or a single user by ID
function handleGet($conn) {
    if (isset($_GET['id'])) {
        $id = intval($_GET['id']);
        $sql = "SELECT * FROM users WHERE id = :id";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        echo json_encode($user);
    } else {
        $sql = "SELECT * FROM users";
        $stmt = $conn->query($sql);
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode($users);
    }
}

// POST: Create a new user
function handlePost($conn) {
    $data = json_decode(file_get_contents("php://input"), true);
    $sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':name', $data['name']);
    $stmt->bindParam(':email', $data['email']);
    $stmt->bindParam(':age', $data['age']);
    if ($stmt->execute()) {
        echo json_encode(["message" => "User created successfully"]);
    } else {
        echo json_encode(["message" => "Failed to create user"]);
    }
}

// PUT: Update an existing user
function handlePut($conn) {
    if (isset($_GET['id'])) {
        $id = intval($_GET['id']);
        $data = json_decode(file_get_contents("php://input"), true);

        $sql = "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':name', $data['name']);
        $stmt->bindParam(':email', $data['email']);
        $stmt->bindParam(':age', $data['age']);
        $stmt->bindParam(':id', $id);

        if ($stmt->execute()) {
            echo json_encode(["message" => "User updated successfully"]);
        } else {
            echo json_encode(["message" => "Failed to update user"]);
        }
    } else {
        echo json_encode(["message" => "User ID not specified"]);
    }
}

// DELETE: Delete a user
function handleDelete($conn) {
    if (isset($_GET['id'])) {
        $id = intval($_GET['id']);
        $sql = "DELETE FROM users WHERE id = :id";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':id', $id);

        if ($stmt->execute()) {
            echo json_encode(["message" => "User deleted successfully"]);
        } else {
            echo json_encode(["message" => "Failed to delete user"]);
        }
    } else {
        echo json_encode(["message" => "User ID not specified"]);
    }
}
?>

db.php: Manages the connection to the MySQL database. Key Highlights from the Example: HTTP Methods in Action:

<?php
// db.php
function connect() {
    // Database connection settings
    $host = 'localhost';           // PostgreSQL server host
    $db_name = 'rest_api_db';      // Your database name
    $username = 'xxxx';        // Your PostgreSQL username
    $password = 'xxxx';   // Your PostgreSQL password
    $port = 5432;                  // PostgreSQL default port

    try {
        // Create a new PDO instance for PostgreSQL
        $conn = new PDO("pgsql:host=$host;port=$port;dbname=$db_name", $username, $password);

        // Set error mode to exception for debugging purposes
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $conn; // Return the database connection to be used elsewhere
    } catch (PDOException $e) {
        // Catch any connection error and display a message
        die("Connection failed: " . $e->getMessage());
    }
}
?>

GET: Retrieve data from the database. POST: Create new entries. PUT: Update existing records. DELETE: Remove data from the database. Security Matters: RESTful APIs often handle sensitive data, like credit card details or login credentials. The video emphasizes the importance of using secure connections (e.g., HTTPS) to safeguard information from interception by malicious actors.

Why RESTful APIs Stand Out:

Stateless: REST APIs don’t maintain any client state, enhancing scalability and reliability.

Cacheable: Clients can store responses, reducing server load and improving performance.

Simple & Intuitive: Built on familiar protocols like HTTP and formats like JSON, making them easy to use and understand.

By the end of the example, you’ll gain practical insights into how RESTful APIs operate, along with tips for best practices when implementing them.

So, whether you’re building the next big streaming platform or creating an internal tool, RESTful APIs are an indispensable part of your developer toolkit.

Shwetha Harsha