Products API: Search, Filter, Pages

<?php
$where = array('p.is_active = 1');   // always: only visible products
$params = array();

if ($q !== '') {                     // ?q=watch
    $where[] = '(p.name LIKE ? OR p.short_desc LIKE ?)';
    $params[] = '%' . $q . '%';
    $params[] = '%' . $q . '%';
}
if ($categoryId > 0) {               // ?category_id=3
    $where[] = 'p.category_id = ?';
    $params[] = $categoryId;
}
// … min_price, max_price the same way

$whereSql = implode(' AND ', $where);
// "p.is_active = 1 AND (p.name LIKE ? OR p.short_desc LIKE ?) AND p.category_id = ?"

<?php
// ❌ NEVER: user text inside SQL
$sql = "SELECT * FROM products ORDER BY " . $_GET['sort'];
// ?sort=price;DROP TABLE users  → disaster

// ✅ Whitelist: the user picks a KEY, we choose the SQL
$sorts = array(
    'price_asc'  => 'p.price ASC',
    'price_desc' => 'p.price DESC',
);
$orderBy = isset($sorts[$sort]) ? $sorts[$sort] : 'p.created_at DESC';

<?php
// Product list with search, category filter, price filter, sorting and paging.
// GET api/products.php?q=watch&category_id=3&min_price=500&max_price=3000&sort=price_asc&page=1&limit=20
require_once __DIR__ . '/../includes/helpers.php';
require_method('GET');

$q = q_str('q');
$categoryId = q_int('category_id');
$minPrice = q_int('min_price');
$maxPrice = q_int('max_price');
$sort = q_str('sort', 'newest');
$page = max(1, q_int('page', 1));
$limit = min(50, max(1, q_int('limit', 20)));
$offset = ($page - 1) * $limit;

$where = array('p.is_active = 1');
$params = array();
if ($q !== '') {
    $where[] = '(p.name LIKE ? OR p.short_desc LIKE ?)';
    $like = '%' . $q . '%';
    $params[] = $like;
    $params[] = $like;
}
if ($categoryId > 0) {
    $where[] = 'p.category_id = ?';
    $params[] = $categoryId;
}
if ($minPrice > 0) {
    $where[] = 'p.price >= ?';
    $params[] = $minPrice;
}
if ($maxPrice > 0) {
    $where[] = 'p.price <= ?';
    $params[] = $maxPrice;
}

// Only whitelisted ORDER BY values: user text never goes into SQL directly.
$sorts = array(
    'newest'     => 'p.created_at DESC, p.id DESC',
    'price_asc'  => 'p.price ASC',
    'price_desc' => 'p.price DESC',
    'rating'     => 'p.rating_avg DESC, p.rating_count DESC',
    'popular'    => 'p.rating_count DESC',
    'discount'   => '(p.mrp - p.price) / p.mrp DESC',
);
$orderBy = isset($sorts[$sort]) ? $sorts[$sort] : $sorts['newest'];
$whereSql = implode(' AND ', $where);

$count = db()->prepare("SELECT COUNT(*) FROM products p WHERE $whereSql");
$count->execute($params);
$total = (int) $count->fetchColumn();

// LIMIT/OFFSET are ints we built ourselves, so they are safe to place in the SQL.
$stmt = db()->prepare("SELECT p.* FROM products p WHERE $whereSql ORDER BY $orderBy LIMIT $limit OFFSET $offset");
$stmt->execute($params);

$userId = optional_user_id();
$wish = $userId ? wishlist_ids($userId) : array();
$items = array();
foreach ($stmt->fetchAll() as $r) {
    $p = product_out($r);
    $p['in_wishlist'] = isset($wish[$p['id']]);
    $items[] = $p;
}

ok(array(
    'items'    => $items,
    'page'     => $page,
    'limit'    => $limit,
    'total'    => $total,
    'has_more' => $offset + count($items) < $total,
));

<?php
// One product with gallery, category, latest reviews and similar products.
// GET api/product.php?id=7
require_once __DIR__ . '/../includes/helpers.php';
require_method('GET');

$id = q_int('id');
$stmt = db()->prepare(
    'SELECT p.*, c.name AS category_name FROM products p
     JOIN categories c ON c.id = p.category_id
     WHERE p.id = ? AND p.is_active = 1'
);
$stmt->execute(array($id));
$row = $stmt->fetch();
if (!$row) {
    fail('Product not found.', 404);
}

$p = product_out($row);
$p['description'] = $row['description'];
$p['category_name'] = $row['category_name'];

$imgs = db()->prepare('SELECT image FROM product_images WHERE product_id = ? ORDER BY sort_order, id');
$imgs->execute(array($id));
$gallery = array();
foreach ($imgs->fetchAll(PDO::FETCH_COLUMN) as $img) {
    $gallery[] = img_url($img);
}
if (count($gallery) === 0 && $p['image'] !== null) {
    $gallery[] = $p['image'];
}
$p['images'] = $gallery;

$rev = db()->prepare(
    'SELECT r.rating, r.comment, r.created_at, u.name FROM reviews r
     JOIN users u ON u.id = r.user_id WHERE r.product_id = ? ORDER BY r.id DESC LIMIT 3'
);
$rev->execute(array($id));
$reviews = array();
foreach ($rev->fetchAll() as $r) {
    $reviews[] = array('name' => $r['name'], 'rating' => (int) $r['rating'], 'comment' => $r['comment'], 'date' => substr($r['created_at'], 0, 10));
}
$p['reviews'] = $reviews;

$sim = db()->prepare('SELECT * FROM products WHERE category_id = ? AND id <> ? AND is_active = 1 ORDER BY rating_avg DESC LIMIT 6');
$sim->execute(array($row['category_id'], $id));

$userId = optional_user_id();
$wish = $userId ? wishlist_ids($userId) : array();
$p['in_wishlist'] = isset($wish[$id]);
$p['cart_qty'] = 0;
if ($userId) {
    $c = db()->prepare('SELECT qty FROM cart_items WHERE user_id = ? AND product_id = ?');
    $c->execute(array($userId, $id));
    $p['cart_qty'] = (int) $c->fetchColumn();
}

$similar = array();
foreach ($sim->fetchAll() as $s) {
    $sp = product_out($s);
    $sp['in_wishlist'] = isset($wish[$sp['id']]);
    $similar[] = $sp;
}
$p['similar'] = $similar;

ok($p);

<?php
// From includes/helpers.php – used by home, products, product, wishlist
function product_out($p)
{
    $price = (float) $p['price'];
    $mrp = (float) $p['mrp'];
    return array(
        'id'           => (int) $p['id'],
        'category_id'  => (int) $p['category_id'],
        'name'         => $p['name'],
        'short_desc'   => $p['short_desc'],
        'price'        => $price,
        'mrp'          => $mrp,
        'discount_pct' => $mrp > $price ? (int) round(($mrp - $price) * 100 / $mrp) : 0,
        'stock'        => (int) $p['stock'],
        'image'        => img_url($p['image']),
        'rating'       => (float) $p['rating_avg'],
        'rating_count' => (int) $p['rating_count'],
    );
}