Scribd
Upload
845 views8 pages

PDO Tutorial For MySQL Developers - Hashphp2

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_* functions. It then covers how to connect to a MySQL database using PDO, handle errors, perform basic queries and retrieve results, and execute prepared statements with bound parameters. The document is intended as a tutorial for MySQL developers looking to transition to using PDO.

Uploaded by

Leonardo Neves
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
845 views8 pages

PDO Tutorial For MySQL Developers - Hashphp2

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_* functions. It then covers how to connect to a MySQL database using PDO, handle errors, perform basic queries and retrieve results, and execute prepared statements with bound parameters. The document is intended as a tutorial for MySQL developers looking to transition to using PDO.

Uploaded by

Leonardo Neves
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

PDOTutorialforMySQLDevelopers

FromHashphp.org

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
SQLdatabaseprogramminginterfaces.Itdoesn'tsupportmodernSQLdatabaseconceptssuchas
preparedstatements,storedprocs,transactionsetc...andit'smethodforescapingparameterswith
mysql_real_escape_stringandconcatenatingintoSQLstringsiserrorproneandoldfashioned.The
otherissuewithmysql_*isthatithashadalackofattentionlatelyfromdevelopers,itisnotbeing
maintained...whichcouldmeanthingslikesecurityvulnerabilitiesarenotgettingfixed,oritmaystop
workingaltogetherwithnewerversionsofMySQL.AlsolatelythePHPcommunityhaveseenfittostart
asoftdeprecationofmysql_*whichmeansyouwillstartseeingaslowprocessofeventuallyremoving
mysql_*functionsaltogetherfromthelanguage(Don'tworrythiswillprobablybeawhilebeforeit
actuallyhappens!).
PDOhasamuchnicerinterface,youwillendupbeingmoreproductive,andwritesaferandcleaner
code.PDOalsohasdifferentdriversfordifferentSQLdatabasevendorswhichwillallowyoutoeasily
useothervendorswithouthavingtorelearnadifferentinterface.(thoughyouwillhavetolearnslightly
differentSQLprobably).InsteadofconcatenatingescapedstringsintoSQL,inPDOyoubind
parameterswhichisaneasierandcleanerwayofsecuringqueries.Bindingparametersalsoallowfora
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:allyougottadoiscreateanewPDOobject.PDO'sconstructortakesatmost4parameters,
DSN,username,password,andanarrayofdriveroptions.
ADSNisbasicallyastringofoptionsthattellPDOwhichdrivertouse,andtheconnectiondetails...
YoucanlookupalltheoptionsherePDOMYSQLDSN(http://www.php.net/manual/en/ref.pdo
mysql.connection.php).
<?php
$db=newPDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4','username','password');

Note:Ifyougetanerroraboutcharactersets,makesureyouaddthecharsetparametertotheDSN.
AddingthecharsettotheDSNisveryimportantforsecurityreasons,mostexamplesyou'llseearound
leaveitout.MAKESURETOINCLUDETHECHARSET!
Youcanalsopassinseveraldriveroptionsasanarraytothefourthparameters.Irecommendpassingthe
parameterwhichputsPDOintoexceptionmode,whichIwillexplaininthenextsection.Theother
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,yetthisistypicalmysqlcode.Youcan'thandledie()asit
willjustendthescriptabruptlyandthenechotheerrortothescreenwhichyouusuallydoNOTwantto
showtoyourendusersallowingnastyhackersdiscoveryourschema.
PDOhasthreeerrorhandlingmodes.
1.PDO::ERRMODE_SILENTactslikemysql_*whereyoumustcheckeachresultandthenlookat
$db>errorInfo();togettheerrordetails.
2.PDO::ERRMODE_WARNINGthrowsPHPWarnings
3.PDO::ERRMODE_EXCEPTIONthrowsPDOException.Inmyopinionthisisthemodeyou
shoulduse.Itactsverymuchlikeordie(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:youdonothavetohandlewithtrycatchrightaway.Youcancatchitanytimethatis
appropriate.Itmaymakemoresensetocatchitatahigherlevellikeoutsideofthefunctionthatcallsthe
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()methodreturnsaPDOStatementobject.Youcanalsofetchresultsthisway:
<?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()codeabove.ThistellsPDOtoreturn
therowsasanassociativearraywiththefieldnamesaskeys.OtherfetchmodeslikePDO::FETCH_NUM
returnstherowasanumericalarray.ThedefaultistofetchwithPDO::FETCH_BOTHwhichduplicates
thedatawithbothnumericalandassociativekeys.It'srecommendedyouspecifyoneortheothersoyou
don'thavearraysthataredoublethesize!PDOcanalsofetchobjectswithPDO::FETCH_OBJ,andcantake
existingclasseswithPDO::FETCH_CLASS.ItcanalsobindintospecificvariableswithPDO::FETCH_BOUND
andusingbindColumnmethod.Thereareevenmorechoices!Readaboutthemallhere:PDOStatement
Fetchdocumentation(http://www.php.net/manual/en/pdostatement.fetch.php).

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
forSELECTqueries.Keepthisinmindwhenwritingcodeformultipledatabases.
ThisisbecauseMySQL'sprotocolisoneoftheveryfewthatgivethisinformationtotheclientfor
SELECTstatements.Mostotherdatabasevendorsdon'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'ttakeinanyvariables.Thesearesimplestatements
andPDOhastheshortcutmethodsqueryforSELECTstatementsandexecforINSERT,UPDATE,DELETE
statements.Forstatementsthattakeinvariableparameters,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,especiallyifyouhavelotsofparameters.ThisishowyoucandoitinPDO:
<?php
$stmt=$db>prepare("SELECT*FROMtableWHEREid=?ANDname=?");
$stmt>execute(array($id,$name));
$rows=$stmt>fetchAll(PDO::FETCH_ASSOC);

Sowhat'sgoingonhere?Thepreparemethodsendsthequerytotheserver,andit'scompiledwiththe'?'
placeholderstobeusedasexpectedarguments.Theexecutemethodsendstheargumentstotheserver
andrunsthecompiledstatement.Sincethequeryandthedynamicparametersaresentseparately,there
isnowaythatanySQLthatisinthoseparameterscanbeexecuted...soNOSQLINJECTIONcan
occur!Thisisamuchbetterandsafersolutionthanconcatenatingstringstogether.
NOTE:whenyoubindparameters,doNOTputquotesaroundtheplaceholders.Itwillcausestrange
SQLsyntaxerrors,andquotesaren'tneededasthetypeoftheparametersaresentduringexecutesothey
arenotneededtobeknownatthetimeofprepare.
There'safewotherwaysyoucanbindparametersaswell.Insteadofpassingthemasanarray,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,andDELETEarenotdifferentthanSELECT.Butletsdosome
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();

NoteweusedbindValueandnotbindParam.Tryingtobindaparameterbyreferencewillgeneratea
FatalErrorandthiscannotbecaughtbyPDOExceptioneither.

Executingpreparedstatementsinaloop
Preparedstatementsexcelinbeingcalledmultipletimesinarowwithdifferentvalues.Becausethesql
statementgetscompiledfirst,itcanbecalledmultipletimesinarowwithdifferentarguments,and
you'llgetabigspeedincreasevscallingmysql_queryoverandoveragain!

TypicallythisisdonebybindingparameterswithbindParam.bindParamismuchlikebindValueexcept
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(http://www.php.net/manual/en/book.pdo.php)
Retrievedfrom"http://wiki.hashphp.org/index.php?
title=PDO_Tutorial_for_MySQL_Developers&oldid=641"

Thispagewaslastmodifiedon17February2016,at09:33.

Common questions

Powered by AI

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 .

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 .

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 .

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 .

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 .

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 .

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 .

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 .

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 .

You might also like