آموزش برنامه نویسی

ثبت، انتخاب، ویرایش و حذف اطلاعات

یک prepared statement که به آن دستور پارامتر پذیر نیز گفته می‌شود، قالبی برای یک دستور SQL است که می‌توانیم از جانگهدار (placeholder) به جای قرار دادن مقادیر واقعی استفاده کنیم. زمانی که دستور اجرا می‌شود، جانگهدارها با مقادیر واقعی جایگزین می‌شوند. همانطور که در زیر نشان داده شده است، در PDO از کاراکتر ? برای مشخص کردن جانگهدار استفاده می‌شود:

INSERT INTO Students 
(FirstName, LastName, Gender, Age, Address) 
VALUES 
(?, ?, ?, ?, ?);

علاوه بر علامت ?، PDO از جانگهدارهای نام گذاری شده نیز پشتیبانی می‌کند. یک جانگهدار نامگذاری شده با کالن (:) آغاز می‌شود و بعد از نام شناسه قرار می‌گیرد. مانند:

INSERT INTO Students 
(FirstName, LastName, Gender, Age, Address) 
VALUES 
(:firstname, :lastname, :gender, :age, :address);

اجرای یک prepared statement شامل دو مرحله است: آماده سازی و اجرا.

• آماده سازی (Prepare): در مرحله آماده سازی، یک قالب برای دستور SQL مورد نظر ایجاد و به سرور پایگاه داده ارسال می‌شود. سپس سرور این قالب را مورد تجزیه و تحلیل قرار می‌دهد و آن را از نظر دستوری و بهینه بودن بررسی می‌کند. سپس آن را برای استفاده بعدی ذخیره می‌کند.
• مرحله اجرا (Execute): در مرحله اجرا، مقادیر پارامترهایی که مشخص کرده بودیم به سرور ارسال می‌شود و سرور یک دستور را بر اساس قالبی که در مرحله قبل ذخیره شده بود ایجاد می‌کند و پس از قرار دادن مقادیر مورد نظر ما در آن، کوئری را اجرا می‌کند.

استفاده از Prepared statement بسیار مفید خواهد بود، به خصوص زمانی که شما می‌خواهید یک کوئری را چندین بار با مقادیر مختلف اجرا کنید.

مزایای استفاده از Prepared statement ها

یک Prepared statement می‌تواند یک دستور را چندین بار با بازدهی بالا اجرا کند. زیرا همانطور که در بخش قبل نیز گفتیم، پس از آنکه یک دستور را یک بار تجزیه و تحلیل کرد می‌توانیم چندین بار آن را اجرا کنیم. همچنین استفاده از پهنای باند را نیز کاهش می‌دهد، زیرا به جای ارسال چند دستور کامل SQL، فقط مقادیر جانگهدار ها را به سرور ارسال می‌کنیم.
Prepared statement همچنین قدرت زیادی برای مقابله با حمله SQL injection دارد. در حالت عادی و زمانی که از Prepared statement استفاده نمی‌کنیم، کوئری ساخته شده توسط مهاجم که دارای مقادیر غیر مجاز است به طور مستقیم به سرور پایگاه داده ارسال می‌شود و پس از تجزیه و تحلیل توسط سرور، اجرا می‌شود و همان نتیجه‌ای را می‌دهد که مهاجم می‌خواهد. ولی در Prepared statement ابتدا کوئری بدون هیچ پارامتری به پایگاه داده ارسال می‌شود و مورد تجزیه و تحلیل قرار می‌گیرد و ذخیره می‌شود. سپس مقادیر پارمترها به سرور ارسال می‌شود. از آنجایی که قبلاً تجزیه و تحلیل رشته کوئری انجام شده، بنابراین هدف مهاجم با شکست مواجه شده و پارامتر غیر مجاز نادیده گرفته می‌شود. به این دلیل است که Prepared statement یک عنصر امنیتی در پایگاه داده در نظر گرفته می‌شود. در ادامه با ذکر یک مثال عملی با نحوه استفاده از Prepared statement آشنا می شوید.

اضافه کردن اطلاعات به جدول

در ادامه مطالب بالا و برای درک بهتر عملکرد Prepared statement به نحوه وارد کردن اطلاعات در جدول با استفاده از این دستورات می پردازیم. در درس های قبلی یک دیتابیس با نام University و یک جدول در داخل آن با نام Students ایجاد کردیم. حال به کد زیر توجه کنید :

  1: <?php
  2:     try 
  3:     {
  4:         $connection = new PDO("mysql:host=localhost;dbname=University", "root", "");
  5:         $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  6:         
  7:         $sql =  "   INSERT INTO Students 
  8:                     (FirstName, LastName, Gender, Age, Address) 
  9:                     VALUES 
 10:                     (:firstname, :lastname, :gender, :age, :address)
 11:                 ";
 12:         
 13:         $statment = $connection->prepare($sql);
 14:         
 15:         $statment->bindParam(':firstname' , $firstname , PDO::PARAM_STR);
 16:         $statment->bindParam(':lastname'  , $lastname  , PDO::PARAM_STR);
 17:         $statment->bindParam(':gender'    , $gender    , PDO::PARAM_STR);
 18:         $statment->bindParam(':age'       , $age       , PDO::PARAM_INT);
 19:         $statment->bindParam(':address'   , $address   , PDO::PARAM_STR);
 20:         
 21:         $firstname  = "Edward";
 22:         $lastname   = "Lyons";
 23:         $gender     = "Male";
 24:         $age        = 17;
 25:         $address    = "Spencer Street";
 26:         $statment->execute();
 27:         
 28:         $firstname  = "Jimmie";
 29:         $lastname   = "Vargas";
 30:         $gender     = "Male";
 31:         $age        = 18;
 32:         $address    = "Blue Bay Avenue";
 33:         $statment->execute();
 34:                
 35:         echo "Records inserted successfully.";        
 36:     }
 37:     catch(PDOException $e)
 38:     {
 39:         echo $e->getMessage();
 40:     }
 41: ?>

در کد بالا ابتدا در خط 4 به سرور و دیتابیس متصل شده ایم. برای وارد کردن اطلاعات در جدول هم در خطوط 11-7 یک دستور SQL نوشته ایم. به خط 10 در کد بالا توجه کنید. در این کد از پارامترهای نامگذاری شده استفاده کرده ایم. در اصل خطوط 11-7 یک Prepared statement می باشد. در خط 13 و با استفاده از متد ()prepare این دستور را به سرور می فرستیم که مورد پردازش قرار بگیرد. برای ارسال مقادیر به پارامترهایی که در خط 10 مشخص شده اند می توان از دو متد ()bindParam و binValue استفاده کرد. در کد بالا ما از ()bindParam استفاده کرده ایم. این متد چهار پارامتر می گیرد :

bindParam(prepMark, $var_val, data_type, length);

prepMark که نام یا اندیس پارامتری است که در Prepared statement مشخص شده است. var_val$ مقداری است که قرار است به پارامتر مورد نظر ارسال شود. data_type نوع پارامتر را مشخص می کند و می توان از مقادیر زیر استفاده کرد :

• PDO::PARAM_NULL
• PDO::PARAM_INT
• PDO::PARAM_STR
• PDO::PARAM_LOB
• PDO::PARAM_STMT
• PDO::PARAM_INPUT_OUTPUT

length تعداد کاراکترهایی است که دوست داریم به آن پارامتر ارسال شده و در بانک ذخیره شوند. البته دو پارامتر آخر یعنی data_type و length اختیاری هستند. به توضیح کد می پردازیم. در خطوط 19-15 با استفاده از متد ()bindParam مقادیر لازم را به پارامترهای موجود در خط 10 ارسال می کنیم. این مقادیر را متد ()bindParam از متغیرهای 25-21 و 32-28 می گیرد. برای اجرای نهایی دستورات و ذخیره آنها در بانک هم متد ()execute را در خطوط 26 و 33 فراخوانی کرده ایم. به جای استفاده از متد ()bindParam می توانید مقادیر را درون یک آرایه قرار دهید و سپس این آرایه را به عنوان پارامتر در متد ()execute استفاده کنید. به مثال زیر دقت کنید :

$statment = $connection->prepare($sql);

$firstPerson = array('firstname' => 'Monica',
                     'lastname'  => 'Ward',
                     'gender'    => 'Female',
                     'age'       => 16,
                     'address'   => 'Mapple Street');

$secondPerson = array('firstname' => 'Joann',
                      'lastname'  => 'Jordan',
                      'gender'    => 'Female',
                      'age'       => 17,
                      'address'   => 'Spencer Street');

$statment->execute($firstPerson);
$statment->execute($secondPerson);

همانطور که در کد بالا مشاهده می کنید برای وارد کردن اطلاعات دو شخص دو آرایه تعریف کرده و آنها را به متد ()execute تحویل می دهیم. و اما متد ()bindValue. این متد سه پارامتر می گیرد :

bindValue(prepMark, value, data_type);

prepMark که نام یا اندیس پارامتری است که در Prepared statement مشخص شده است. value مقداری است که قرار است به پارامتر مورد نظر ارسال شود. data_type نوع پارامتر را مشخص می کند و همان مقادیری را می پزیرد که در بالا به آنها اشاره شد و اختیاری می باشد. حال فرض کنیم که شما می خواستید از متد ()bindValue استفاده کنید. برای اینکار کافی بود خطوط 33-15 را به صورت زیر بنویسید :

$statment->bindValue(':firstname' , "Cheryl"         , PDO::PARAM_STR);
$statment->bindValue(':lastname'  , "Swanson"        , PDO::PARAM_STR);
$statment->bindValue(':gender'    , "Female"         , PDO::PARAM_STR);
$statment->bindValue(':age'       , 17               , PDO::PARAM_INT);
$statment->bindValue(':address'   , "Wacky Street"   , PDO::PARAM_STR);
$statment->execute();

$statment->bindValue(':firstname' , "Clara"          , PDO::PARAM_STR);
$statment->bindValue(':lastname'  , "Webb"           , PDO::PARAM_STR);
$statment->bindValue(':gender'    , "Female"         , PDO::PARAM_STR);
$statment->bindValue(':age'       , 18               , PDO::PARAM_INT);
$statment->bindValue(':address'   , "Spooner Avenue" , PDO::PARAM_STR);
$statment->execute();

همانطور که در کد بالا مشاهده می کنید، این متد مقادیر را به طور مستقیم می گیرد در حالیکه در متد ()bindParam ابتدا باید مقادیر را به یک متغیر اختصاص داده و سپس نام متغیر را در داخل متد بنویسیم. می توان خط 10 کد بالا را به صورت زیر نوشت :

(?, ?, ?, ? ,?)

به هر علامت سوال (?) یک جانگهدار می گویند که به وسیله پارامترهایی که توسط دو متد ()bindParam و ()bindValue ارسال می شوند، جایگزین می شوند. کدهای خطوط 33-15 را به صورت تلفیقی از دو متد مذکور هم می توان به صورت زیر نوشت :

$statment->bindParam(1 , $firstname  , PDO::PARAM_STR);
$statment->bindParam(2 , $lastname   , PDO::PARAM_STR);
$statment->bindParam(3 , $gender     , PDO::PARAM_STR);
$statment->bindParam(4 , $age        , PDO::PARAM_INT);
$statment->bindParam(5 , $address    , PDO::PARAM_STR);

$firstname  = "Zack";
$lastname   = "Norris";
$gender     = "Male";
$age        = 19;
$address    = "Blue Bay Street";
$statment->execute();

$statment->bindValue(1 , "Randall"        , PDO::PARAM_STR);
$statment->bindValue(2 , "May"         	  , PDO::PARAM_STR);
$statment->bindValue(3 , "Male"           , PDO::PARAM_STR);
$statment->bindValue(4 , 19               , PDO::PARAM_INT);
$statment->bindValue(5 , "Golden  Avenue" , PDO::PARAM_STR);
$statment->execute();

به اعداد 1 تا 5 توجه کنید. این اعداد به تعداد علامت های سوال می باشند. که عدد 1 یعنی علامت سوال اول، عدد 2 یعنی علامت سوال دوم و … . و در کل به این معنی است که پارامتر ارسال شده توسط متدهای ()bindParam یا ()bindValue جایگزین کدام علامت سوال شوند. برای به دست آوردن تعداد سطرهای وارد شده در جدول می توان از متد ()lastInsertId به صورت زیر و بعد از ثبت اطلاعات (خط 33) استفاده کرد:

echo $connection->lastInsertId();

انتخاب اطلاعات

برای اجرای دستور SELECT یا همان انتخاب اطلاعات بر روی جدول از متد ()query و برای نحوه نمایش اطلاعات از متد ()fetch استفاده می شود. به کد زیر توجه کنید :

  1: <?php
  2:     try 
  3:     {
  4:         $connection = new PDO("mysql:host=localhost;dbname=University", "root", "");
  5:         $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  6:         
  7:         $sql =  "SELECT * FROM Students";
  8:         
  9:         $statment = $connection->query($sql);
 10: 
 11:         while($row = $statment->fetch(PDO::FETCH_ASSOC))
 12:         {
 13:             echo $row['StudentID']  .' ';
 14:             echo $row['FirstName']  .' ';
 15:             echo $row['LastName']   .' ';
 16:             echo $row['Gender']     .' ';
 17:             echo $row['Age']        .' ';
 18:             echo $row['Address'];
 19:             
 20:             echo '<br/>';
 21:         }
 22:         
 23:     
 24:     }
 25:     catch(PDOException $e)
 26:     {
 27:         echo $e->getMessage();
 28:     }
 29: ?>

در خط 7 کد بالا ابتدا دستور انتخاب اطلاعات را نوشته و سپس با استفاده از متد ()query آن را بر روی بانک اعمال می کنیم. در خط 11 از متد ()fetch اطلاعات را بیرون می کشیم. این متد هر سری یک سطر را بر می گرداند. به همین دلیل است که از حلقه while برای به دست آوردن تمام سطرهای جدول، استفاده کرده ایم. متد ()fetch پارامترهایی می گیرد. که این پارامترها نحوه استخراج و چگونگی نمایش اطلاعات را تعیین می کنند. در زیر لیست این پارامترها ذکر شده است :

• PDO::FETCH_ASSOC : یک آرایه را برمی گرداند که اندیس های آن نام ستون های جدول هستند.
• PDO::FETCH_BOTH (default) : آرایه ای را برمی گرداند که اندیس های آن هم به صورت عددی و هم به صورت نام ستون های جدول هستند.
• PDO::FETCH_BOUND : مقادیر ستون ها را با استفاده از متد ()bindColumn به متغیر های شما اختصاص می دهد.
• PDO::FETCH_CLASS : مقادیر ستون ها را به پراپرتی های کلاس نسبت می دهد. اگر پراپرتی ها موجود نباشند ایجاد خواهند شد. پراپرتی ها همنام با نام ستونها ی جدول خواهند بود.
• PDO::FETCH_INTO : نمونه ی موجود از کلاس نامگذاری شده را بروزرسانی می کند.
• PDO::FETCH_LAZY : ترکیبی از حالت های PDO::FETCH_BOTH/PDO::FETCH_OBJ است و نام اشیاء را همانطور که نامگذاری شده اند، ایجاد می کند.
• PDO::FETCH_NUM : آرایه ای را برمی گرداند که اندیس های آن به صورت عددی است.
• PDO::FETCH_OBJ : یک شیء بی نام (anonymouse) بر می گرداند که نام پراپرتی های آن برابر با نام ستون های جدول است.

به جای متد ()fetch می توانیم از متد ()fetchAll استفاده کنیم که کل سطرها را به صورت یک آرایه بر می گرداند. مثلا کدهای 21-11 را به صورت زیر هم می توان نوشت :

$rows = $statment->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row)
{
    echo $row['StudentID']  .' ';
    echo $row['FirstName']  .' ';
    echo $row['LastName']   .' ';
    echo $row['Gender']     .' ';
    echo $row['Age']        .' ';
    echo $row['Address'];
    
    echo '<br/>';
}

به این نکته توجه کنید که به جای ارسال پارامتر به متدهای ()fetch و ()fetchAll جهت چگونگی نمایش داده ها در خروجی، می توانیم این کار را با استفاده از متد ()setFetchMode انجام دهیم. این متد را می توانیم قبل از متد های مذکور فراخوانی کرده و نوع خروجی را مشخص کنیم:

$statment->setFetchMode(PDO::FETCH_ASSOC);

while($row = $statment->fetch())
{
   //some code
}

یا

$statment->setFetchMode(PDO::FETCH_ASSOC);

$rows = $statment->fetchAll();

نوع خروجی به یک صورت دیگر قابل تعیین است و آن ارسال حالت های مختلف نمایش به متد ()query به صورت زیر می باشد :

$statment = $connection->query($sql, PDO::FETCH_NUM);
       
while($row = $statment->fetch())
{
   //some code
}

یا

$statment = $connection->query($sql, PDO::FETCH_NUM);
       
$rows = $statment->fetchAll();

برای به دست آوردن تعداد رکوردهای جدول می توان از متد ()rowCount بعد از حلقه های foreach و while به صورت زیر :

echo $statment->rowCount();

و برای به دست آوردن تعداد ستون های جدول هم می توان از متد ()columnCount به صورت زیر استفاده کرد:

echo $statment->columnCount();

بروزرسانی اطلاعات

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

  1: <?php
  2:     try
  3:     {
  4:         $connection = new PDO("mysql:host=localhost;dbname=University", "root", "");
  5:         $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  6:         
  7:         $sql = "UPDATE Students SET firstName= :firstname WHERE studentID = :studentid";
  8:         
  9:         $statment = $connection->prepare($sql);
 10:         
 11:         $statment->bindParam(':firstname' , $firstname , PDO::PARAM_STR);
 12:         $statment->bindParam(':studentid' , $studentid , PDO::PARAM_INT);
 13:         
 14:         $firstname  = "Syluster";
 15:         $studentid  = 5;
 16:         
 17:         $statment->execute();
 18:     
 19:         echo $statment->rowCount() . " Row(s) Updated successfully.";
 20:     }
 21:     catch(PDOException $e)
 22:     {
 23:         echo $e->getMessage();
 24:     }
 25: ?>

همانطور که در خط 7 کد بالا مشاهده می کنید، دستور SQL ما یک دستور آپدیت است، که در آن ما می خواهیم نام یک شخص، که دارای یک شماره ID خاص است را تغییر دهیم. این دستور چگونه تکمیل می شود؟ با مقادیری که در خطوط 14 و 15 مشخص کرده ایم.

حذف اطلاعات
حذف اطلاعات هم شبیه به عملیات بروزرسانی است و فقط دستور SQL آن فرق می کند. به کد زیر توجه کنید :

  1: <?php
  2:     try
  3:     {
  4:         $connection = new PDO("mysql:host=localhost;dbname=University", "root", "");
  5:         $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  6:         
  7:         $sql = "DELETE FROM Students WHERE studentID = :studentid";
  8:         
  9:         $statment = $connection->prepare($sql);
 10:         
 11:         $statment->bindParam(':studentid', $studentid, PDO::PARAM_INT);
 12: 
 13:         $studentid  = 5;
 14:         
 15:         $statment->execute();
 16:     
 17:         echo $statment->rowCount() . " row(s) deleted successfully.";
 18:     }
 19:     catch(PDOException $e)
 20:     {
 21:         echo $e->getMessage();
 22:     }
 23: ?>

در خط 7 کد بالا ما از برنامه خواسته ایم که اطلاعات شخصی که دارای یک ID خاص است را حذف کند و این ID همان عددی است که در خط 13 مشخص کرده ایم.