47 Task 2 Application of PDOPHP动态网站开发项目教程课件

Project3OverwritetheMemberAdministrationSystemwiththePHPFrameworkLaravelTask2ApplicationofPDOSchoolofSoftware,ChengduPolytechnicLecturer:MuQichunApplicationofPDOInthepreviousprogram,weconnectMySQLdatabaseusingmysqli_connect()function,andinvokerelevantfunctionstocompletevariousoperations.However,inactualdevelopment,wemayusedifferentdatabases.Atthistime,weshouldusecorrespondingfunctionstoconnectdifferentdatabases,bringinginconveniencetoprogramdevelopment.WearetalkingaboutPDO,isadatabaseaccessabstractionlayerthatisusedtounifyaccessinterfacesofdatabases,withoutmodifyingfunctionsonebyonewhenreplacingthedatabasetype.ApplicationofPDOLet'ssimplyexplainhowtoapplythePDO.BeforeusingthePDO,wemustopenPDOextensionintheserverenvironmentconfiguration.Takingthememberadministrationsystemasanexample,let'sgobacktoXP.Openthe"Website"tab,findthemember,click"Administration",andthenclick"PHPExtension"tomakesurethatPDO_MYSQLisselected.ApplicationofPDOApplicationofPDO:ConnectDatabaseCreateanewsamplefileandenterthefollowing:WecanrunatesttoseeifwecanconnecttoMySQLdatabasenormally.Try-catchstatementinPHPisusedinlines8to12,andusedtohandleexceptions.Ifthecodeinthetryclauseexecutesnormally,thecatchclausewillnotbeexecuted.Oncealineofcodeinthetryclauseisabnormal(forexample,ifthedatausernameiswrong,itisabletoconnecttothedatabasenormally,andthesystemwillthrowanexception),thecatchclausewillautomaticallyexecute,withthecorrespondingerrormessageoutput.ApplicationofPDO:ConnectDatabaseApplicationofPDO:QueryRecordsinDataTableWechangethecodeasbelow:Runningtheprogram,wecanseethatweoutput2recordsintheuserinfotableintwoways.ThequerymethodinPDOismainlyusedhere.ApplicationofPDO:QueryRecordsinDataTableThequery()methodofthePDOinstanceistoexecuteaquerystatementandreturnaPDOStatementobject.Bytraversingthisobject,wecanobtainthequerieddataresultset.Wecanseethatthereturnedresultsetcontainsbothassociativearraysandindexedarrays,butusually,weonlyneedassociativedatafordevelopment.Wecansimplysetitupbyaddingaparameterwhenusingquery()(thisparameterisapredefinedconstantofPDO,whichindicatesthatonlyassociativearraysareobtained.):ApplicationofPDO:Add,DeleteandChangeDataAswehavejustseen,thedatabaseisqueriedbyusingquery()method.Ifwewanttoadd,deleteandenterthedatabase,weneedtouseexec()method.Addthefollowingcodetothesamplefile:ApplicationofPDO:Add,DeleteandChangeDataRunningtheprogram,wecanseethatweaddarecordnormallyandgotitsID.Let'slearnhowtoupdatedatabaserecords.Ifthedataisupdated,thereturned"numberofrecordsaffected"equalsthenumberofrecordstrulychanged.Ifthereisnosubstantialupdate,forexample,thisusernameisoriginallywang,andnowitisupdatedtowang,thereturned"numberofrecordsaffected"is0.ApplicationofPDO:Add,DeleteandChangeDataLet'slearnhowtodeletedatabaserecords.Ifarecordisdeleted,thenumberofdeletedrecordrowsisreturned.Ifnorecordsaredeleted,0isreturned.NotethatPDO::exec()doesnotreturnresultsfromaSELECTstatement.PDO::query()shouldbeusedforthequerystatementthatneedstoexecuteSELECTintheprogram.ApplicationofPDO:PreparedPDOStatementApreparedstatementisastatementreadytobeexecuted,andaPDOStatementobjectisreturned.Generally,weexecutethisstatementbytheexecute()methodofPDOStatementobject.Whyisitprepared?Becauseitallowsustoinvokethisstatementmanytimes,andwecanreplacethefieldconditionsinthestatementwithplaceholders.Comparedwithquery()orexec()thatdirectlyusesPDOobjects,itismoreefficient.Moreimportantly,theplaceholderscanbeusedtoeffectivelypreventbasicSQLinjectionattacks.Itcandirectlysolvethisproblem,andwedon'tmanuallyaddquotationmarkstoSQLstatements.ApplicationofPDO:PreparedPDOStatementTouseapreparedstatement,weneedtousethepreparemethod.Akeyfactoristheuseplaceholdersinparameters.Rememberthatinallstatements,wecanonlyuseplaceholdersforthevaluesofdatacolumns.Forexample,inaquerystatement,thevaluesofthecolumnstobefilteredarereplacedbyplaceholdersinthewhereclause.Orintheinsertstatement,thevaluesoftheinsertedcolumnsarereplacedbyplaceholders.Lookingatthefollowingexample,weuseplaceholder?toreplacethequeriedIDvalue,thenusethepreparemethodtopreparetheSQLstatement,andfinallyinvoketheexecutemethod,withthearrayasaparameter.Then,thequeryiscompleted.Theexecuteparameterisanarray,andthenumberofitselementsshouldbeconsistentwiththenumberofplaceholdersandinthesameorder.ApplicationofPDO:PreparedPDOStatementInthefollowingcode,webuildanSQLquerystatement,wheretheIDintheWHEREclausehasavalueof?,representingaplaceholder.Thentheprepare()methodisinvokedinline4forpreparation,thentheexecute()methodiscalledinline5,andanarrayparameterispassed,wherethevalueof20istheIDinthecorrespondingwhereclause.Uptonow,wehavesimplyunderstoodtheobject-orientedprogrammingmethodofPHPandthePDOuseinPHP.ThereasonwhyweexplainthesecontentsfirstisthatLaravel,aPHPfr