MySQLi — SELECT ও ডেটা পড়া

Simple SELECT

<?php
$conn = new mysqli("localhost", "root", "", "shop_db");
$conn->set_charset("utf8mb4");

$result = $conn->query("SELECT id, name, email FROM users WHERE status='active'");

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: {$row['id']}, Name: {$row['name']}\n";
    }
}

$result->free();
$conn->close();
?>

Fetch Methods

// fetch_assoc() — associative ⭐
$row = $result->fetch_assoc();
echo $row["name"];

// fetch_row() — numeric
$row = $result->fetch_row();
echo $row[1];

// fetch_object()
$row = $result->fetch_object();
echo $row->name;

// fetch_all() — সব একসাথে ⭐
$all = $result->fetch_all(MYSQLI_ASSOC);

সব Row Loop

$result = $conn->query("SELECT * FROM products");
$products = $result->fetch_all(MYSQLI_ASSOC);

foreach ($products as $p) {
    echo $p["name"] . " — ৳" . $p["price"] . "\n";
}

Row Count

echo $result->num_rows;

// COUNT query দিয়ে
$r = $conn->query("SELECT COUNT(*) AS t FROM users");
echo $r->fetch_assoc()["t"];

Error Check

$result = $conn->query($sql);
if (!$result) die("Query ভুল: " . $conn->error);

⚠️ বিপদ — সরাসরি User Input

// ❌ SQL Injection!
$id = $_GET["id"];  // "1 OR 1=1"
$sql = "SELECT * FROM users WHERE id=$id";
// Hacker সব user দেখবে!

সমাধান: Prepared Statements (পরবর্তী টপিক)।

Escape Helper (অপ্রিয়, তবে বিদ্যমান)

$name = $conn->real_escape_string($_POST["name"]);
$sql = "SELECT * FROM users WHERE name='$name'";

সম্পূর্ণ উদাহরণ

<?php
$conn = new mysqli("localhost", "root", "", "shop_db");
if ($conn->connect_error) die($conn->connect_error);
$conn->set_charset("utf8mb4");

$result = $conn->query("
    SELECT u.id, u.name, COUNT(o.id) AS orders
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name
    ORDER BY orders DESC
    LIMIT 10
");

$users = $result->fetch_all(MYSQLI_ASSOC);
echo json_encode($users, JSON_UNESCAPED_UNICODE);
?>