Connecting REST APIs with MySQL

# CHAPTER 21

Connecting REST APIs with MySQL #

1. Introduction #

2. Learning Objectives #

• Establish a secure PDO connection to a MySQL database.

• Use PDO Prepared Statements to prevent SQL injection.

• Fetch database records and output them as JSON arrays.

• Insert new records from a JSON API payload into MySQL.

• Handle database connection errors gracefully without leaking credentials.

3. Beginner-Friendly Explanation #

4. Real-World Examples #

• Fetching Users: GET /users tells PHP to ask PDO to run SELECT * FROM users. PDO returns an array of users, and PHP json_encodes it back to the client.

• Creating a Post: POST /posts takes the JSON payload, gives the variables to PDO, and PDO runs INSERT INTO posts...

5. Detailed Code Examples #

1. The Database Connection (db.php):

<?php
$host = &#039;127.0.0.1';
$db   = &#039;api_database';
$user = &#039;root';
$pass = &#039;';
$charset = &#039;utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Fetch as associative arrays!
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    // DO NOT output $e->getMessage() in production! It leaks DB passwords.
    http_response_code(500);
    echo json_encode(["error" => "Database connection failed."]);
    exit;
}
?>

2. Integrating PDO into the API:

<?php
require &#039;db.php'; // Include the PDO connection

// Assume $method and $uri are set up like Chapter 20

if ($method === &#039;GET' && $uri[2] === 'users') {
    // GET /users
    $stmt = $pdo->query(&#039;SELECT id, name, email FROM users');
    $users = $stmt->fetchAll(); // PDO::FETCH_ASSOC is default now
    
    echo json_encode(["data" => $users]);
} 

elseif ($method === &#039;POST' && $uri[2] === 'users') {
    // POST /users
    $input = json_decode(file_get_contents(&#039;php://input'), true);
    
    // Always use Prepared Statements!
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        &#039;:name' => $input['name'],
        &#039;:email' => $input['email']
    ]);
    
    $newId = $pdo->lastInsertId();
    http_response_code(201);
    echo json_encode(["message" => "User created", "id" => $newId]);
}
?>

6. Request/Response Examples #

{
  "data": [
    {
      "id": 1,
      "name": "Alice",
      "email": "alice@example.com"
    },
    {
      "id": 2,
      "name": "Bob",
      "email": "bob@example.com"
    }
  ]
}

7. HTTP Examples #

• GET triggers $pdo->query() or $stmt->fetchAll().

• POST triggers $pdo->prepare(INSERT...) and $pdo->lastInsertId().

• DELETE triggers $pdo->prepare(DELETE...) and $stmt->rowCount() to see if a row was actually deleted.

8. JSON Examples #

// Inside PUT /users/5
$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$stmt->execute([':name' => $input['name'], ':id' => $userId]);

if ($stmt->rowCount() > 0) {
    echo json_encode(["success" => true, "message" => "User updated"]);
} else {
    // If rowCount is 0, the ID didn't exist OR the new name was the same as the old one
    echo json_encode(["success" => false, "message" => "No changes made"]);
}

9. Best Practices #

• PDO::FETCHASSOC: Always set this as your default fetch mode. It forces PDO to return arrays with column names as keys (['name' => 'Alice']), which jsonencode perfectly translates into JSON objects.

• Never expose passwords: When selecting data (e.g., SELECT * FROM users), explicitly name the columns you want (SELECT id, name, email). If you use *, you might accidentally jsonencode the user's hashed password and send it to the frontend!

• mysqli vs PDO: Beginners often use the older mysqliquery functions. PDO is highly preferred for APIs because it allows named parameters (:name) and returns clean arrays perfectly suited for JSON.

1. 1. Look at this bad code: $pdo->query("SELECT * FROM users WHERE name = '" . $GET['name'] . "'");. Why is this incredibly dangerous?

12. Coding Challenges #

13. MCQs with Answers #

14. Interview Questions #

• Q: Explain why Prepared Statements are mandatory when building APIs that accept user data.

• Q: If a PDO connection fails, why is it a security risk to output the raw PDOException message to the API client?

• Q: Describe how you handle the output of PDO::lastInsertId() in a REST API POST response.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #