PHP MySQL & PDO
Complete Guide in Hindi

<?php
// Config — separate file में रखो
$host = "localhost";
$dbname = "myapp";
$user = "root";
$pass = "password";
$charset= "utf8mb4";

// DSN — Data Source Name
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

// PDO Options
$options = [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Exceptions throw
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Assoc array
  PDO::ATTR_EMULATE_PREPARES => false, // Real prepares
];

try {
  $pdo = new PDO($dsn, $user, $pass, $options);
  echo "✅ Database connected!";
} catch (PDOException $e) {
  // Production में यह log करो, user को मत दिखाओ
  error_log("DB Error: " . $e->getMessage());
  die("Database connection failed");
}
?>

<?php
// db.php — एक बार बनाओ, हर जगह include करो
function getDB(): PDO {
  static $pdo = null; // Singleton — एक ही connection
  if ($pdo === null) {
    $pdo = new PDO(
      "mysql:host=localhost;dbname=myapp;charset=utf8mb4",
      "root", "password",
      [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
       PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
    );
  }
  return $pdo;
}

// Use करना
$pdo = getDB();
?>

<?php
$pdo = getDB();

// Table: users (id, naam, email, password, created_at)

// ✅ Prepared Statement — SQL Injection safe
$sql = "INSERT INTO users (naam, email, password) VALUES (:naam, :email, :password)";
$stmt = $pdo->prepare($sql);

$stmt->execute([
  ":naam" => "Rahul Kumar",
  ":email" => "rahul@gmail.com",
  ":password" => password_hash("mypass123", PASSWORD_BCRYPT),
]);

// Last inserted ID
$lastId = $pdo->lastInsertId();
echo "New user ID: $lastId";

// Rows affected check
echo $stmt->rowCount() . " row inserted";

// Multiple rows insert — loop
$users = [
  ["Priya", "priya@gmail.com", "pass1"],
  ["Amit", "amit@gmail.com", "pass2"],
];
$stmt = $pdo->prepare("INSERT INTO users (naam, email, password) VALUES (?, ?, ?)");
foreach ($users as [$naam, $email, $pass]) {
  $stmt->execute([$naam, $email, password_hash($pass, PASSWORD_BCRYPT)]);
}
?>

<?php
$pdo = getDB();

// All users fetch
$stmt = $pdo->query("SELECT * FROM users ORDER BY naam ASC");
$users = $stmt->fetchAll(); // All rows at once
foreach ($users as $user) {
  echo $user["naam"] . " — " . $user["email"] . "\n";
}

// Single user by ID — Prepared Statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([":id" => 1]);
$user = $stmt->fetch(); // One row
if ($user) {
  echo "Found: {$user['naam']}";
} else {
  echo "User not found";
}

// Search with LIKE
$search = "%rahul%";
$stmt = $pdo->prepare("SELECT * FROM users WHERE naam LIKE :search OR email LIKE :search");
$stmt->execute([":search" => $search]);
$results = $stmt->fetchAll();

// COUNT — total rows
$count = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
echo "Total users: $count";

// Pagination
$page = 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit OFFSET :offset");
$stmt->bindValue(":limit", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$pageUsers = $stmt->fetchAll();
?>

<?php
$pdo = getDB();

// Single field update
$stmt = $pdo->prepare("UPDATE users SET naam = :naam WHERE id = :id");
$stmt->execute([":naam" => "Rahul Kumar Sharma", ":id" => 1]);
echo $stmt->rowCount() . " row updated";

// Multiple fields update
$stmt = $pdo->prepare("
  UPDATE users
  SET naam = :naam, email = :email, updated_at = NOW()
  WHERE id = :id
");
$stmt->execute([
  ":naam" => "Rahul Sharma",
  ":email" => "rahul@example.com",
  ":id" => 1,
]);

// Check — कुछ update हुआ?
if ($stmt->rowCount() === 0) {
  echo "कोई change नहीं हुआ";
}

// Password update
$stmt = $pdo->prepare("UPDATE users SET password = :pass WHERE id = :id");
$stmt->execute([
  ":pass" => password_hash("newpassword", PASSWORD_BCRYPT),
  ":id" => 1,
]);
?>

<?php
$pdo = getDB();

// Delete by ID
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([":id" => 5]);

if ($stmt->rowCount() > 0) {
  echo "✅ User deleted";
} else {
  echo "User not found";
}

// Soft delete — actually delete नहीं, flag set करो
$stmt = $pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = :id");
$stmt->execute([":id" => 5]);

// Soft deleted users को queries से exclude करो
$stmt = $pdo->query("SELECT * FROM users WHERE deleted_at IS NULL");
?>

<?php
// Named Placeholders — :naam, :email (readable)
$stmt = $pdo->prepare("SELECT * FROM users WHERE naam = :naam AND active = :active");
$stmt->execute([":naam" => "Rahul", ":active" => 1]);

// Positional Placeholders — ? (shorter)
$stmt = $pdo->prepare("SELECT * FROM users WHERE naam = ? AND active = ?");
$stmt->execute(["Rahul", 1]);

// bindParam — type specify करो
$naam = "Rahul";
$active = 1;
$stmt = $pdo->prepare("SELECT * FROM users WHERE naam = :naam AND active = :active");
$stmt->bindParam(":naam", $naam, PDO::PARAM_STR);
$stmt->bindParam(":active", $active, PDO::PARAM_INT);
$stmt->execute();

// Reuse — same statement, different data
$insertStmt = $pdo->prepare("INSERT INTO logs (message, level) VALUES (?, ?)");
$insertStmt->execute(["User logged in", "info"]);
$insertStmt->execute(["Payment failed", "error"]);
$insertStmt->execute(["Order placed", "info"]);
?>

<?php
$stmt = $pdo->query("SELECT id, naam, email FROM users");

// FETCH_ASSOC — Associative array (most common)
$user = $stmt->fetch(PDO::FETCH_ASSOC);
echo $user["naam"]; // Rahul

// FETCH_OBJ — stdClass object
$user = $stmt->fetch(PDO::FETCH_OBJ);
echo $user->naam; // Rahul

// FETCH_CLASS — Custom class में map
class User {
  public int $id;
  public string $naam;
  public string $email;
  public function getDisplayNaam(): string { return ucwords($this->naam); }
}
$stmt->setFetchMode(PDO::FETCH_CLASS, "User");
$user = $stmt->fetch();
echo $user->getDisplayNaam();

// fetchAll — All rows
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// fetchAll key-indexed
$byId = $stmt->fetchAll(PDO::FETCH_UNIQUE); // first col as key

// fetchColumn — single column
$count = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$emails= $pdo->query("SELECT email FROM users")->fetchAll(PDO::FETCH_COLUMN);
?>

<?php
function transferMoney(PDO $pdo, int $fromId, int $toId, float $amount): bool {
  try {
    $pdo->beginTransaction();

    // Step 1: Balance check करो
    $stmt = $pdo->prepare("SELECT balance FROM accounts WHERE id = ? FOR UPDATE");
    $stmt->execute([$fromId]);
    $balance = $stmt->fetchColumn();

    if ($balance < $amount) {
      throw new Exception("Insufficient balance");
    }

    // Step 2: Sender से deduct
    $deduct = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $deduct->execute([$amount, $fromId]);

    // Step 3: Receiver को add
    $credit = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $credit->execute([$amount, $toId]);

    // Step 4: Transaction log
    $log = $pdo->prepare("INSERT INTO transactions (from_id, to_id, amount) VALUES (?,?,?)");
    $log->execute([$fromId, $toId, $amount]);

    // सब OK — commit करो
    $pdo->commit();
    return true;

  } catch (Exception $e) {
    // कुछ गलत हुआ — सब undo करो
    $pdo->rollBack();
    error_log("Transfer failed: " . $e->getMessage());
    return false;
  }
}

$success = transferMoney($pdo, 1, 2, 500.00);
echo $success ? "✅ Transfer complete" : "❌ Transfer failed";
?>

<?php
// Register — new user बनाओ
function registerUser(PDO $pdo, string $naam, string $email, string $password): array {
  // Email unique है?
  $check = $pdo->prepare("SELECT id FROM users WHERE email = ?");
  $check->execute([strtolower(trim($email))]);
  if ($check->fetch()) {
    return ["success" => false, "error" => "Email already registered"];
  }

  // Insert करो
  $stmt = $pdo->prepare("INSERT INTO users (naam, email, password) VALUES (?, ?, ?)");
  $stmt->execute([
    ucwords(strtolower(trim($naam))),
    strtolower(trim($email)),
    password_hash($password, PASSWORD_BCRYPT),
  ]);

  return ["success" => true, "id" => $pdo->lastInsertId()];
}

// Login — verify करो
function loginUser(PDO $pdo, string $email, string $password): array|false {
  $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ? AND deleted_at IS NULL");
  $stmt->execute([strtolower(trim($email))]);
  $user = $stmt->fetch();

  if (!$user || !password_verify($password, $user["password"])) {
    return false; // User not found या wrong password
  }

  // Password rehash — अगर bcrypt algorithm update हुई
  if (password_needs_rehash($user["password"], PASSWORD_BCRYPT)) {
    $newHash = password_hash($password, PASSWORD_BCRYPT);
    $pdo->prepare("UPDATE users SET password = ? WHERE id = ?")->execute([$newHash, $user["id"]]);
  }

  // Password return मत करो
  unset($user["password"]);
  return $user;
}

// Usage
$pdo = getDB();
$reg = registerUser($pdo, "Rahul Kumar", "rahul@g.com", "pass123");
$user= loginUser($pdo, "rahul@g.com", "pass123");
if ($user) echo "Welcome, {$user['naam']}!";
?>