CRUD — সম্পূর্ণ উদাহরণ (Product Model)

Schema

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Product Model Class

<?php
class Product {
    public static function create(array $data): int {
        $conn = DB::getInstance()->getConnection();
        $stmt = $conn->prepare("
            INSERT INTO products (name, price, stock, description)
            VALUES (?, ?, ?, ?)
        ");
        $stmt->bind_param("sdis",
            $data["name"], $data["price"],
            $data["stock"], $data["description"]
        );
        $stmt->execute();
        $id = $conn->insert_id;
        $stmt->close();
        return $id;
    }

    public static function all(int $limit=50, int $offset=0): array {
        $conn = DB::getInstance()->getConnection();
        $stmt = $conn->prepare("SELECT * FROM products ORDER BY id DESC LIMIT ? OFFSET ?");
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }

    public static function find(int $id): ?array {
        $conn = DB::getInstance()->getConnection();
        $stmt = $conn->prepare("SELECT * FROM products WHERE id=?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row ?: null;
    }

    public static function search(string $q): array {
        $conn = DB::getInstance()->getConnection();
        $like = "%$q%";
        $stmt = $conn->prepare("SELECT * FROM products WHERE name LIKE ? OR description LIKE ?");
        $stmt->bind_param("ss", $like, $like);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }

    public static function update(int $id, array $data): bool {
        $conn = DB::getInstance()->getConnection();
        $stmt = $conn->prepare("
            UPDATE products SET name=?, price=?, stock=?, description=?
            WHERE id=?
        ");
        $stmt->bind_param("sdisi",
            $data["name"], $data["price"],
            $data["stock"], $data["description"], $id
        );
        $stmt->execute();
        $ok = $stmt->affected_rows > 0;
        $stmt->close();
        return $ok;
    }

    public static function delete(int $id): bool {
        $conn = DB::getInstance()->getConnection();
        $stmt = $conn->prepare("DELETE FROM products WHERE id=?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $ok = $stmt->affected_rows > 0;
        $stmt->close();
        return $ok;
    }

    public static function count(): int {
        $conn = DB::getInstance()->getConnection();
        $r = $conn->query("SELECT COUNT(*) AS c FROM products");
        return (int)$r->fetch_assoc()["c"];
    }
}
?>

ব্যবহার

$id = Product::create([
    "name" => "ল্যাপটপ", "price" => 75000,
    "stock" => 10, "description" => "Gaming"
]);

$products = Product::all(10);
$p = Product::find(1);
$results = Product::search("ল্যাপটপ");
Product::update(1, [...]);
Product::delete(5);
echo Product::count();

Transaction

$conn = DB::getInstance()->getConnection();

try {
    $conn->begin_transaction();

    // Order তৈরি
    $stmt = $conn->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
    $stmt->bind_param("id", $userId, $total);
    $stmt->execute();
    $orderId = $conn->insert_id;

    // Items যোগ
    $stmt = $conn->prepare("INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)");
    foreach ($items as $item) {
        $stmt->bind_param("iii", $orderId, $item["pid"], $item["qty"]);
        $stmt->execute();
    }

    // Stock কমাও
    $stmt = $conn->prepare("UPDATE products SET stock = stock - ? WHERE id = ?");
    foreach ($items as $item) {
        $stmt->bind_param("ii", $item["qty"], $item["pid"]);
        $stmt->execute();
    }

    $conn->commit();
    echo "Success: $orderId";
} catch (Exception $e) {
    $conn->rollback();
    echo "ভুল: " . $e->getMessage();
}