PDO va ma'lumotlar bazasi

// Har bir misol qat'iy turlar rejimida ishlaydi.
// Misollar uchun xotiradagi SQLite bazasidan foydalanamiz.
function bazaYarat(): PDO
{
    $pdo = new PDO("sqlite::memory:", null, null, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);

    $pdo->exec("
        CREATE TABLE mahsulotlar (
            id     INTEGER PRIMARY KEY AUTOINCREMENT,
            nom    TEXT    NOT NULL,
            narx   INTEGER NOT NULL,
            soni   INTEGER NOT NULL DEFAULT 0,
            turkum TEXT    NOT NULL
        )
    ");

    $qatorlar = [
        ["Klaviatura",  35000000, 12, "aksessuar"],
        ["Sichqoncha",  12000000, 40, "aksessuar"],
        ["Monitor 24",  240000000, 5, "monitor"],
        ["Monitor 27",  390000000, 2, "monitor"],
        ["Quloqchin",    8900000,  0, "aksessuar"],
    ];

    $stmt = $pdo->prepare(
        "INSERT INTO mahsulotlar (nom, narx, soni, turkum) VALUES (?, ?, ?, ?)"
    );
    foreach ($qatorlar as $q) {
        $stmt->execute($q);
    }

    return $pdo;
}

// Haqiqiy loyihada ulanish shunday bo'ladi:
//
// $dsn = "mysql:host=localhost;dbname=dokon;charset=utf8mb4";
// $pdo = new PDO($dsn, $foydalanuvchi, $parol, [
//     PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
//     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
//     PDO::ATTR_EMULATE_PREPARES   => false,
// ]);

$pdo = bazaYarat();

echo "Ulandi. Drayver: " . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) . "\n";

echo "\nMuhim uchta sozlama:\n";
$sozlamalar = [
    ["ERRMODE_EXCEPTION", "Xato bo'lsa istisno tashlaydi", "Aks holda JIMGINA false qaytaradi"],
    ["FETCH_ASSOC", "Natija assotsiativ massiv", "Standart holatda har qator IKKI marta keladi"],
    ["EMULATE_PREPARES=false", "Haqiqiy tayyorlangan so'rov", "Taqlid qilingani zaifroq"],
];

foreach ($sozlamalar as [$nom, $nima, $nimaga]) {
    printf("  %-24s %s\n", $nom, $nima);
    printf("  %-24s (%s)\n", "", $nimaga);
}

$soni = $pdo->query("SELECT COUNT(*) FROM mahsulotlar")->fetchColumn();
echo "\nJadvalda {$soni} ta yozuv bor.\n";

$pdo = bazaYarat();

// 1. Pozitsion parametrlar - ? belgisi
$stmt = $pdo->prepare("SELECT nom, narx FROM mahsulotlar WHERE turkum = ? AND soni > ?");
$stmt->execute(["aksessuar", 0]);

echo "Pozitsion parametrlar bilan:\n";
foreach ($stmt as $q) {
    printf("  %-14s %12s\n", $q["nom"], number_format($q["narx"] / 100, 0, ",", " "));
}

// 2. Nomli parametrlar - o'qishga qulayroq
echo "\nNomli parametrlar bilan:\n";
$stmt = $pdo->prepare("
    SELECT nom, narx, soni
    FROM mahsulotlar
    WHERE narx BETWEEN :eng_kam AND :eng_kop
    ORDER BY narx DESC
");
$stmt->execute(["eng_kam" => 10000000, "eng_kop" => 300000000]);

foreach ($stmt->fetchAll() as $q) {
    printf("  %-14s %12s  %2d dona\n", $q["nom"],
           number_format($q["narx"] / 100, 0, ",", " "), $q["soni"]);
}

// 3. Bitta qiymat olish
echo "\n";
$stmt = $pdo->prepare("SELECT COUNT(*) FROM mahsulotlar WHERE turkum = ?");
$stmt->execute(["monitor"]);
echo "Monitorlar soni: " . $stmt->fetchColumn() . "\n";

// 4. Bitta qator olish
$stmt = $pdo->prepare("SELECT * FROM mahsulotlar WHERE id = ?");
$stmt->execute([1]);
$bitta = $stmt->fetch();
echo "id=1: " . ($bitta === false ? "topilmadi" : $bitta["nom"]) . "\n";

$stmt->execute([999]);
$yoq = $stmt->fetch();
echo "id=999: " . ($yoq === false ? "topilmadi" : $yoq["nom"]) . "\n";

$pdo = bazaYarat();

$hujum = "' OR '1'='1";

// 1. XAVFLI usul - satrni birlashtirish
$xavfliSql = "SELECT nom FROM mahsulotlar WHERE nom = '" . $hujum . "'";
echo "Xavfli so'rov:\n  {$xavfliSql}\n";

$natija = $pdo->query($xavfliSql)->fetchAll();
echo "  Qaytgan qatorlar: " . count($natija) . " ta  <- HAMMASI!\n";

// 2. XAVFSIZ usul - tayyorlangan so'rov
echo "\nXavfsiz so'rov:\n  SELECT nom FROM mahsulotlar WHERE nom = ?\n";
$stmt = $pdo->prepare("SELECT nom FROM mahsulotlar WHERE nom = ?");
$stmt->execute([$hujum]);
$natija = $stmt->fetchAll();
echo "  Qaytgan qatorlar: " . count($natija) . " ta  <- to'g'ri\n";
echo "  (chunki bunday NOMLI mahsulot yo'q)\n";

echo "\n";
echo "Farq shundaki, ikkinchi holatda \"' OR '1'='1\" - bu\n";
echo "SQL buyrug'i emas, shunchaki QIDIRILAYOTGAN MATN.\n";

$pdo = bazaYarat();

// INSERT va oxirgi id
$stmt = $pdo->prepare(
    "INSERT INTO mahsulotlar (nom, narx, soni, turkum) VALUES (:nom, :narx, :soni, :turkum)"
);
$stmt->execute([
    "nom" => "Veb-kamera", "narx" => 45000000, "soni" => 8, "turkum" => "aksessuar",
]);
$yangiId = (int) $pdo->lastInsertId();
echo "Qo'shildi, id = {$yangiId}\n";

// UPDATE va ta'sirlangan qatorlar
$stmt = $pdo->prepare("UPDATE mahsulotlar SET narx = narx * 9 / 10 WHERE turkum = ?");
$stmt->execute(["monitor"]);
echo "Chegirma qo'llandi: " . $stmt->rowCount() . " qatorga\n";

// DELETE
$stmt = $pdo->prepare("DELETE FROM mahsulotlar WHERE soni = 0");
$stmt->execute();
echo "O'chirildi: " . $stmt->rowCount() . " qator\n";

echo "\n";
// TRANZAKSIYA - yo hammasi, yo hech nima
echo "Tranzaksiya sinovi (ombordan ikkita mahsulot ko'chirish):\n";

try {
    $pdo->beginTransaction();

    $kamaytir = $pdo->prepare("UPDATE mahsulotlar SET soni = soni - ? WHERE id = ?");
    $kamaytir->execute([5, 1]);
    echo "  1-qadam: 5 dona ayirildi\n";

    // Ikkinchi qadamda ataylab xato keltiramiz
    $qoldiq = $pdo->query("SELECT soni FROM mahsulotlar WHERE id = 1")->fetchColumn();
    if ($qoldiq < 10) {
        throw new RuntimeException("Omborda yetarli emas (qoldi: {$qoldiq})");
    }

    $pdo->commit();
    echo "  Tasdiqlandi\n";
} catch (Throwable $x) {
    $pdo->rollBack();
    echo "  XATO: " . $x->getMessage() . "\n";
    echo "  Orqaga qaytarildi\n";
}

$qoldiq = $pdo->query("SELECT soni FROM mahsulotlar WHERE id = 1")->fetchColumn();
echo "\nYakuniy qoldiq: {$qoldiq} (o'zgarmadi - tranzaksiya bekor qilindi)\n";

$pdo = bazaYarat();

final class Mahsulot
{
    public function __construct(
        public readonly int $id,
        public readonly string $nom,
        public readonly int $narx,
        public readonly int $soni,
        public readonly string $turkum,
    ) {}

    public function narxMatni(): string
    {
        return number_format($this->narx / 100, 0, ",", " ") . " so'm";
    }

    public function omborda(): bool
    {
        return $this->soni > 0;
    }
}

$stmt = $pdo->query("SELECT id, nom, narx, soni, turkum FROM mahsulotlar ORDER BY narx");

$mahsulotlar = [];
foreach ($stmt as $q) {
    $mahsulotlar[] = new Mahsulot(
        (int) $q["id"],
        $q["nom"],
        (int) $q["narx"],
        (int) $q["soni"],
        $q["turkum"],
    );
}

printf("%-14s %14s %8s %s\n", "NOM", "NARX", "SONI", "HOLAT");
echo str_repeat("-", 50) . "\n";
foreach ($mahsulotlar as $m) {
    printf("%-14s %14s %8d %s\n", $m->nom, $m->narxMatni(), $m->soni,
           $m->omborda() ? "mavjud" : "tugagan");
}

echo "\n";
$jami = array_sum(array_map(
    static fn(Mahsulot $m): int => $m->narx * $m->soni,
    $mahsulotlar
));
echo "Ombor qiymati: " . number_format($jami / 100, 0, ",", " ") . " so'm\n";
echo "Obyektlar soni: " . count($mahsulotlar) . "\n";