PHP MySQL REST API for Products: Full CRUD Operation

Step 1: Set Up the MySQL Database

Run the following SQL commands to set up the database and table:

Step 2: Create the PHP Files

Database connection (db.php)
API functionality (product.php)
API endpoints (index.php)

Step 3: Database Connection (`db.php`)

Create a file named db.php:

Step 4: Product Class (`product.php`)

Create a file named product.php:

<?php
include_once 'db.php';

class Product {

    // Get all products
    public static function getProducts() {
        global $conn;
        $stmt = $conn->prepare("SELECT * FROM products");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Get a single product by ID
    public static function getProduct($id) {
        global $conn;
        $stmt = $conn->prepare("SELECT * FROM products WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Create a new product
    public static function createProduct($name, $description, $price) {
        global $conn;
        $stmt = $conn->prepare("INSERT INTO products (name, description, price) VALUES (:name, :description, :price)");
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':price', $price);
        return $stmt->execute();
    }

    // Update an existing product
    public static function updateProduct($id, $name, $description, $price) {
        global $conn;
        $stmt = $conn->prepare("UPDATE products SET name = :name, description = :description, price = :price WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':price', $price);
        return $stmt->execute();
    }

    // Delete a product
    public static function deleteProduct($id) {
        global $conn;
        $stmt = $conn->prepare("DELETE FROM products WHERE id = :id");
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }
}
?>

Step 5: API Endpoints (`index.php`)

Create a file named index.php:

<?php
header('Content-Type: application/json');
include_once 'product.php';

// Check the request method
$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));

switch ($method) {
    case 'GET':
        if (isset($request[1])) {
            // Single product
            $product = Product::getProduct($request[1);
            if ($product) {
                echo json_encode($product);
            } else {
                echo json_encode(['message' => 'Product not found']);
            }
        } else {
            // All products
            $products = Product::getProducts();
            echo json_encode($products);
        }
        break;

    case 'POST':
        $data = json_decode(file_get_contents("php://input"));
        if (isset($data->name) && isset($data->description) && isset($data->price)) {
            $result = Product::createProduct($data->name, $data->description, $data->price);
            if ($result) {
                echo json_encode(['message' => 'Product created successfully']);
            } else {
                echo json_encode(['message' => 'Failed to create product']);
            }
        } else {
            echo json_encode(['message' => 'Invalid input']);
        }
        break;

    case 'PUT':
        if (isset($request[0])) {
            $data = json_decode(file_get_contents("php://input"));
            if (isset($data->name) && isset($data->description) && isset($data->price)) {
                $result = Product::updateProduct($request[0], $data->name, $data->description, $data->price);
                if ($result) {
                    echo json_encode(['message' => 'Product updated successfully']);
                } else {
                    echo json_encode(['message' => 'Failed to update product']);
                }
            } else {
                echo json_encode(['message' => 'Invalid input']);
            }
        } else {
            echo json_encode(['message' => 'Product ID required']);
        }
        break;

    case 'DELETE':
        if (isset($request[0])) {
            $result = Product::deleteProduct($request[0]);
            if ($result) {
                echo json_encode(['message' => 'Product deleted successfully']);
            } else {
                echo json_encode(['message' => 'Failed to delete product']);
            }
        } else {
            echo json_encode(['message' => 'Product ID required']);
        }
        break;

    default:
        echo json_encode(['message' => 'Method not allowed']);
        break;
}
?>

Step 6: Testing the API

1. GET (Retrieve Products)

All Products: GET /index.php/products
Single Product: GET /index.php/products/{id}

2. POST (Create Product)

Send a JSON payload to create a new product:

Endpoint: POST /index.php/products

3. PUT (Update Product)

Send a JSON payload to update an existing product:

Endpoint: PUT /index.php/products/{id}

4. DELETE (Delete Product)

Endpoint: DELETE /index.php/products/{id}

Our dedicated and industry-experienced trainers are here to teach you the core concepts of each subject. After mastering these fundamentals, you'll work on real-world projects to gain practical experience. We place special emphasis on these projects, ensuring that when you secure a placement, you'll be ready to seamlessly integrate and contribute to your new team.

About Us

Categories

100 React JS questions

Angular 20

Animations

ASP.NET

Block Pattern

Our Location

PHP MySQL REST API for Products: Full CRUD Operation