MySQL and JSON Website Updates

Use REST API to read data, update data, and refresh the website UI.

This is the practical bridge between backend and frontend. MySQL stores the data, PHP exposes it as JSON through REST endpoints, and JavaScript fetches the JSON and updates the page without a full reload.

Hub Basics Intermediate Advanced Labs Challenges

Browser

Sends `fetch()` requests and renders JSON into cards, tables, charts, or form messages.

REST endpoint

Receives the request, validates it, runs SQL, and returns JSON with status codes.

MySQL

Stores the records. The API reads and writes rows using SQL and converts the result set into JSON.

MySQL table example

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  status ENUM('draft','published') DEFAULT 'draft',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
);

JSON_EXTRACT and JSON_SET

JSON columns are useful when some fields are flexible or nested. They let you store structured metadata while still updating only the path that changed.

-- Extract a JSON value
SELECT JSON_EXTRACT(metadata, '$.specs.mp') AS mp
FROM items
WHERE id = 1;

-- Update nested JSON fields
UPDATE items
SET metadata = JSON_SET(metadata, '$.flags.featured', true, '$.color', 'red')
WHERE id = 1;

Transactions for safe multi-step updates

When one API call changes multiple columns or tables, use a transaction so the write fully succeeds or fully rolls back.

START TRANSACTION;
UPDATE items SET name = 'Camera' WHERE id = 1;
UPDATE items
SET metadata = JSON_SET(metadata, '$.updatedBy', 'api')
WHERE id = 1;
COMMIT;

GET endpoint example

This is the classic "load data into the website" endpoint. It queries MySQL, fetches rows, and returns JSON.

<?php
header('Content-Type: application/json; charset=utf-8');

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->query('SELECT id, title, status, updated_at FROM articles ORDER BY id DESC LIMIT 10');
$rows = $stmt->fetchAll();

echo json_encode([
    'success' => true,
    'data' => $rows,
], JSON_UNESCAPED_UNICODE);

POST endpoint example

This creates a new row from a JSON body sent by a form, admin panel, or another service.

<?php
$payload = json_decode(file_get_contents('php://input'), true);

$stmt = $pdo->prepare('INSERT INTO articles (title, body, status) VALUES (:title, :body, :status)');
$stmt->execute([
    ':title' => $payload['title'],
    ':body' => $payload['body'],
    ':status' => $payload['status'] ?? 'draft',
]);

echo json_encode([
    'success' => true,
    'insert_id' => $pdo->lastInsertId(),
]);

Render JSON into the website

async function loadArticles() {
  const response = await fetch('/api/articles');
  const payload = await response.json();

  const container = document.querySelector('#article-list');
  container.innerHTML = payload.data.map((item) => `
    <article class="article-card">
      <h3>${item.title}</h3>
      <p>Status: ${item.status}</p>
    </article>
  `).join('');
}

loadArticles();

Update a record and refresh the UI

async function saveArticle(id, updates) {
  const response = await fetch(`/api/articles/${id}`, {
    method: 'PATCH',
    headers: {
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(updates)
  });

  if (!response.ok) {
    throw new Error(`Update failed with ${response.status}`);
  }

  await loadArticles();
}

Near real-time refresh

REST itself is request-response, so the simplest near real-time website update is short polling.

loadArticles();
setInterval(loadArticles, 30000);

Good production pattern

Use REST for create, read, update, and delete actions.
Use MySQL indexes so filtered reads stay fast.
If you filter by JSON metadata often, consider generated columns plus indexes instead of scanning raw JSON every time.
Return JSON only, not HTML, from API endpoints.
Keep DB credentials on the server, never in browser JavaScript.
Refresh only the changed part of the page after save.

Useful local examples in this project

/pages/api.php shows MySQL-backed JSON output, auth checks, and table querying.
/api/api-example.php shows CRUD against a JSON file, useful for understanding request flow before adding MySQL.
/api/api-webhook.php shows how updates can trigger webhook events after data changes.