PHP + MySQL Tutorial: Implementing a RESTful API from Scratch

RESTful APIs serve as the backbone of modern web applications, enabling seamless communication between different systems and platforms. This comprehensive tutorial demonstrates how to build a robust RESTful API using PHP and MySQL, implementing full CRUD operations with security best practices and proper error handling.

Understanding RESTful Architecture

REST (Representational State Transfer) operates on a simple principle: each URL represents a resource, and HTTP methods define actions performed on these resources. The four primary HTTP methods map directly to CRUD operations:

• GET - Retrieves data (Read)
• POST - Creates new resources (Create)
• PUT - Updates existing resources (Update)
• DELETE - Removes resources (Delete)

Unlike SOAP or GraphQL, REST APIs maintain statelessness, making them highly scalable and cacheable. Each request contains all necessary information for processing, reducing server memory overhead.

Project Structure and Setup

A well-organized project structure ensures maintainability and scalability. Our API follows the MVC pattern with clear separation of concerns:

Database Configuration

Create a secure database connection using PDO with proper error handling and prepared statements. The config/database.php file establishes our MySQL connection:

conn = null;
        
        try {
            $dsn = "mysql:host=" . $this->host . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_PERSISTENT => false
            ];
            
            $this->conn = new PDO($dsn, $this->username, $this->password, $options);
        } catch(PDOException $e) {
            error_log("Connection error: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
        
        return $this->conn;
    }
}
?>

Creating the Data Model

The Item model handles all database operations with proper validation and security measures. Create models/Item.php:

conn = $db;
    }
    
    // Read all items with pagination
    public function read($limit = 10, $offset = 0) {
        $query = "SELECT id, name, description, price, created_at 
                 FROM " . $this->table_name . " 
                 ORDER BY created_at DESC 
                 LIMIT :limit OFFSET :offset";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(\':limit\', $limit, PDO::PARAM_INT);
        $stmt->bindParam(\':offset\', $offset, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }
    
    // Create new item
    public function create() {
        $query = "INSERT INTO " . $this->table_name . " 
                 (name, description, price) 
                 VALUES (:name, :description, :price)";
        
        $stmt = $this->conn->prepare($query);
        
        // Sanitize input
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->price = floatval($this->price);
        
        $stmt->bindParam(\':name\', $this->name);
        $stmt->bindParam(\':description\', $this->description);
        $stmt->bindParam(\':price\', $this->price);
        
        if($stmt->execute()) {
            $this->id = $this->conn->lastInsertId();
            return true;
        }
        
        return false;
    }
    
    // Update existing item
    public function update() {
        $query = "UPDATE " . $this->table_name . " 
                 SET name = :name, description = :description, price = :price 
                 WHERE id = :id";
        
        $stmt = $this->conn->prepare($query);
        
        $stmt->bindParam(\':name\', $this->name);
        $stmt->bindParam(\':description\', $this->description);
        $stmt->bindParam(\':price\', $this->price);
        $stmt->bindParam(\':id\', $this->id);
        
        return $stmt->execute();
    }
    
    // Delete item
    public function delete() {
        $query = "DELETE FROM " . $this->table_name . " WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(\':id\', $this->id);
        
        return $stmt->execute();
    }
}
?>

Building the API Controller

The controller manages HTTP requests and coordinates between the model and response formatting. This separation ensures clean, testable code:

db = $database->getConnection();
        $this->item = new Item($this->db);
        $this->response = new Response();
    }
    
    public function handleRequest() {
        $method = $_SERVER[\'REQUEST_METHOD\'];
        $path = parse_url($_SERVER[\'REQUEST_URI\'], PHP_URL_PATH);
        $segments = explode(\'/\', trim($path, \'/\'));
        
        switch($method) {
            case \'GET\':
                $this->handleGet($segments);
                break;
            case \'POST\':
                $this->handlePost();
                break;
            case \'PUT\':
                $this->handlePut($segments);
                break;
            case \'DELETE\':
                $this->handleDelete($segments);
                break;
            default:
                $this->response->methodNotAllowed();
        }
    }
    
    private function handleGet($segments) {
        try {
            $items = $this->item->read();
            $this->response->success($items);
        } catch(Exception $e) {
            $this->response->error(\'Failed to retrieve items\', 500);
        }
    }
    
    private function handlePost() {
        $data = json_decode(file_get_contents("php://input"), true);
        
        if(!$this->validateItemData($data)) {
            $this->response->error(\'Invalid input data\', 400);
            return;
        }
        
        $this->item->name = $data[\'name\'];
        $this->item->description = $data[\'description\'];
        $this->item->price = $data[\'price\'];
        
        if($this->item->create()) {
            $this->response->created([\'id\' => $this->item->id, \'message\' => \'Item created successfully\']);
        } else {
            $this->response->error(\'Failed to create item\', 500);
        }
    }
    
    private function validateItemData($data) {
        return isset($data[\'name\']) && 
               isset($data[\'description\']) && 
               isset($data[\'price\']) &&
               !empty(trim($data[\'name\'])) &&
               is_numeric($data[\'price\']);
    }
}
?>

Response Standardization

Consistent API responses improve client-side integration and debugging. The Response utility class ensures uniform JSON output across all endpoints:

output([
            \'status\' => \'success\',
            \'message\' => $message,
            \'data\' => $data
        ], 200);
    }
    
    public function created($data = null) {
        $this->output([
            \'status\' => \'success\',
            \'message\' => \'Resource created\',
            \'data\' => $data
        ], 201);
    }
    
    public function error($message, $code = 400) {
        $this->output([
            \'status\' => \'error\',
            \'message\' => $message,
            \'data\' => null
        ], $code);
    }
    
    private function output($data, $httpCode = 200) {
        header(\'Content-Type: application/json\');
        header(\'Access-Control-Allow-Origin: *\');
        header(\'Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS\');
        header(\'Access-Control-Allow-Headers: Content-Type, Authorization\');
        
        http_response_code($httpCode);
        echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
        exit;
    }
}
?>

Security Considerations

Production APIs require robust security measures to protect against common vulnerabilities:

• Input Validation: Validate and sanitize all incoming data
• SQL Injection Prevention: Use prepared statements exclusively
• CORS Headers: Configure appropriate cross-origin policies
• Rate Limiting: Implement request throttling to prevent abuse
• Authentication: Add JWT or API key validation for protected endpoints

Testing Your API

Thorough testing ensures reliability and catches edge cases early in development. Use tools like Postman or cURL to test each endpoint:

# Test GET request
curl -X GET http://localhost/api/items

# Test POST request
curl -X POST http://localhost/api/items \\
  -H "Content-Type: application/json" \\
  -d \'{"name":"Sample Item","description":"Test description","price":29.99}\'

# Test PUT request
curl -X PUT http://localhost/api/items/1 \\
  -H "Content-Type: application/json" \\
  -d \'{"name":"Updated Item","description":"Updated description","price":39.99}\'

# Test DELETE request
curl -X DELETE http://localhost/api/items/1

This implementation provides a solid foundation for building scalable RESTful APIs with PHP and MySQL. The modular structure allows easy extension with additional features like authentication, caching, or advanced server configurations. For enhanced security and performance in production environments, consider implementing additional layers such as API gateways and optimized database indexing strategies.