PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
);
} catch (PDOException $e) {
http_response_code(500);
die('Cannot connect to MySQL '
. htmlspecialchars($DB_NAME) . ': ' . htmlspecialchars($e->getMessage())
. '
Is WAMP MySQL running, and have you run python pipeline/load_mysql.py?
');
}
$RETAILERS = ['pnp' => 'Pick n Pay', 'checkers' => 'Checkers', 'woolworths' => 'Woolworths'];
// ---- inputs ----
$mode = ($_GET['mode'] ?? 'mvp') === 'all' ? 'all' : 'mvp';
$q = trim($_GET['q'] ?? '');
$bucket = trim($_GET['bucket'] ?? '');
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 60;
$offset = ($page - 1) * $perPage;
// ---- header stats ----
$stats = $pdo->query("SELECT
(SELECT COUNT(*) FROM catalogue) AS catalogue_rows,
(SELECT COUNT(*) FROM mvp_catalogue) AS mvp_rows,
(SELECT COUNT(*) FROM mvp_catalogue WHERE n_retailers>=3) AS mvp_all3
")->fetch();
$buckets = $pdo->query("SELECT bucket, COUNT(*) c FROM mvp_catalogue GROUP BY bucket ORDER BY bucket")
->fetchAll();
// ---- main query ----
$rows = [];
$totalRows = 0;
if ($mode === 'mvp') {
$where = [];
$args = [];
if ($q !== '') {
$where[] = "(CONVERT(m.name USING utf8mb4) LIKE :q OR CONVERT(m.brand USING utf8mb4) LIKE :q "
. "OR CONVERT(m.barcode USING utf8mb4) LIKE :q)";
$args[':q'] = "%$q%";
}
if ($bucket !== '') { $where[] = "CONVERT(m.bucket USING utf8mb4) = :bucket"; $args[':bucket'] = $bucket; }
$whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
$cnt = $pdo->prepare("SELECT COUNT(*) c FROM mvp_catalogue m $whereSql");
$cnt->execute($args); $totalRows = (int)$cnt->fetch()['c'];
// image via a single grouped join (CONVERT keeps it collation-safe across latin1/utf8mb4)
$sql = "SELECT m.*, img.image_url
FROM mvp_catalogue m
LEFT JOIN (
SELECT barcode_norm, MIN(image_url) AS image_url
FROM catalogue
WHERE image_url IS NOT NULL AND image_url <> ''
GROUP BY barcode_norm
) img ON CONVERT(img.barcode_norm USING utf8mb4) = CONVERT(m.barcode USING utf8mb4)
$whereSql
ORDER BY m.bucket, m.n_retailers DESC, m.name
LIMIT $perPage OFFSET $offset";
$stmt = $pdo->prepare($sql);
$stmt->execute($args);
$rows = $stmt->fetchAll();
} else {
// all barcode-matched products, pivoted by barcode
$having = "HAVING COUNT(DISTINCT retailer) >= 2";
$where = ["barcode_norm IS NOT NULL", "is_instore_bc = 0", "price IS NOT NULL"];
$args = [];
if ($q !== '') {
$where[] = "(CONVERT(name USING utf8mb4) LIKE :q OR CONVERT(brand USING utf8mb4) LIKE :q "
. "OR CONVERT(barcode_norm USING utf8mb4) LIKE :q)";
$args[':q'] = "%$q%";
}
$whereSql = 'WHERE ' . implode(' AND ', $where);
$cntSql = "SELECT COUNT(*) c FROM (
SELECT barcode_norm FROM catalogue $whereSql
GROUP BY barcode_norm $having) t";
$cnt = $pdo->prepare($cntSql); $cnt->execute($args); $totalRows = (int)$cnt->fetch()['c'];
$sql = "SELECT barcode_norm AS barcode,
MAX(name) AS name, MAX(brand) AS brand,
COUNT(DISTINCT retailer) AS n_retailers,
MAX(CASE WHEN retailer='pnp' THEN price END) AS pnp_price,
MAX(CASE WHEN retailer='checkers' THEN price END) AS checkers_price,
MAX(CASE WHEN retailer='woolworths' THEN price END) AS woolworths_price,
MIN(price) AS min_price, MAX(price) AS max_price,
(SELECT image_url FROM catalogue c2
WHERE c2.barcode_norm = catalogue.barcode_norm AND c2.image_url IS NOT NULL
LIMIT 1) AS image_url
FROM catalogue
$whereSql
GROUP BY barcode_norm
$having
ORDER BY (MAX(price)-MIN(price)) DESC
LIMIT $perPage OFFSET $offset";
$stmt = $pdo->prepare($sql);
$stmt->execute($args);
$rows = $stmt->fetchAll();
}
$totalPages = max(1, (int)ceil($totalRows / $perPage));
function money($v) { return $v === null ? null : 'R' . number_format((float)$v, 2); }
function qs($overrides = []) {
$base = ['mode' => $_GET['mode'] ?? 'mvp', 'q' => $_GET['q'] ?? '',
'bucket' => $_GET['bucket'] ?? '', 'page' => $_GET['page'] ?? 1];
return htmlspecialchars(http_build_query(array_merge($base, $overrides)));
}
function h($s) { return htmlspecialchars((string)$s, ENT_QUOTES); }
?>
Baskit — Price Comparison
products
· page of
| Product |
$label): ?>
|
Cheapest |
You save |
| No products found. Try a different search or bucket. |
$r['pnp_price'], 'checkers'=>$r['checkers_price'], 'woolworths'=>$r['woolworths_price']];
$valid = array_filter($prices, fn($v)=>$v!==null);
$min = $valid ? min($valid) : null;
$max = $valid ? max($valid) : null;
$save = ($min!==null && $max!==null) ? ($max-$min) : 0;
$savePct = $min>0 ? round($save/$max*100) : 0;
$cheapest = null;
foreach ($prices as $k=>$v) { if ($v!==null && (float)$v==(float)$min) { $cheapest=$k; break; } }
?>
 ?>)
|
barcode
|
$label):
$v = $prices[$key];
$isCheap = ($v!==null && $cheapest===$key && count($valid)>1);
?>
—
|
'.h($RETAILERS[$cheapest]).'' : '—' ?> |
0): ?>%
—
|
1): ?>