Database basics. A relational database stores data in tables (rows and columns). Each table has a primary key (a unique id per row). Foreign keys link tables. We talk to the database in SQL.
Create the database in phpMyAdmin (SQL tab):
SQL
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role VARCHAR(20) NOT NULL DEFAULT 'customer'
);Connect with PDO (recommended). PDO (PHP Data Objects) works with many databases and supports prepared statements. Put the connection in db.php:
PHP
<?php
$host = 'localhost';
$db = 'shop';
$user = 'root'; // XAMPP default; use a limited user on a real server
$pass = ''; // XAMPP default is empty; always set a strong password online
try {
$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';charset=utf8mb4', $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // throw errors
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // rows as associative arrays
PDO::ATTR_EMULATE_PREPARES => false,
]);
} catch (PDOException $e) {
error_log($e->getMessage()); // log details privately
exit('Database connection failed'); // show a generic message to users
}Include it wherever needed with require_once 'db.php';. (The MySQLi extension is an alternative; the same ideas apply.)