Scribd
Upload

MySQL PDO Tutorial for Developers

302 views8 pages
This document provides an overview of using PDO (PHP Data Objects) for interacting with a MySQL database in PHP. It begins with an introduction to why PDO should be used over the older mysql…

Uploaded by

Leonardo Neves

PDOTutorialforMySQLDevelopers

[Link]

Contents
1WhyusePDO?
2ConnectingtoMySQL
3ErrorHandling
4RunningSimpleSelectStatements
4.1FetchModes
4.2GettingRowCount
4.3GettingtheLastInsertId
5RunningSimpleINSERT,UPDATE,orDELETEstatements
6RunningStatementsWithParameters
6.1NamedPlaceholders
6.2INSERT,DELETE,UPDATEPreparedQueries
6.3PreparingStatementsusingSQLfunctions
6.4Executingpreparedstatementsinaloop
7Transactions
8SeeAlso
9Externallinks

WhyusePDO?
mysql_*functionsaregettingold.Foralongtimenowmysql_*hasbeenatoddswithothercommon
[Link]'tsupportmodernSQLdatabaseconceptssuchas
preparedstatements,storedprocs,transactionsetc...andit'smethodforescapingparameterswith
mysql_real_escape_stringandconcatenatingintoSQLstringsiserrorproneandoldfashioned.The
otherissuewithmysql_*isthatithashadalackofattentionlatelyfromdevelopers,itisnotbeing
maintained...whichcouldmeanthingslikesecurityvulnerabilitiesarenotgettingfixed,oritmaystop
[Link]
asoftdeprecationofmysql_*whichmeansyouwillstartseeingaslowprocessofeventuallyremoving
mysql_*functionsaltogetherfromthelanguage(Don'tworrythiswillprobablybeawhilebeforeit
actuallyhappens!).
PDOhasamuchnicerinterface,youwillendupbeingmoreproductive,andwritesaferandcleaner
[Link]
useothervendorswithouthavingtorelearnadifferentinterface.(thoughyouwillhavetolearnslightly
differentSQLprobably).InsteadofconcatenatingescapedstringsintoSQL,inPDOyoubind
[Link]
performanceincreasewhencallingthesameSQLquerymanytimeswithslightlydifferentparameters.
PDOalsohasmultiplemethodsoferrorhandling.ThebiggestissueIhaveseenwithmysql_*codeis
thatitlacksconsistenthandling,ornohandlingatall!WithPDOinexceptionmode,youcanget
consistenterrorhandlingwhichwillendupsavingyouloadsoftimetrackingdownissues.

PDOisenabledbydefaultinPHPinstallationsnow,howeveryouneedtwoextensionstobeabletouse
PDO:PDO,andadriverforthedatabaseyouwanttouselikepdo_mysql.InstallingtheMySQLdriver
isassimpleasinstallingthephpmysqlpackageinmostdistributions.

ConnectingtoMySQL
oldway:
<?php
$link=mysql_connect('localhost','user','pass');
mysql_select_db('testdb',$link);
mysql_set_charset('UTF8',$link);

newway:[Link]'sconstructortakesatmost4parameters,
DSN,username,password,andanarrayofdriveroptions.
ADSNisbasicallyastringofoptionsthattellPDOwhichdrivertouse,andtheconnectiondetails...
YoucanlookupalltheoptionsherePDOMYSQLDSN([Link]
[Link]).
<?php
$db=newPDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4','username','password');

Note:Ifyougetanerroraboutcharactersets,makesureyouaddthecharsetparametertotheDSN.
AddingthecharsettotheDSNisveryimportantforsecurityreasons,mostexamplesyou'llseearound
[Link]!
[Link]
parameterwhichputsPDOintoexceptionmode,[Link]
parameteristoturnoffprepareemulationwhichisenabledinMySQLdriverbydefault,butreally
shouldbeturnedofftousePDOsafelyandisreallyonlyusableifyouareusinganoldversionof
MySQL.

<?php
$db=newPDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4','username','password',array(PDO::ATTR_EMULATE_PRE
PDO::ATTR_ERRMODE

YoucanalsosetsomeattributesafterPDOconstructionwiththesetAttributemethod:
<?php
$db=newPDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4','username','password');
$db>setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db>setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

ErrorHandling
Consideryourtypicalmysql_*errorhandling:
<?php
//connectedtomysql
$result=mysql_query("SELECT*FROMtable",$link)ordie(mysql_error($link));

ORdieisaprettybadwaytohandleerrors,[Link]'thandledie()asit
willjustendthescriptabruptlyandthenechotheerrortothescreenwhichyouusuallydoNOTwantto
showtoyourendusersallowingnastyhackersdiscoveryourschema.
PDOhasthreeerrorhandlingmodes.
[Link]::ERRMODE_SILENTactslikemysql_*whereyoumustcheckeachresultandthenlookat
$db>errorInfo();togettheerrordetails.
[Link]::ERRMODE_WARNINGthrowsPHPWarnings
[Link]::ERRMODE_EXCEPTIONthrowsPDOException.Inmyopinionthisisthemodeyou
[Link](mysql_error());whenitisn'tcaught,butunlikeor
die()thePDOExceptioncanbecaughtandhandledgracefullyifyouchoosetodoso.
<?php
try{
//connectasappropriateasabove
$db>query('hi');//invalidquery!
}catch(PDOException$ex){
echo"AnErroroccured!";//userfriendlymessage
some_logging_function($ex>getMessage());
}

NOTE:[Link]
[Link]
PDOstuff:
<?php
functiongetData($db){
$stmt=$db>query("SELECT*FROMtable");
return$stmt>fetchAll(PDO::FETCH_ASSOC);
}

//thenmuchlater
try{
getData($db);
}catch(PDOException$ex){
//handleme.
}

oryoumaynotwanttohandletheexceptionwithtry/catchatall,andhaveitworkmuchlikeordie()
does.Youcanhidethedangerouserrormessagesinproductionbyturningdisplay_errorsoffandjust
readingyourerrorlog.

RunningSimpleSelectStatements
Considerthemysql_*code:
<?php
$result=mysql_query('SELECT*fromtable')ordie(mysql_error());

$num_rows=mysql_num_rows($result);

while($row=mysql_fetch_assoc($result)){
echo$row['field1'].''.$row['field2'];//etc...
}

InPDOYoucanrunsuchquerieslikethis:

<?php
foreach($db>query('SELECT*FROMtable')as$row){
echo$row['field1'].''.$row['field2'];//etc...
}

query()[Link]:
<?php
$stmt=$db>query('SELECT*FROMtable');

while($row=$stmt>fetch(PDO::FETCH_ASSOC)){
echo$row['field1'].''.$row['field2'];//etc...
}

or
<?php
$stmt=$db>query('SELECT*FROMtable');
$results=$stmt>fetchAll(PDO::FETCH_ASSOC);
//use$results

FetchModes
NotetheuseofPDO::FETCH_ASSOCinthefetch()andfetchAll()[Link]
[Link]::FETCH_NUM
[Link]::FETCH_BOTHwhichduplicates
[Link]'srecommendedyouspecifyoneortheothersoyou
don'thavearraysthataredoublethesize!PDOcanalsofetchobjectswithPDO::FETCH_OBJ,andcantake
existingclasseswithPDO::FETCH_CLASS.ItcanalsobindintospecificvariableswithPDO::FETCH_BOUND
[Link]!Readaboutthemallhere:PDOStatement
Fetchdocumentation([Link]

GettingRowCount
Insteadofusingmysql_num_rowstogetthenumberofreturnedrowsyoucangetaPDOStatementanddo
rowCount()
<?php
$stmt=$db>query('SELECT*FROMtable');
$row_count=$stmt>rowCount();
echo$row_count.'rowsselected';

NOTE:ThoughthedocumentationsaysthismethodisonlyforreturningaffectedrowsfromUPDATE,
INSERT,DELETEqueries,withthePDO_MYSQLdriver(andthisdriveronly)youcangettherowcount
[Link].
ThisisbecauseMySQL'sprotocolisoneoftheveryfewthatgivethisinformationtotheclientfor
[Link]'tbotherdivulgingthisinformationtotheclientasit
wouldincurmoreoverheadintheirimplementations.

GettingtheLastInsertId

Previouslyinmysql_*youdidsomethinglikethis.

<?php
$result=mysql_query("INSERTINTOtable(firstname,lastname)VALUES('John','Doe')")ordie("InsertFailed".mysql_err
$insert_id=mysql_insert_id();

WithPDOyoujustdorunthelastInsertIdmethod.
<?php
$result=$db>exec("INSERTINTOtable(firstname,lastname)VAULES('John','Doe')");
$insertId=$db>lastInsertId();

RunningSimpleINSERT,UPDATE,orDELETEstatements
Considerthemysql_*code.
<?php
$results=mysql_query("UPDATEtableSETfield='value'")ordie(mysql_error());
$affected_rows=mysql_affected_rows($result);
echo$affected_rows.'wereaffected';

forPDOthiswouldlooklike:
<?php
$affected_rows=$db>exec("UPDATEtableSETfield='value'");
echo$affected_rows.'wereaffected'

DothesameforsimpleDELETE,andINSERTstatementsaswell

RunningStatementsWithParameters
Sofarwe'veonlyshownsimplestatementsthatdon'[Link]
andPDOhastheshortcutmethodsqueryforSELECTstatementsandexecforINSERT,UPDATE,DELETE
[Link],youshoulduseboundparametermethodsto
executeyourqueriessafely.Considerthefollowingmysql_*code.
<?php
$results=mysql_query(sprintf("SELECT*FROMtableWHEREid='%s'ANDname='%s'",
mysql_real_escape_string($id),mysql_real_escape_string($name)))ordie(mysql_error());
$rows=array();
while($row=mysql_fetch_assoc($results)){
$rows[]=$row;
}

Man!that'sapain,[Link]:
<?php
$stmt=$db>prepare("SELECT*FROMtableWHEREid=?ANDname=?");
$stmt>execute(array($id,$name));
$rows=$stmt>fetchAll(PDO::FETCH_ASSOC);

Sowhat'sgoingonhere?Thepreparemethodsendsthequerytotheserver,andit'scompiledwiththe'?'
[Link]
[Link],there
isnowaythatanySQLthatisinthoseparameterscanbeexecuted...soNOSQLINJECTIONcan
occur!Thisisamuchbetterandsafersolutionthanconcatenatingstringstogether.
NOTE:whenyoubindparameters,[Link]
SQLsyntaxerrors,andquotesaren'tneededasthetypeoftheparametersaresentduringexecutesothey
arenotneededtobeknownatthetimeofprepare.
There'[Link],which
bindseachparameterasaStringtype,youcanusebindValueandspecifythetypeforeachparameter:
<?php
$stmt=$db>prepare("SELECT*FROMtableWHEREid=?ANDname=?");
$stmt>bindValue(1,$id,PDO::PARAM_INT);
$stmt>bindValue(2,$name,PDO::PARAM_STR);
$stmt>execute();
$rows=$stmt>fetchAll(PDO::FETCH_ASSOC);

NamedPlaceholders
Nowifyouhavelotsofparameterstobind,doesn'tallthose'?'charactersmakeyoudizzyandarehard
tocount?Well,inPDOyoucanusenamedplaceholdersinsteadofthe'?':
<?php
$stmt=$db>prepare("SELECT*FROMtableWHEREid=:idANDname=:name");
$stmt>bindValue(':id',$id,PDO::PARAM_INT);
$stmt>bindValue(':name',$name,PDO::PARAM_STR);
$stmt>execute();
$rows=$stmt>fetchAll(PDO::FETCH_ASSOC);

Youcanbindusingexecutewithanarrayaswell:
<?php
$stmt=$db>prepare("SELECT*FROMtableWHEREid=:idANDname=:name");
$stmt>execute(array(':name'=>$name,':id'=>$id));
$rows=$stmt>fetchAll(PDO::FETCH_ASSOC);

INSERT,DELETE,UPDATEPreparedQueries
PreparedStatementsforINSERT,UPDATE,[Link]
examplesanyway:

<?php
$stmt=$db>prepare("INSERTINTOtable(field1,field2,field3,field4,field5)VALUES(:field1,:field2,:field3,:field4,:fie
$stmt>execute(array(':field1'=>$field1,':field2'=>$field2,':field3'=>$field3,':field4'=>$field4,':field5'
$affected_rows=$stmt>rowCount();

<?php
$stmt=$db>prepare("DELETEFROMtableWHEREid=:id");
$stmt>bindValue(':id',$id,PDO::PARAM_STR);
$stmt>execute();
$affected_rows=$stmt>rowCount();

<?php
$stmt=$db>prepare("UPDATEtableSETname=?WHEREid=?");
$stmt>execute(array($name,$id));
$affected_rows=$stmt>rowCount();

PreparingStatementsusingSQLfunctions
YoumayaskhowdoyouuseSQLfunctionswithpreparedstatements.I'veseenpeopletrytobind
functionsintoplaceholderslikeso:
<?php
//THISWILLNOTWORK!
$time='NOW()';
$name='BOB';
$stmt=$db>prepare("INSERTINTOtable(`time`,`name`)VALUES(?,?)");
$stmt>execute(array($time,$name));

Thisdoesnotwork,youneedtoputthefunctioninthequeryasnormal:
<?php
$name='BOB';
$stmt=$db>prepare("INSERTINTOtable(`time`,`name`)VALUES(NOW(),?)");
$stmt>execute(array($name));

YoucanbindargumentsintoSQLfunctionshowever:
<?php
$name='BOB';
$password='badpass';
$stmt=$db>prepare("INSERTINTOtable(`hexvalue`,`password`)VALUES(HEX(?),PASSWORD(?))");
$stmt>execute(array($name,$password));

AlsonotethatthisdoesNOTworkforLIKEstatements:
<?php
//THISDOESNOTWORK
$stmt=$db>prepare("SELECTfieldFROMtableWHEREfieldLIKE%?%");
$stmt>bindParam(1,$search,PDO::PARAM_STR);
$stmt>execute();

Sodothisinstead:
<?php
$stmt=$db>prepare("SELECTfieldFROMtableWHEREfieldLIKE?");
$stmt>bindValue(1,"%$search%",PDO::PARAM_STR);
$stmt>execute();

[Link]
FatalErrorandthiscannotbecaughtbyPDOExceptioneither.

Executingpreparedstatementsinaloop
[Link]
statementgetscompiledfirst,itcanbecalledmultipletimesinarowwithdifferentarguments,and
you'llgetabigspeedincreasevscallingmysql_queryoverandoveragain!

[Link]
insteadofbindingthevalueofavariable,itbindsthevariableitself,sothatifthevariablechanges,it
willbereadatthetimeofexecute.
<?php
$values=array('bob','alice','lisa','john');
$name='';
$stmt=$db>prepare("INSERTINTOtable(`name`)VALUES(:name)");
$stmt>bindParam(':name',$name,PDO::PARAM_STR);
foreach($valuesas$name){
$stmt>execute();
}

Transactions
Here'sanexampleofusingtransactionsinPDO:(notethatcallingbeginTransaction()turnsoffauto
commitautomatically):
<?php
try{
$db>beginTransaction();

$db>exec("SOMEQUERY");

$stmt=$db>prepare("SOMEOTHERQUERY?");
$stmt>execute(array($value));

$stmt=$db>prepare("YETANOTHERQUERY??");
$stmt>execute(array($value2,$value3));

$db>commit();
}catch(PDOException$ex){
//Somethingwentwrongrollback!
$db>rollBack();
echo$ex>getMessage();
}

SeeAlso
ValidationandSQLInjection

Externallinks
PDODocumentation([Link]
Retrievedfrom"[Link]
title=PDO_Tutorial_for_MySQL_Developers&oldid=641"

Thispagewaslastmodifiedon17February2016,at09:33.

Common questions

Powered by AI

Switching to PDO from mysql_* functions significantly enhances application security and code maintainability. By using prepared statements and parameter binding, PDO reduces the risk of SQL injection attacks. Additionally, the ability to reuse prepared statements with different parameters improves performance by compiling the query once, reducing the overhead of repeated parsing and execution for similar queries .

Named placeholders in PDO provide clearer syntax by explicitly indicating each parameter's role, making the code more readable and maintainable, especially when dealing with multiple parameters. This approach reduces mistakes compared to unnamed placeholders, where developers must correctly track the position of each parameter, making the code cumbersome when numerous placeholders are present .

Including the charset in the DSN is crucial for preventing security vulnerabilities related to character encoding attacks. It ensures that the connection uses a consistent character set, which helps avoid issues with string handling and potential SQL injection or data corruption risks that arise from incorrect encoding interpretations .

Disabling prepare emulation in PDO when using the MySQL driver is recommended for enhanced security and to ensure that prepared statements function as intended. Emulated prepares lack the security benefits of server-side prepared statements and can lead to potential SQL injection vulnerabilities in certain situations .

PDO offers three error handling modes: PDO::ERRMODE_SILENT, PDO::ERRMODE_WARNING, and PDO::ERRMODE_EXCEPTION. PDO::ERRMODE_EXCEPTION is preferred as it allows exceptions to be caught and handled gracefully, unlike mysql_* which typically uses die() to handle errors, abruptly ending the script execution and potentially exposing sensitive error messages to users .

Transactions in PDO enhance reliability by allowing multiple operations to be executed as a single atomic unit. If any operation within the transaction fails, changes can be rolled back to maintain data integrity, preventing partial updates that could lead to inconsistent state. This approach is crucial for operations that depend on multiple related updates .

PDO offers a more modern approach to interacting with SQL databases in PHP by supporting prepared statements, stored procedures, and transactions, which mysql_* functions do not . Additionally, PDO provides a cleaner interface, enhances security through parameter binding, increases performance on repeated queries, and facilitates consistent error handling, unlike mysql_* which often lacks consistent handling. Furthermore, PDO supports multiple database drivers, allowing developers to switch databases with minimal code changes .

PDO fetch modes provide flexibility by allowing developers to specify how query results are returned, optimizing for readability, memory usage, or specific data handling requirements. Examples include PDO::FETCH_ASSOC for associative arrays, PDO::FETCH_NUM for numeric arrays, and PDO::FETCH_OBJ for objects. Using specific modes helps in efficient data processing and minimizes redundant data retrieval .

When using SQL functions with PDO prepared statements, ensure the functions are included directly in the SQL query rather than being bound as parameters. Attempting to bind functions like NOW() as parameters will not execute as expected. Instead, SQL functions should be integrated into the query syntax itself, ensuring that the query logic remains intact and functions are correctly processed by the database .

bindParam is advantageous when executing a prepared statement multiple times with varying parameter values in a loop. Since bindParam binds the variable itself rather than its value, it allows the parameter to be dynamically updated within the loop, while bindValue binds the value at the time of binding and is used when the value should remain constant between executions .

PDO Tutorial for MySQL Developers From Hashphp.org Contents 1 Why use PDO? 2 Connecting to MySQL 3 Error Handling 4 Running S
PDO is enabled by default in PHP installations now, however you need two extensions to be able to use PDO: PDO, and a driver 
OR die is a pretty bad way to handle errors, yet this is typical mysql code. You can't handle die(); as it will just end the 
<?php  foreach($db‐>query('SELECT * FROM table') as $row) {      echo $row['field1'].' '.$row['field2']; //etc...  } query() 
Previously in mysql_* you did something like this. <?php  $result = mysql_query("INSERT INTO table(firstname, lastname) VALUE
So what's going on here? The prepare method sends the query to the server, and it's compiled with the '?' placeholders to be 
<?php  $stmt = $db‐>prepare("UPDATE table SET name=? WHERE id=?");  $stmt‐>execute(array($name, $id));  $affected_rows = $stm
Typically this is done by binding parameters with bindParam. bindParam is much like bindValue except instead of binding the v

You might also like