PHP va ma'lumotlar bazasi (PDO)

Faylga saqlash kichik loyiha uchun yetadi, lekin minglab yozuv, qidiruv va bir vaqtda ishlaydigan foydalanuvchilar kerak bo’lganda — ma’lumotlar bazasi kerak. PHP unga PDO orqali ulanadi.

PDO nima

Ulanish

<?php
$host = "localhost";
$baza = "learncode";
$foydalanuvchi = "root";
$parol = "";

$dsn = "mysql:host=$host;dbname=$baza;charset=utf8mb4";

$sozlama = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $foydalanuvchi, $parol, $sozlama);
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit("Bazaga ulanib bo'lmadi");
}

Uchala sozlama ham muhim:

charset=utf8mb4 — o’zbek harflari va emoji to’g’ri saqlanishi uchun.

SQL injection — asosiy xavf

Tayyorlangan so’rov

Ikki uslub bor — nomlangan va tartibli:

<?php
// Nomlangan (o'qishga qulay)
$s = $pdo->prepare("SELECT * FROM talabalar WHERE guruh = :guruh AND ball >= :ball");
$s->execute(["guruh" => "IT-21", "ball" => 60]);
$talabalar = $s->fetchAll();

// Tartibli (qisqa)
$s = $pdo->prepare("SELECT * FROM talabalar WHERE guruh = ? AND ball >= ?");
$s->execute(["IT-21", 60]);
$talabalar = $s->fetchAll();

execute() ga berilgan qiymatlar hech qachon SQL sifatida talqin qilinmaydi — injection mumkin emas.

Ma’lumot o’qish

<?php
// Bitta yozuv
$s = $pdo->prepare("SELECT * FROM talabalar WHERE id = ?");
$s->execute([$id]);
$talaba = $s->fetch();

if ($talaba === false) {
    exit("Talaba topilmadi");
}
echo $talaba["ism"];

// Hammasi
$s = $pdo->query("SELECT * FROM talabalar ORDER BY ball DESC");
foreach ($s as $t) {
    echo $t["ism"], " — ", $t["ball"], "\n";
}

// Faqat bitta qiymat
$soni = $pdo->query("SELECT COUNT(*) FROM talabalar")->fetchColumn();
echo "Jami: $soni ta";

Yozish, yangilash, o’chirish

<?php
// INSERT
$s = $pdo->prepare(
    "INSERT INTO talabalar (ism, guruh, ball) VALUES (:ism, :guruh, :ball)"
);
$s->execute([
    "ism"   => $ism,
    "guruh" => $guruh,
    "ball"  => $ball,
]);

$yangiId = $pdo->lastInsertId();
echo "Qo'shildi, id: $yangiId";

// UPDATE
$s = $pdo->prepare("UPDATE talabalar SET ball = :ball WHERE id = :id");
$s->execute(["ball" => 90, "id" => 5]);
echo $s->rowCount(), " ta yozuv yangilandi";

// DELETE
$s = $pdo->prepare("DELETE FROM talabalar WHERE id = ?");
$s->execute([$id]);

lastInsertId() — yangi yozuvning AUTO_INCREMENT id’sini beradi.

Tranzaksiya

Bir necha amal birgalikda bajarilishi kerak bo’lsa:

<?php
try {
    $pdo->beginTransaction();

    $s = $pdo->prepare("UPDATE hisoblar SET balans = balans - ? WHERE id = ?");
    $s->execute([100000, 1]);

    $s = $pdo->prepare("UPDATE hisoblar SET balans = balans + ? WHERE id = ?");
    $s->execute([100000, 2]);

    $pdo->commit();
    echo "Pul o'tkazildi";
} catch (PDOException $e) {
    $pdo->rollBack();      // hamma o'zgarish bekor qilinadi
    error_log($e->getMessage());
    echo "O'tkazma bajarilmadi";
}

Pul o’tkazmasida ikkinchi amal xato bersa, birinchisi ham bekor qilinishi shart — aks holda pul yo’qoladi. Tranzaksiya buni kafolatlaydi.

Amaliy klass

Loyihada PDO’ni to’g’ridan-to’g’ri ishlatish o’rniga uni o’rash qulay:

<?php
class TalabaOmbori
{
    public function __construct(private PDO $pdo) {}

    public function topId(int $id): ?array
    {
        $s = $this->pdo->prepare("SELECT * FROM talabalar WHERE id = ?");
        $s->execute([$id]);
        return $s->fetch() ?: null;
    }

    public function guruhBoyicha(string $guruh): array
    {
        $s = $this->pdo->prepare(
            "SELECT * FROM talabalar WHERE guruh = ? ORDER BY ball DESC"
        );
        $s->execute([$guruh]);
        return $s->fetchAll();
    }

    public function qosh(string $ism, string $guruh, int $ball): int
    {
        $s = $this->pdo->prepare(
            "INSERT INTO talabalar (ism, guruh, ball) VALUES (?, ?, ?)"
        );
        $s->execute([$ism, $guruh, $ball]);
        return (int) $this->pdo->lastInsertId();
    }

    public function ochir(int $id): bool
    {
        $s = $this->pdo->prepare("DELETE FROM talabalar WHERE id = ?");
        $s->execute([$id]);
        return $s->rowCount() > 0;
    }
}

// Ishlatish
$ombor = new TalabaOmbori($pdo);
$talaba = $ombor->topId(5);
$guruh = $ombor->guruhBoyicha("IT-21");

Ustun nomini o’zgaruvchi qilib bo’lmaydi

Sahifalash

<?php
$sahifa = max(1, (int) ($_GET["sahifa"] ?? 1));
$sahifada = 10;
$boshlanish = ($sahifa - 1) * $sahifada;

$s = $pdo->prepare(
    "SELECT * FROM talabalar ORDER BY id DESC LIMIT :limit OFFSET :offset"
);
$s->bindValue(":limit", $sahifada, PDO::PARAM_INT);
$s->bindValue(":offset", $boshlanish, PDO::PARAM_INT);
$s->execute();

$talabalar = $s->fetchAll();
$jami = (int) $pdo->query("SELECT COUNT(*) FROM talabalar")->fetchColumn();
$sahifalar = (int) ceil($jami / $sahifada);

LIMIT va OFFSET uchun bindValue bilan PDO::PARAM_INT ko’rsatiladi — aks holda ular matn sifatida yuboriladi va MySQL xato beradi.

Amaliy mashq

O’z kompyuteringizda MySQL o’rnatib bajaring:

1. Baza va talabalar jadvalini yarating;
2. PDO bilan ulaning va uchala sozlamani qo’ying;
3. prepare + execute bilan yozuv qo’shing;
4. fetchAll bilan hammasini o’qing;
5. UPDATE va rowCount ni sinang;
6. TalabaOmbori kabi klass yozing.

Qisqacha xulosa

• PDO — turli bazalar bilan bir xil kod orqali ishlash qatlami.
• Ulanishda: ERRMODE_EXCEPTION, FETCH_ASSOC, EMULATE_PREPARES => false.
• Qiymat bo’lgan har so’rov uchun prepare + execute — SQL injection’dan yagona ishonchli himoya.
• fetch bitta qator, fetchAll hammasi, fetchColumn bitta qiymat.
• lastInsertId() yangi yozuv id’sini, rowCount() ta’sirlangan sonni beradi.
• Bir necha bog’liq amal uchun tranzaksiya (commit / rollBack).
• Ustun nomi tayyorlangan so’rov bilan himoyalanmaydi — ruxsat etilgan ro’yxat ishlating.

Keyingi darsda JSON va API — boshqa xizmatlar bilan ma’lumot almashish.