آموزش برنامه نویسی PHP-جلسه بیست و چهارم

۱. آشنایی با شناسه رکوردها در MySQL

هنگام ایجاد یک جدول در MySQL معمولاً برای هر رکورد یک شناسه اختصاصی در نظر می‌گیریم. این شناسه کمک می‌کند هر سطر از اطلاعات را به‌صورت منحصربه‌فرد شناسایی کنیم.

برای مثال، فرض کنید جدولی با نام users داریم که اطلاعات کاربران را ذخیره می‌کند. ساختار این جدول می‌تواند به شکل زیر باشد:

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    email VARCHAR(150) NOT NULL UNIQUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);
        

در این جدول، ستون id به‌عنوان کلید اصلی تعریف شده است. همچنین با استفاده از AUTO_INCREMENT مقدار این ستون به‌صورت خودکار برای هر رکورد جدید افزایش پیدا می‌کند.

برای مثال، پس از ثبت سه کاربر، اطلاعات جدول ممکن است به شکل زیر باشد:

همان‌طور که مشاهده می‌کنید، هر کاربر یک شناسه اختصاصی دارد. این شناسه در عملیات مختلفی مانند نمایش اطلاعات، ویرایش کاربران، حذف رکوردها و ایجاد ارتباط بین جدول‌ها کاربرد دارد.

۲. دریافت شناسه آخرین رکورد ثبت‌شده در MySQL (Get Last Inserted ID)

یکی از قابلیت‌های کاربردی MySQL، امکان دریافت شناسه آخرین رکوردی است که در یک جدول دارای ستون خودافزاینده ثبت شده است.

فرض کنید یک کاربر جدید در وب‌سایت ثبت‌نام می‌کند. پس از ذخیره اطلاعات او، ممکن است بخواهیم شناسه اختصاصی کاربر را دریافت کنیم تا بتوانیم اطلاعات تکمیلی او را در جدول دیگری ذخیره نماییم.

برای مثال، هنگام ثبت سفارش در یک فروشگاه اینترنتی ابتدا اطلاعات سفارش در جدول orders ذخیره می‌شود. سپس شناسه سفارش جدید را دریافت می‌کنیم و اطلاعات محصولات مربوط به آن سفارش را در جدول order_items ثبت می‌کنیم.

۲.۱. دریافت آخرین شناسه با استفاده از PDO

در PHP و PDO برای دریافت شناسه آخرین رکورد ثبت‌شده از متد lastInsertId استفاده می‌کنیم.

ابتدا اتصال به دیتابیس را برقرار می‌کنیم:

<?php

$host = "localhost";
$dbname = "php_tutorial";
$username = "root";
$password = "";

try {

    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    );

} catch (PDOException $e) {

    error_log($e->getMessage());

    exit("خطا در اتصال به دیتابیس.");

}

?>
        

حالا می‌توانیم یک کاربر جدید ثبت کرده و شناسه او را دریافت کنیم:

<?php

require_once "db.php";

$sql = "
    INSERT INTO users (name, email)
    VALUES (:name, :email)
";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ":name" => "Ali Ahmadi",
    ":email" => "ali@example.com"
]);

$lastId = $pdo->lastInsertId();

echo "شناسه آخرین کاربر ثبت‌شده: " . $lastId;

?>
        

در این مثال ابتدا اطلاعات کاربر با استفاده از دستور INSERT ثبت می‌شود. سپس با اجرای متد lastInsertId شناسه اختصاص‌یافته به رکورد جدید دریافت خواهد شد.

فرض کنید شناسه کاربر جدید برابر با ۱۵ باشد. خروجی برنامه به شکل زیر خواهد بود:

شناسه آخرین کاربر ثبت‌شده: 15
        

نکته مهم این است که lastInsertId باید روی همان اتصال PDO که عملیات INSERT را انجام داده است اجرا شود. این متد برای دریافت شناسه تولیدشده توسط ستون AUTO_INCREMENT در همان اتصال کاربرد دارد.

۲.۲. دریافت آخرین شناسه با استفاده از MySQLi

اگر در پروژه خود از MySQLi استفاده می‌کنید، می‌توانید از ویژگی insert_id برای دریافت شناسه آخرین رکورد ثبت‌شده استفاده کنید.

<?php

$conn = new mysqli(
    "localhost",
    "root",
    "",
    "php_tutorial"
);

if ($conn->connect_error) {
    die("خطا در اتصال به دیتابیس.");
}

$conn->set_charset("utf8mb4");

$name = "Sara Mohammadi";

$email = "sara@example.com";

$sql = "
    INSERT INTO users (name, email)
    VALUES (?, ?)
";

$stmt = $conn->prepare($sql);

$stmt->bind_param(
    "ss",
    $name,
    $email
);

if ($stmt->execute()) {

    $lastId = $conn->insert_id;

    echo "شناسه کاربر جدید: " . $lastId;

} else {

    echo "خطا در ثبت اطلاعات.";

}

$stmt->close();

$conn->close();

?>
        

در این کد از ویژگی insert_id شیء اتصال MySQLi استفاده کرده‌ایم. پس از اجرای موفق دستور INSERT، شناسه تولیدشده توسط MySQL دریافت می‌شود.

۲.۳. تفاوت lastInsertId در PDO و insert_id در MySQLi

هر دو روش برای دریافت شناسه رکوردهای خودافزاینده کاربرد دارند. باید توجه داشت که این شناسه مربوط به اتصال فعلی است و لزوماً بزرگ‌ترین شناسه موجود در کل جدول نیست.

۲.۴. چرا استفاده از MAX(id) مناسب نیست؟

برخی برنامه‌نویسان برای دریافت شناسه آخرین رکورد از دستور زیر استفاده می‌کنند:

SELECT MAX(id) FROM users;
        

این دستور بزرگ‌ترین شناسه موجود در جدول را برمی‌گرداند، اما لزوماً شناسه رکوردی نیست که درخواست فعلی شما ثبت کرده است.

در برنامه‌هایی که چند کاربر به‌صورت هم‌زمان اطلاعات ثبت می‌کنند، ممکن است بین دو درخواست رکورد دیگری ثبت شود. به همین دلیل استفاده از lastInsertId در PDO یا insert_id در MySQLi روش مناسب‌تری برای دریافت شناسه رکورد ایجادشده توسط همان اتصال است.

۳. ثبت چندین رکورد به‌صورت هم‌زمان در MySQL (Insert Multiple)

در بسیاری از پروژه‌ها نیاز داریم چندین رکورد را به‌صورت هم‌زمان در یک جدول ذخیره کنیم. برای مثال، ممکن است بخواهیم چند محصول را به فروشگاه اضافه کنیم یا اطلاعات تعدادی از کاربران را به‌صورت گروهی وارد دیتابیس نماییم.

یکی از روش‌های مناسب برای این کار استفاده از قابلیت Multiple INSERT در MySQL است.

۳.۱. ثبت چند رکورد با یک دستور INSERT

ساختار کلی ثبت چندین رکورد در MySQL به شکل زیر است:

INSERT INTO table_name (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6);
        

همان‌طور که مشاهده می‌کنید، مقادیر مربوط به هر رکورد داخل یک پرانتز قرار می‌گیرند و رکوردها با کاما از یکدیگر جدا می‌شوند.

برای مثال، فرض کنید جدولی با نام products داریم:

CREATE TABLE products (

    id INT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(150) NOT NULL,

    price DECIMAL(12,2) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);
        

حالا می‌خواهیم سه محصول جدید را با یک دستور در دیتابیس ثبت کنیم:

INSERT INTO products (title, price)
VALUES
    ('Laptop', 45000000),
    ('Mobile', 25000000),
    ('Keyboard', 1500000);
        

با اجرای این دستور، سه محصول به‌صورت هم‌زمان به جدول اضافه می‌شوند.

۳.۲. ثبت چند رکورد با استفاده از PHP و PDO

برای ثبت چندین رکورد از طریق PHP می‌توانیم از یک دستور INSERT چندردیفی و Prepared Statement استفاده کنیم.

در مثال زیر قصد داریم چند محصول را با استفاده از PDO در جدول products ذخیره کنیم:

<?php

require_once "db.php";

$products = [
    [
        "title" => "Laptop",
        "price" => 45000000
    ],
    [
        "title" => "Mobile",
        "price" => 25000000
    ],
    [
        "title" => "Keyboard",
        "price" => 1500000
    ]
];

$sql = "
    INSERT INTO products (title, price)
    VALUES (?, ?), (?, ?), (?, ?)
";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    $products[0]["title"],
    $products[0]["price"],

    $products[1]["title"],
    $products[1]["price"],

    $products[2]["title"],
    $products[2]["price"]
]);

echo "تمام محصولات با موفقیت ثبت شدند.";

?>
        

در این مثال شش پارامتر برای سه رکورد تعریف کرده‌ایم. هر رکورد شامل دو مقدار نام محصول و قیمت است.

مزیت این روش آن است که چندین اطلاعات با یک دستور SQL در دیتابیس ذخیره می‌شوند و نیازی به اجرای چند دستور INSERT جداگانه نداریم.

۳.۳. ثبت تعداد متغیر رکوردها با PDO

در مثال قبلی تعداد محصولات از قبل مشخص بود. اما در پروژه‌های واقعی ممکن است تعداد رکوردهایی که باید ثبت شوند متغیر باشد.

برای حل این مشکل می‌توانیم بر اساس تعداد اطلاعات دریافتی، بخش VALUES و پارامترهای دستور SQL را به‌صورت پویا ایجاد کنیم.

<?php

require_once "db.php";

$products = [
    ["title" => "Laptop", "price" => 45000000],
    ["title" => "Mobile", "price" => 25000000],
    ["title" => "Keyboard", "price" => 1500000],
    ["title" => "Mouse", "price" => 800000]
];

if (empty($products)) {
    exit("هیچ محصولی برای ثبت وجود ندارد.");
}

$placeholders = [];

$values = [];

foreach ($products as $product) {

    $placeholders[] = "(?, ?)";

    $values[] = $product["title"];

    $values[] = $product["price"];

}

$sql = "
    INSERT INTO products (title, price)
    VALUES " . implode(", ", $placeholders);

$stmt = $pdo->prepare($sql);

$stmt->execute($values);

echo count($products) . " محصول با موفقیت ثبت شد.";

?>
        

در این روش تعداد رکوردها محدود به یک عدد مشخص نیست. برنامه با استفاده از حلقه foreach اطلاعات محصولات را بررسی می‌کند و برای هر محصول دو علامت سؤال در دستور SQL قرار می‌دهد.

تابع implode نیز تمام بخش‌های VALUES را به یک رشته تبدیل می‌کند. در نهایت مقادیر واقعی با استفاده از execute به دستور آماده‌شده ارسال می‌شوند.

نکته امنیتی: نام جدول و ستون‌ها در این مثال ثابت هستند و فقط مقادیر داده‌ها به‌صورت پارامتر ارسال می‌شوند. پارامترهای Prepared Statement برای جایگزینی نام جدول یا نام ستون کاربرد ندارند.

۳.۴. ثبت چندین رکورد با استفاده از MySQLi

با MySQLi نیز می‌توانیم چندین رکورد را با یک دستور Prepared Statement ثبت کنیم.

<?php

$conn = new mysqli(
    "localhost",
    "root",
    "",
    "php_tutorial"
);

if ($conn->connect_error) {
    die("خطا در اتصال به دیتابیس.");
}

$conn->set_charset("utf8mb4");

$products = [
    ["Laptop", 45000000],
    ["Mobile", 25000000],
    ["Keyboard", 1500000]
];

$sql = "
    INSERT INTO products (title, price)
    VALUES (?, ?), (?, ?), (?, ?)
";

$stmt = $conn->prepare($sql);

$stmt->bind_param(
    "sdsdsd",
    $products[0][0],
    $products[0][1],
    $products[1][0],
    $products[1][1],
    $products[2][0],
    $products[2][1]
);

if ($stmt->execute()) {

    echo "محصولات با موفقیت ثبت شدند.";

} else {

    echo "خطا در ثبت محصولات.";

}

$stmt->close();

$conn->close();

?>
        

در متد bind_param نوع داده هر پارامتر مشخص می‌شود:

• s: برای داده‌های رشته‌ای.
• i: برای اعداد صحیح.
• d: برای اعداد اعشاری.
• b: برای داده‌های باینری.

در این مثال از رشته sdsdsd استفاده کرده‌ایم؛ زیرا نام محصولات رشته‌ای و قیمت‌ها عددی هستند.

۴. آشنایی با Prepared Statement در MySQL

یکی از مهم‌ترین موضوعات در برنامه‌نویسی امن PHP، استفاده از Prepared Statement است.

زمانی که اطلاعات از طریق فرم دریافت می‌شوند و قرار است در دیتابیس ذخیره یا جستجو شوند، نباید مقادیر ورودی کاربران را مستقیماً با رشته SQL ترکیب کنیم.

اگر ورودی کاربران بدون جداسازی مناسب وارد دستور SQL شود، احتمال حمله‌ای به نام SQL Injection وجود دارد. این حمله می‌تواند باعث دسترسی غیرمجاز به اطلاعات یا تغییر و حذف داده‌های دیتابیس شود.

Prepared Statement روشی برای آماده‌سازی دستورات SQL و ارسال جداگانه مقادیر ورودی است. در این روش ابتدا ساختار دستور SQL مشخص می‌شود و سپس مقادیر موردنیاز به‌عنوان پارامتر به آن ارسال می‌شوند.

۴.۱. روش ناامن اجرای دستور SQL

فرض کنید می‌خواهیم یک کاربر را بر اساس ایمیل جستجو کنیم. روش زیر مناسب نیست:

<?php

$email = $_GET["email"];

$sql = "
    SELECT * FROM users
    WHERE email = '$email'
";

$result = $conn->query($sql);

?>
        

مشکل این کد آن است که مقدار ورودی کاربر مستقیماً داخل دستور SQL قرار می‌گیرد. در نتیجه اگر ورودی شامل ساختارهای مخرب SQL باشد، ممکن است رفتار دستور تغییر کند.

بنابراین باید از روش Prepared Statement استفاده کنیم.

۴.۲. اجرای دستور امن با استفاده از PDO

در PDO برای آماده‌سازی یک دستور SQL از متد prepare استفاده می‌کنیم.

<?php

require_once "db.php";

$email = "ali@example.com";

$sql = "
    SELECT id, name, email
    FROM users
    WHERE email = :email
";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ":email" => $email
]);

$user = $stmt->fetch();

if ($user) {

    echo "کاربر پیدا شد.";

} else {

    echo "کاربری با این ایمیل وجود ندارد.";

}

?>
        

در این مثال به‌جای قرار دادن مستقیم ایمیل داخل دستور SQL، از پارامتر نام‌گذاری‌شده email استفاده کرده‌ایم.

سپس مقدار واقعی با استفاده از متد execute ارسال می‌شود. PDO مقدار را به‌عنوان داده پردازش می‌کند و آن را با ساختار SQL ترکیب نمی‌کند.

۴.۳. استفاده از علامت سؤال در Prepared Statement

در PDO می‌توانیم به‌جای پارامترهای نام‌گذاری‌شده از علامت سؤال نیز استفاده کنیم.

<?php

require_once "db.php";

$email = "ali@example.com";

$sql = "
    SELECT id, name, email
    FROM users
    WHERE email = ?
";

$stmt = $pdo->prepare($sql);

$stmt->execute([$email]);

$user = $stmt->fetch();

if ($user) {

    echo htmlspecialchars(
        $user["name"],
        ENT_QUOTES,
        "UTF-8"
    );

}

?>
        

در این روش، ترتیب مقادیری که به execute ارسال می‌کنیم باید با ترتیب علامت سؤال‌ها در دستور SQL یکسان باشد.

۴.۴. استفاده از bindParam در PDO

یکی دیگر از روش‌های اتصال پارامترها در PDO استفاده از bindParam است.

<?php

require_once "db.php";

$name = "Ali Ahmadi";

$email = "ali@example.com";

$sql = "
    INSERT INTO users (name, email)
    VALUES (:name, :email)
";

$stmt = $pdo->prepare($sql);

$stmt->bindParam(":name", $name, PDO::PARAM_STR);

$stmt->bindParam(":email", $email, PDO::PARAM_STR);

$stmt->execute();

echo "اطلاعات با موفقیت ثبت شد.";

?>
        

متد bindParam متغیر را به پارامتر متصل می‌کند و مقدار آن هنگام اجرای دستور خوانده می‌شود.

همچنین متد bindValue برای اتصال مستقیم یک مقدار به پارامتر استفاده می‌شود:

<?php

require_once "db.php";

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE id = :id"
);

$stmt->bindValue(":id", 10, PDO::PARAM_INT);

$stmt->execute();

$user = $stmt->fetch();

?>
        

تفاوت اصلی این دو متد در نحوه اتصال مقدار است. bindParam متغیر را با ارجاع متصل می‌کند، اما bindValue مقدار را در همان زمان فراخوانی دریافت می‌کند.

۴.۵. استفاده از Prepared Statement با MySQLi

در MySQLi نیز می‌توان از متد prepare برای اجرای امن دستورات SQL استفاده کرد.

<?php

$conn = new mysqli(
    "localhost",
    "root",
    "",
    "php_tutorial"
);

if ($conn->connect_error) {
    die("خطا در اتصال به دیتابیس.");
}

$conn->set_charset("utf8mb4");

$email = "ali@example.com";

$sql = "
    SELECT id, name, email
    FROM users
    WHERE email = ?
";

$stmt = $conn->prepare($sql);

$stmt->bind_param("s", $email);

$stmt->execute();

$result = $stmt->get_result();

$user = $result->fetch_assoc();

if ($user) {

    echo "نام کاربر: " . htmlspecialchars(
        $user["name"],
        ENT_QUOTES,
        "UTF-8"
    );

} else {

    echo "کاربری پیدا نشد.";

}

$stmt->close();

$conn->close();

?>
        

در این مثال ابتدا دستور SQL با یک پارامتر آماده می‌شود. سپس با استفاده از bind_param مقدار ایمیل به دستور متصل شده و با execute اجرا می‌شود.

متد get_result نتیجه اجرای دستور SELECT را دریافت می‌کند. این متد در محیط‌هایی که از درایور mysqlnd پشتیبانی می‌کنند قابل استفاده است.

۵. استفاده از Prepared Statement برای عملیات مختلف SQL

Prepared Statement فقط برای ثبت اطلاعات کاربرد ندارد. از این قابلیت می‌توان برای عملیات مختلف مانند SELECT، INSERT، UPDATE و DELETE نیز استفاده کرد.

۵.۱. جستجوی اطلاعات با SELECT

<?php

require_once "db.php";

$id = 5;

$sql = "SELECT * FROM users WHERE id = :id";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ":id" => $id
]);

$user = $stmt->fetch();

if ($user) {

    echo htmlspecialchars(
        $user["name"],
        ENT_QUOTES,
        "UTF-8"
    );

}

?>
        

۵.۲. ثبت اطلاعات با INSERT

<?php

require_once "db.php";

$name = "Reza Karimi";

$email = "reza@example.com";

$sql = "
    INSERT INTO users (name, email)
    VALUES (:name, :email)
";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ":name" => $name,
    ":email" => $email
]);

echo "کاربر جدید ثبت شد.";

?>
        

۵.۳. ویرایش اطلاعات با UPDATE

<?php

require_once "db.php";

$id = 5;

$name = "Ali Rezaei";

$sql = "
    UPDATE users
    SET name = :name
    WHERE id = :id
";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ":name" => $name,
    ":id" => $id
]);

echo "اطلاعات کاربر به‌روزرسانی شد.";

?>
        

۵.۴. حذف اطلاعات با DELETE

<?php

require_once "db.php";

$id = 5;

$sql = "DELETE FROM users WHERE id = :id";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ":id" => $id
]);

echo "عملیات حذف انجام شد.";

?>
        

در تمام این مثال‌ها، مقادیر ورودی از ساختار اصلی SQL جدا شده‌اند. این موضوع باعث افزایش امنیت و خوانایی کدها می‌شود.

۶. استفاده از تراکنش برای ثبت چندین اطلاعات

گاهی اوقات ثبت چند رکورد به یکدیگر وابسته است. برای مثال، در یک فروشگاه اینترنتی ابتدا اطلاعات سفارش ثبت می‌شود و سپس محصولات مربوط به آن سفارش در جدول دیگری ذخیره می‌شوند.

در چنین شرایطی بهتر است از Transaction استفاده کنیم تا عملیات به‌صورت یکپارچه انجام شوند.

تراکنش این امکان را فراهم می‌کند که چند عملیات دیتابیس را به‌عنوان یک واحد منطقی اجرا کنیم. اگر همه عملیات موفق باشند، تغییرات ثبت می‌شوند و اگر مشکلی رخ دهد، می‌توانیم آن‌ها را بازگردانی کنیم.

مثال عملی استفاده از Transaction در PDO

<?php

require_once "db.php";

try {

    $pdo->beginTransaction();

    $sql = "
        INSERT INTO orders (customer_name, total_price)
        VALUES (:name, :price)
    ";

    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ":name" => "Ali Ahmadi",
        ":price" => 5000000
    ]);

    $orderId = $pdo->lastInsertId();

    $items = [
        ["title" => "Laptop", "price" => 4500000],
        ["title" => "Mouse", "price" => 500000]
    ];

    $itemSql = "
        INSERT INTO order_items
        (order_id, product_name, price)
        VALUES (:order_id, :name, :price)
    ";

    $itemStmt = $pdo->prepare($itemSql);

    foreach ($items as $item) {

        $itemStmt->execute([
            ":order_id" => $orderId,
            ":name" => $item["title"],
            ":price" => $item["price"]
        ]);

    }

    $pdo->commit();

    echo "سفارش با موفقیت ثبت شد.";

} catch (Throwable $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    error_log($e->getMessage());

    echo "خطا در ثبت سفارش.";

}

?>
        

در این مثال ابتدا تراکنش با beginTransaction آغاز می‌شود. سپس سفارش اصلی ثبت شده و شناسه آن دریافت می‌شود. بعد از آن اطلاعات محصولات سفارش در جدول order_items ذخیره می‌شوند.

اگر تمام عملیات با موفقیت انجام شوند، commit تغییرات را نهایی می‌کند. اما اگر خطایی رخ دهد، rollBack تمام تغییرات انجام‌شده در تراکنش را بازمی‌گرداند.

توجه داشته باشید که برای پشتیبانی از تراکنش، جدول‌های مورد استفاده باید از موتور ذخیره‌سازی مناسب مانند InnoDB استفاده کنند.

۷. تفاوت Insert Multiple و اجرای چند دستور INSERT جداگانه

برای ثبت چندین رکورد در MySQL دو روش متداول وجود دارد:

1. ثبت چند رکورد با یک دستور INSERT چندردیفی.
2. اجرای چند دستور INSERT جداگانه در یک حلقه.

در روش اول تمام رکوردها در قالب یک دستور SQL ارسال می‌شوند. اما در روش دوم برای هر رکورد یک بار دستور INSERT اجرا می‌شود.

اگر تعداد رکوردها مشخص باشد و همه آن‌ها ساختار یکسانی داشته باشند، Insert Multiple معمولاً انتخاب مناسبی است.

اما اگر لازم باشد قبل از ثبت هر رکورد عملیات خاصی انجام دهیم یا تعداد اطلاعات بسیار زیاد باشد، استفاده از حلقه و تراکنش می‌تواند گزینه مناسبی باشد.

۸. مزایای استفاده از Prepared Statement

استفاده از Prepared Statement مزایای مهمی برای توسعه‌دهندگان PHP دارد. مهم‌ترین این مزایا عبارت‌اند از:

افزایش امنیت برنامه

جدا کردن داده‌های ورودی از ساختار SQL احتمال حملات SQL Injection را کاهش می‌دهد.

خوانایی بهتر کدها

با استفاده از پارامترهای نام‌گذاری‌شده، کدهای SQL واضح‌تر و قابل‌فهم‌تر می‌شوند.

استفاده مجدد از دستور آماده‌شده

می‌توان یک دستور SQL را آماده کرد و در شرایط مناسب چندین بار با مقادیر متفاوت اجرا نمود.

مدیریت بهتر انواع داده

با مشخص کردن نوع پارامترها در PDO یا MySQLi می‌توان داده‌ها را با ساختار مناسب به دستور SQL ارسال کرد.

کاهش خطاهای برنامه‌نویسی

استفاده از پارامترها باعث می‌شود نیازی به ترکیب دستی رشته‌های ورودی با دستور SQL نداشته باشیم.

۹. محدودیت‌ها و نکات مهم در Prepared Statement

با وجود مزایای فراوان، هنگام استفاده از Prepared Statement باید به چند نکته مهم توجه کنیم.

• پارامترها برای جایگزینی مقادیر استفاده می‌شوند، نه نام جدول‌ها و ستون‌ها.
• برای مرتب‌سازی پویا با ORDER BY باید نام ستون را از یک فهرست مجاز و کنترل‌شده انتخاب کنیم.
• برای مقادیر LIMIT و OFFSET باید ورودی‌ها را به اعداد صحیح معتبر تبدیل کنیم.
• Prepared Statement جایگزین اعتبارسنجی اطلاعات ورودی نیست.
• برای نمایش امن اطلاعات در HTML همچنان باید از htmlspecialchars استفاده کنیم.
• اطلاعات محرمانه مانند رمز عبور نباید به‌صورت متن ساده در دیتابیس ذخیره شوند.

مثال مرتب‌سازی امن با ORDER BY

فرض کنید می‌خواهیم اطلاعات کاربران را بر اساس نام یا تاریخ ثبت مرتب کنیم. از آنجا که نام ستون را نمی‌توان مستقیماً با یک پارامتر معمولی جایگزین کرد، باید آن را از میان گزینه‌های مجاز انتخاب کنیم.

<?php

require_once "db.php";

$allowedColumns = [
    "name",
    "email",
    "created_at"
];

$sort = $_GET["sort"] ?? "created_at";

if (!in_array($sort, $allowedColumns, true)) {
    $sort = "created_at";
}

$sql = "
    SELECT id, name, email, created_at
    FROM users
    ORDER BY `$sort` DESC
";

$stmt = $pdo->query($sql);

$users = $stmt->fetchAll();

?>
        

در این مثال نام ستون از ورودی مستقیم کاربر وارد دستور SQL نشده است؛ بلکه ابتدا با استفاده از فهرست ستون‌های مجاز بررسی شده است.

۱۰. خطاهای رایج هنگام استفاده از این دستورات

دریافت شناسه اشتباه

اگر شناسه آخرین رکورد را از اتصال دیگری دریافت کنید یا از MAX(id) برای شناسایی رکورد فعلی استفاده کنید، ممکن است نتیجه مورد انتظار را به دست نیاورید. همیشه شناسه را از همان اتصال و پس از اجرای موفق INSERT دریافت کنید.

تعداد نادرست پارامترها

در Prepared Statement تعداد پارامترهای ارسالی باید با تعداد جایگاه‌های مشخص‌شده در دستور SQL برابر باشد. در غیر این صورت خطای اجرای دستور رخ می‌دهد.

اشتباه در ترتیب پارامترها

هنگام استفاده از علامت سؤال در PDO، ترتیب مقادیر ارسالی باید دقیقاً با ترتیب پارامترها در دستور SQL هماهنگ باشد.

اشتباه در نوع داده MySQLi

در متد bind_param باید برای هر پارامتر نوع داده مناسبی تعیین شود. استفاده از رشته نوع اشتباه می‌تواند باعث بروز مشکلاتی در پردازش مقادیر شود.

خطای Duplicate Entry

اگر مقداری تکراری در ستونی با محدودیت UNIQUE ثبت شود، MySQL اجازه ذخیره آن را نمی‌دهد. برای جلوگیری از این مشکل، اطلاعات تکراری را بررسی کنید و خطاها را به شکل مناسب مدیریت نمایید.

ثبت ناقص چند رکورد

اگر چند عملیات ثبت به یکدیگر وابسته هستند، بهتر است آن‌ها را داخل یک تراکنش قرار دهید تا در صورت بروز خطا، تغییرات ناقص در دیتابیس باقی نمانند.

۱۱. پروژه عملی: ثبت چند محصول و دریافت شناسه آن‌ها

برای درک بهتر مباحث این قسمت، یک پروژه کوچک ایجاد می‌کنیم که در آن چند محصول را به‌صورت هم‌زمان در دیتابیس ذخیره کرده و شناسه اولین و آخرین محصول ثبت‌شده را دریافت می‌کنیم.

مرحله اول: ایجاد جدول محصولات

CREATE TABLE products (

    id INT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(150) NOT NULL,

    price DECIMAL(12,2) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;
        

مرحله دوم: ثبت محصولات با PHP و PDO

<?php

require_once "db.php";

$products = [
    ["title" => "Laptop", "price" => 45000000],
    ["title" => "Mobile", "price" => 25000000],
    ["title" => "Keyboard", "price" => 1500000],
    ["title" => "Mouse", "price" => 800000]
];

if (empty($products)) {
    exit("محصولی برای ثبت وجود ندارد.");
}

$placeholders = [];

$values = [];

foreach ($products as $product) {

    $placeholders[] = "(?, ?)";

    $values[] = $product["title"];

    $values[] = $product["price"];

}

$sql = "
    INSERT INTO products (title, price)
    VALUES " . implode(", ", $placeholders);

try {

    $pdo->beginTransaction();

    $stmt = $pdo->prepare($sql);

    $stmt->execute($values);

    $lastId = (int) $pdo->lastInsertId();

    $count = count($products);

    $firstId = $lastId - $count + 1;

    $pdo->commit();

    echo "محصولات با موفقیت ثبت شدند.<br>";

    echo "شناسه اولین محصول: " . $firstId . "<br>";

    echo "شناسه آخرین محصول: " . $lastId;

} catch (Throwable $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    error_log($e->getMessage());

    echo "خطا در ثبت محصولات.";

}

?>
        

در این پروژه چند محصول با یک دستور INSERT ثبت می‌شوند و شناسه آخرین رکورد از طریق lastInsertId دریافت می‌شود.

نکته مهم: محاسبه شناسه اولین رکورد با کم کردن تعداد محصولات از شناسه آخرین رکورد فقط در شرایط مشخصی قابل اتکا است؛ از جمله اینکه درج همه رکوردها با یک دستور موفق انجام شده باشد، شناسه‌ها به‌صورت پیوسته تخصیص یافته باشند و مکانیزم‌های دیگری مانند تریگر یا تخصیص شناسه در مسیر ثبت دخالت نداشته باشند. در پروژه‌های حساس بهتر است شناسه هر رکورد را از روش مطمئن و متناسب با طراحی سیستم دریافت کنید.

۱۲. نکات حرفه‌ای برای بهینه‌سازی ثبت اطلاعات در MySQL

زمانی که حجم اطلاعات افزایش پیدا می‌کند، نحوه ثبت رکوردها و مدیریت درخواست‌های دیتابیس اهمیت بیشتری پیدا می‌کند. برای بهبود عملکرد سیستم بهتر است نکات زیر را رعایت کنید:

• برای ثبت اطلاعات گروهی از INSERT چندردیفی استفاده کنید تا تعداد درخواست‌ها کاهش پیدا کند.
• در عملیات چندمرحله‌ای از تراکنش استفاده کنید.
• فقط ستون‌هایی را در دستور SQL وارد کنید که واقعاً به آن‌ها نیاز دارید.
• برای ستون‌های پرتکرار در جستجو، ایندکس مناسب ایجاد کنید.
• اندازه هر دسته از اطلاعات را کنترل کنید تا درخواست‌های بسیار بزرگ به سرور ارسال نشوند.
• خطاهای دیتابیس را ثبت کنید و جزئیات حساس را به کاربران نمایش ندهید.
• از Prepared Statement برای تمام ورودی‌های پویا استفاده کنید.
• برای اطلاعات حجیم، ثبت رکوردها را در دسته‌های کوچک‌تر انجام دهید.

۱۳. تمرین‌های پیشنهادی برای یادگیری بهتر

برای تسلط بیشتر بر مباحث این قسمت، تمرین‌های زیر را انجام دهید:

1. یک جدول users ایجاد کنید و پس از ثبت هر کاربر، شناسه اختصاصی او را با lastInsertId دریافت نمایید.
2. پنج محصول مختلف را با یک دستور INSERT در جدول products ذخیره کنید.
3. یک فرم HTML طراحی کنید که چند محصول را از کاربر دریافت کرده و با استفاده از PHP به‌صورت گروهی ثبت کند.
4. یک دستور SELECT با Prepared Statement بنویسید که اطلاعات کاربر را بر اساس شناسه دریافت کند.
5. با استفاده از Prepared Statement اطلاعات یک کاربر را ویرایش کنید.
6. یک سیستم ثبت سفارش طراحی کنید که با استفاده از Transaction اطلاعات سفارش و محصولات آن را ذخیره کند.
7. خطای ثبت ایمیل تکراری را مدیریت کرده و پیام مناسبی به کاربر نمایش دهید.

۱۴. جمع‌بندی آموزش PHP قسمت ۲۴

در این قسمت از مجموعه آموزش PHP با سه قابلیت بسیار مهم و کاربردی در ارتباط با پایگاه داده MySQL آشنا شدیم.

ابتدا نحوه دریافت شناسه آخرین رکورد ثبت‌شده را با استفاده از lastInsertId در PDO و insert_id در MySQLi بررسی کردیم. یاد گرفتیم که این شناسه برای ارتباط بین جدول‌ها و انجام عملیات بعدی در پروژه‌های مختلف اهمیت زیادی دارد.

سپس با قابلیت Insert Multiple آشنا شدیم و یاد گرفتیم چگونه چندین رکورد را با یک دستور SQL در دیتابیس ذخیره کنیم. همچنین نحوه ثبت تعداد متغیر رکوردها با استفاده از حلقه و Prepared Statement را بررسی کردیم.

در بخش بعدی، مفهوم Prepared Statement را به‌صورت کامل یاد گرفتیم و مشاهده کردیم چگونه می‌توان از آن برای اجرای امن دستورات SELECT، INSERT، UPDATE و DELETE استفاده کرد.

در نهایت با مفهوم Transaction آشنا شدیم و یاد گرفتیم چگونه چند عملیات مرتبط را به‌صورت یکپارچه اجرا کنیم تا در صورت بروز خطا، اطلاعات ناقص در دیتابیس باقی نمانند.

تسلط بر این مفاهیم به شما کمک می‌کند برنامه‌های PHP امن‌تر، سریع‌تر و حرفه‌ای‌تری طراحی کنید. پیشنهاد می‌کنیم تمام مثال‌های این آموزش را در محیط XAMPP اجرا کرده و با تغییر کدها، امکانات جدیدی به آن‌ها اضافه کنید.

۱۵. سؤالات متداول درباره MySQL و PHP

۱. برای دریافت آخرین شناسه ثبت‌شده در PDO از چه دستوری استفاده می‌شود؟

از متد lastInsertId استفاده می‌کنیم. این متد شناسه تولیدشده توسط عملیات درج در اتصال فعلی را برمی‌گرداند.

$lastId = $pdo->lastInsertId();
        

۲. چگونه در MySQLi شناسه آخرین رکورد را دریافت کنیم؟

در MySQLi از ویژگی insert_id استفاده می‌شود:

$lastId = $conn->insert_id;
        

۳. منظور از Insert Multiple چیست؟

Insert Multiple قابلیتی در MySQL است که امکان ثبت چندین رکورد را با یک دستور INSERT فراهم می‌کند. این روش برای ثبت گروهی اطلاعات بسیار کاربردی است.

۴. Prepared Statement چیست؟

Prepared Statement روشی برای آماده‌سازی دستورات SQL و ارسال جداگانه مقادیر ورودی است که نقش مهمی در جلوگیری از SQL Injection دارد.

۵. آیا Prepared Statement فقط برای INSERT استفاده می‌شود؟

خیر. از Prepared Statement می‌توان برای دستورات SELECT، INSERT، UPDATE و DELETE نیز استفاده کرد.

۶. تفاوت prepare و execute در PDO چیست؟

متد prepare دستور SQL را برای اجرا آماده می‌کند و متد execute مقادیر پارامترها را ارسال کرده و دستور آماده‌شده را اجرا می‌کند.

۷. آیا ثبت چندین رکورد با یک دستور سریع‌تر است؟

در بسیاری از شرایط بله؛ زیرا تعداد رفت‌وبرگشت‌های برنامه با دیتابیس کاهش پیدا می‌کند. البته عملکرد نهایی به حجم داده‌ها، ساختار جدول، ایندکس‌ها و شرایط سرور بستگی دارد.

۸. تفاوت bindParam و bindValue چیست؟

bindParam متغیر را با ارجاع به پارامتر متصل می‌کند و مقدار آن هنگام اجرای دستور خوانده می‌شود؛ اما bindValue مقدار را در زمان فراخوانی متد به پارامتر اختصاص می‌دهد.

۹. چرا نباید از MAX(id) برای دریافت شناسه رکورد فعلی استفاده کنیم؟

زیرا MAX(id) بزرگ‌ترین شناسه موجود در جدول را برمی‌گرداند و ممکن است رکورد دیگری توسط اتصال یا درخواست دیگری ثبت شده باشد. برای دریافت شناسه رکورد ثبت‌شده توسط اتصال فعلی باید از lastInsertId یا insert_id استفاده کنیم.

۱۰. Transaction در MySQL چه کاربردی دارد؟

Transaction امکان اجرای چند عملیات مرتبط را به‌صورت یک واحد منطقی فراهم می‌کند. با استفاده از commit تغییرات نهایی می‌شوند و با rollBack می‌توان تغییرات تراکنش را در صورت بروز خطا بازگرداند.