13.7 Caching Likes | Web Application Development

A Like button looks simple, but counting rows in a likes table on every page view becomes slow when a product has thousands of likes and the page lists many products. Caching means keeping a ready-made result so you do not recompute it every time. Here, we cache each product's like count.

Design

likes table: the truth (who liked what). A unique key stops duplicate likes.
products.like_count: a cached counter updated whenever a like is added or removed. Reading it is instant.

SQL

CREATE TABLE likes (
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, product_id),           -- one like per user per product
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

api/like.php (toggle like or unlike):

PHP

<?php
require '../includes/auth.php'; require '../config/db.php';
header('Content-Type: application/json');

if (!is_logged_in()) { http_response_code(401); echo json_encode(['error' => 'Login required']); exit; }

$uid = $_SESSION['user_id'];
$pid = (int)($_POST['product_id'] ?? 0);

$pdo->beginTransaction();
$del = $pdo->prepare('DELETE FROM likes WHERE user_id = ? AND product_id = ?');
$del->execute([$uid, $pid]);

if ($del->rowCount() > 0) {                         // it existed: so this click means UNLIKE
    $pdo->prepare('UPDATE products SET like_count = GREATEST(like_count - 1, 0) WHERE id = ?')->execute([$pid]);
    $liked = false;
} else {                                            // it did not exist: LIKE
    $pdo->prepare('INSERT INTO likes (user_id, product_id) VALUES (?, ?)')->execute([$uid, $pid]);
    $pdo->prepare('UPDATE products SET like_count = like_count + 1 WHERE id = ?')->execute([$pid]);
    $liked = true;
}
$pdo->commit();

$count = $pdo->prepare('SELECT like_count FROM products WHERE id = ?');
$count->execute([$pid]);
echo json_encode(['liked' => $liked, 'count' => (int)$count->fetchColumn()]);

JavaScript

document.querySelectorAll('.like-btn').forEach(btn => {
  btn.addEventListener('click', async () => {
    const res = await fetch('api/like.php', {
      method: 'POST',
      body: new URLSearchParams({ product_id: btn.dataset.id })
    });
    if (res.status === 401) { location.href = 'login.php'; return; }
    const data = await res.json();
    btn.classList.toggle('liked', data.liked);
    btn.querySelector('.count').textContent = data.count;
  });
});

Going further (optional caching layers)

Server memory cache (APCu, Redis, Memcached): store like_count:{id}; increment in the cache; write back to the database every minute. Useful for very busy sites.
Browser cache: localStorage can remember which products the user liked to colour buttons instantly; the server remains the source of truth.
Consistency: run the occasional repair query to confirm cached counts match reality:

SQL

UPDATE products p
SET like_count = (SELECT COUNT(*) FROM likes l WHERE l.product_id = p.id);

Rule: a cache is a copy for speed. If cache and source disagree, the source wins.