php mysql外文翻译_php毕业设计外文翻译--通过PHP访问MySQL

php毕业设计外文翻译--通过PHP访问MySQL

GettingPHPtoTalktoMySQlNowthatyourecomfortableusingtheMySQLclienttoolstomanipulatedatainthedatabase,youcanbeginusingPHPtodisplayandmodifydatafromthedatabase.PHPhasstandardfunctionsforworkingwiththedatabase.First,weregoingtodiscussPHPsbuilt-indatabasefunctions.WellalsoshowyouhowtousetheThePHPExtensionandApplicationRepositoryPEARdatabasefunctionsthatprovidetheabilitytousethesamefunctionstoaccessanysupporteddatabase.Thistypeofflexibilitycomesfromaprocesscalledabstraction.Inprogramminginterfaces,abstractionsimplifiesacomplexinteraction.Itworksbyremovinganynonessentialpartsoftheinteraction,allowingyoutoconcentrateontheimportantparts.PEARsDBclassesareonesuchdatabaseinterfaceabstraction.Theinationyouneedtologintoadatabaseisreducedtothebareminimum.ThisstandardatallowsyoutointeractwithMySQL,aswellasotherdatabasesusingthesamefunctions.Similarly,otherMySQL-specificfunctionsarereplacedwithgenericonesthatknowhowtotalktomanydatabases.Forexample,theMySQL-specificconnectfunctionismysql_connectdb_host,db_username,db_password;versusPEARsDBconnectfunctionconnectionDBconnectmysql//db_usernamedb_passworddb_host/db_database;Thesamebasicinationispresentinbothcommands,butthePEARfunctionalsospecifiesthetypeofdatabasestowhichtoconnect.YoucanconnecttoMySQLorothersupporteddatabases.Welldiscussbothconnectionsindetail.Inthischapter,youlllearnhowtoconnecttoaMySQLserverfromPHP,howtousePHPtoaccessandretrievestoreddata,andhowtocorrectlydisplayinationtotheuser.TheProcessThebasicstepsofperingaquery,whetherusingthemysqlcommand-linetoolorPHP,arethesameConnecttothedatabase.Selectthedatabasetouse.BuildaSELECTstatement.Perthequery.Displaytheresults.WellwalkthrougheachofthesestepsforbothplainPHPandPEARfunctions.ResourcesWhenconnectingtoaMySQLdatabase,youwillusetwonewresources.Thefirstisthelinkidentifierthatholdsalloftheinationnecessarytoconnecttothedatabaseforanactiveconnection.Theotherresourceistheresultsresource.Itcontainsallinationrequiredtoretrieveresultsfromanactivedatabasequerysresultset.Youllbecreatingandassigningbothresourcesinthischapter.QueryingtheDatabasewithPHPFunctionsInthissection,weintroducehowtoconnecttoaMySQLdatabasewithPHP.Itsquitesimple,andwellbeginshortlywithexamples,butweshouldtalkbrieflyaboutwhatactuallyhappens.WhenyoutryconnectingtoaMySQLdatabase,theMySQLserverauthenticatesyoubasedonyourusernameandpassword.PHPhandlesconnectingtothedatabaseforyou,anditallowsyoutostartperingqueriesandgatheringdataimmediately.AsinChapter8,wellneedthesamepiecesofinationtoconnecttothedatabaseTheIPaddressofthedatabaseserverThenameofthedatabaseTheusernameThepasswordBeforemovingon,makesureyoucanlogintoyourdatabaseusingtheMySQLcommand-lineclient.Figure9-1showshowthestepsofthedatabaseinteractionrelatetothetwotypesofresources.BuildingtheSELECTstatementhappensbeforethethirdfunctioncall,butitisnotshown.ItsdonewithplainPHPcode,notaMySQL-specificPHPfunction.Figure9-1.TheinteractionbetweenfunctionsandresourceswhenusingthedatabaseIncludingDatabaseLoginDetailsYouregoingtocreateafiletoholdtheinationforloggingintoMySQL.Storingthisinationinafileyouincludeisrecommended.Ifyouchangethedatabasepassword,thereisonlyoneplacethatyouneedtochangeit,regardlessofhowmanyPHPfilesyouhavethataccessthedatabase.Youdonthavetoworryaboutanyonedirectlyviewingthefileandgettingyourdatabaselogindetails.Thefile,ifrequestedbyitself,isprocessedasaPHPfileandreturnsablankpage.Letscallthisfiledb_login.phpandplaceitinthesamedirectoryasyourotherPHPfiles.ThefileisrepresentedinExample9-1.Example9-1.AtemplateforsettingdatabaseloginsettingsInExample9-2,wecreatethisfiletouseadatabaseonthesamemachineasthewebserver.Weassignitadatabasename,username,andpassword.Figure9-2illustrateshowyouregoingtousethisfilewithotherPHPfiles.YouregoingtocontinueusingthedatabasethatyoustartedtosetupinChapter7.Figure9-2.ReusingthelogindetailsinmultiplefilesExample9-3.TheSQLtorecreatethetestobjectscontinuedDROPTABLEIFEXISTSbooks;CREATETABLEbookstitle_idint11NOTNULLauto_increment,titlevarchar150defaultNULL,pagesint11defaultNULL,PRIMARYKEYtitle_idENGINEMyISAMDEFAULTCHARSETlatin1;----Dumpingdatafortablebooks--INSERTINTObooksVALUES1,LinuxinaNutshell,476,2,ClassicShellScripting,256;----Tablestructurefortablepurchases--DROPTABLEIFEXISTSpurchases;CREATETABLEpurchasesidint11NOTNULLauto_increment,uservarchar10defaultNULL,titlevarchar150defaultNULL,daydatedefaultNULL,PRIMARYKEYidENGINEMyISAMDEFAULTCHARSETlatin1;----Dumpingdatafortablepurchases--LOCKTABLESpurchasesWRITE;INSERTINTOpurchasesVALUES1,Mdavis,RegularExpressionPocketReference,2005-02-15,2,Mdavis,JavaScriptIfyoudidntcreatethetablesinChapter8,thecodeinExample9-3canbesavedasbackup.sqlandrunfromthecommandpromptwiththefollowingsyntaxmysql-uusername-ppassword-Ddatabase_name.mysql_error;Themysql_connectfunctiontakesthedatabasehost,username,andpasswordasparameters.Iftheconnectionissuccessful,alinktoadatabaseisreturned.FALSEisreturnedifaconnectioncantbemade.Checkthereturnvaluefromthefunctiontomakesuretheresaconnection.Iftheresaproblem,suchasanincorrectpassword,printoutapolitewarningandthereasonfortheerrorusingmysql_error.Insteadofsimplyechoinganerrormessage,diedisplaystheerrorandstopstheprogram.Notbeingabletoaccessthedatabasemakesmostdatabase-drivenpagesfairlyuselessandpreventstheuserfromseeingnumerouserrors.Noticethatwedidntspecifythedatabasenameyet.TroubleshootingconnectionerrorsOneerroryoumaygetisFatalerrorCalltoundefinedfunctionmysql_connectinCProgramFilesApacheSoftwareFoundationApache2.2htdocsdb_test.phponline4ThiserroroccursbecausePHP5.xforWindowswasdownloaded,andMySQLsupportwasnotincludedbydefault.Tofixthiserror,copythephp_mysql.dllfilefromtheext/directoryofthePHPZIPfiletoCphp,andthenCWINDOWSphp.ini.Makesuretherearetwolinesthatarenotcommentedoutbyasemicolon;atthebeginningofthelineliketheseextension_dirc/PHP/ext/extensionphp_mysql.dllThiswillchangetheextensiontoincludethedirectorytoC/phpandincludetheMySQLextension,respectively.YoucanusetheSearchfunctionofyourtexteditortocheckwhetherthelinesarealreadythereandjustneedtobeuncommented,orwhethertheyneedtobeaddedcompletely.YoullneedtorestartApache,andthenMySQLsupportwillbeenabled.SelectingtheDatabaseNowthatyoureconnected,thenextstepistoselectwhichdatabasetousewiththemysql_select_dbcommand.Ittakestwoparametersthedatabasenameand,optionally,thedatabaseconnection.Ifyoudontspecifythedatabaseconnection,thedefaultistheconnectionfromthelastmysql_connect//Selectthedatabasedb_selectmysql_select_dbdb_database;ifdb_selectdieCouldnotselectthedatabase.mysql_error;Again,itsgoodpracticetocheckforanerroranddisplayiteverytimeyouaccessthedatabase.Whileitspossibletocallmysql_select_dbmultipletimeswithinthesamescript,itsnotconsideredgoodpractice.Nowthatyouvegotagooddatabaseconnection,yourereadytocuteyourSQLquery.BuildingtheSQLSELECTQueryBuildingaSQLqueryisaseasyassettingavariabletothestringthatisyourSQLquery.Ofcourse,youllneedtouseavalidSQLquery,orMySQLreturnswithanerrorwhenyoucutethequery.Thevariablenamequeryisusedsincethenamereflectsitspurpose,butyoucanchooseanythingyoudlikeforavariablename.TheSQLqueryinthisexampleisSELECT*FROMbooks.Unlikewhenyouusedthemysqlcommand-lineclient,thequerydoesnothaveasemicolonattheend.Youcanbuildupyourqueryinpartsusingthestringconcatenate.operator//AssignthequeryselectSELECT;column*;fromFROM;tablesbooks;whereNATURALJOINauthors;queryselect.column.from.tables.where;Thiscodeisamoreflexibleversionofthefollowing//AssignthequeryquerySELECT*FROMbooksNATURALJOINauthors;ThequerystringcouldalsouseavariableintheWHEREclausetolimitwhichrowsarereturnedbasedonuserinationoranotherquery.Nowthatyouhaveyourqueryassignedtoavariable,youcancuteit.cutingtheQueryTohavethedatabasecutethequery,usethemysql_queryfunction.Ittakestwoparametersthequeryand,optionally,thedatabaselinkandreturnstheresult.Savealinktotheresultsinavariablecalled,youguessedit,resultThisisalsoagoodplacetocheckthereturncodefrommysql_querytomakesurethattherewerenoerrorsinthequerystringorthedatabaseconnectionbyverifyingthatresultisnotFALSE//cutethequeryresultmysql_queryquery;ifresultdieCouldnotquerythedatabase.mysql_error;Whenthedatabasecutesthequery,alloftheresultsaresultset.Theseresultscorrespondtotherowsthatyousawupondoingaqueryusingthemysqlcommand-lineclient.Todisplaythem,youprocesseachrow,oneatatime.FetchingandDisplayingUsemysql_fetch_rowtogettherowsfromtheresultset.Itssyntaxisarraymysql_fetch_rowresourceresult;Ittakestheresultyoustoredinresultfromthequeryasaparameter.Itreturnsonerowatatimefromthequeryuntiltherearenomorerows,andthenitreturnsFALSE.Therefore,youdoaloopontheresultofmysql_fetch_rowanddefinesomecodetodisplayeachrow//Fetchanddisplaytheresultswhileresult_rowmysql_fetch_rowresultechoTitle.result_row1.;echoAuthor.result_row4.;echoPages.result_row2.;Thecolumnsoftheresultrowarestoredinthearrayandcanbeaccessedoneatatime.Thevariableresult_row2accessesthesecondattributeasdefinedinthequeryscolumnorderorthecolumnorderofthetableifSELECT*isusedintheresultrow.FetchtypesThisisnottheonlywaytofetchtheresults.Usingmysql_fetch_array,PHPcanplacetheresultsintoanarrayinonestep.Ittakesaresultasitsfirstparameter,andthewaytobindtheresultsasanoptionalsecondparameter.IfMYSQL_ASSOCisspecified,theresultsareinddinanarraybasedontheircolumnnamesinthequery.IfMYSQL_NUMisspecified,thenthenumberstartingatzeroaccessestheresults.Thedefaultvalue,MYSQL_BOTH,returnsaresultarraywithbothtypes.Themysql_fetch_associsanalternativetosupplyingtheMYSQL_ASSOCargument.Ifyourewrotethecodeshownpreviouslytousemysql_fetch_arraywithanassociativeinddarray,itwouldlooklikethis//Fetchanddisplaytheresultswhileresult_rowmysql_fetch_arrayresult,MYSQL_ASSOCechoTitle.result_rowtitle.;echoAuthor.result_rowauthor.;echoPages.result_rowpages.;ClosingtheConnectionAsaruleofthumb,youalwayswanttocloseaconnectiontoadatabasewhenyouredoneusingit.Closingadatabasewithmysql_closewilltellPHPandMySQLthatyounolongerwillbeusingtheconnection,andwillfreeanyresourcesandmemoryallocatedtoitmysql_closeconnectionUsingPEARPEARisaframeworkanddistributionsystemforreusablePHPcomponents,creatingacollectionofadd-onfunctionalitiesforPHPdevelopment.Therearemanymodulesavailabletohandleeverythingfromsessionmanagementtoshoppingcartfunctionality.CategoriesofmodulesthatarecurrentlyavailablearelistedinTable9-1.Table9-1.PEARmodulescategoriesAuthenticationHTMLProcessingBenchmarkingHTTPScienceCachingImagesSemanticWebConfigurationInternationalizationStreamsConsoleLoggingStructuresDatabaseMailSystemDate/TimeMathTestEncryptionNetworkingToolsandutilitiesEventNumbersValidateFileatsPaymentWebservicesFilesystemPEARXMLGTKcomponentsPHPOurlistisnotcomplete.Visithttp//tofindoutallofthemodulesthatareavailablefordownload.InstallingPEARusesaPackageManagerthatoverseeswhichPEARfeaturesyouinstall.WhetheryouneedtoinstallthePackageManagerdependsonwhichversionofPHPyouinstalled.IfyourerunningPHP4.3.0ornewer,itsalreadyinstalled.IfyourerunningPHP5.0,PEARhasbeensplitoutintoaseparatepackage.TheDBpackagethatyoureinterestedinisoptionalbutinstalledbydefaultwiththePackageManager.SoifyouhavethePackageManager,youreallset.UnixYoucaninstallthePackageManageronaUnixsystembycutingthefollowingfromtheshellcommand-linepromptlynx-sourcehttp//go-pear.org/|phpThistakestheoutputofthego-pear.orgsitewhichisactuallythesourcePHPcodetoinstallPEARandpassesitalongtothephpcommandforcution.WindowsThePHP5installationincludesthePEARinstallationscriptasCphpgo-pear.bat.IncaseyoudidntinstallallthefilesinChapter2,goaheadandextractallthePHPfilestoC/phpfromthecommandprompt,andcutethe.batfile.IfyouinstalledPHPfromtheMSIinstaller,youmayneedtocutethefollowinginsteadofthego-pear.batfilephpgo-pear.pharIfthePEARdirectorydoesnotexistsatallyoullneedtore-runthePHPMSIinstaller,selecttheChangeoption,andsetExtensionsandExtrasto“Willbeinstalledonlocaldrive”beforerunninggo-pear.phar.Figure9-5showstheinitialscreenaftercutingthePEARinstaller.Figure9-5.Thego-pear.batinstallscriptYoullbeaskedasetofquestionsaboutpaths.Youcanacceptthedefaultsforallofthem.ThebasepathshouldbeCphp.Thephp.filemustbeinyourpath.Verifybytypingphp.fromacommandprompt.Ifitisnotfound,youllneedtoaddittoyourPATHvariable.Toaccessyoursystempath,navigatetoStartControlPanelSystemEnvironment,andaddanentrytotheendofthepathwithCphp.ThePEARinstallercreatesafilecalledCphpPEAR_ENV.reg.YouneedtodoubleclicktosetupthePEARpathsintheregistry.ThisfileiscontingentonwhichPEARversionyouinstalled.Whenthedialogappearstoverifyyourination,youwilladdthistotheregistryandclickOK.Youmayhavetoeditthephp.inifileafterrunningthis.batfiletoaddthePEARdirectorytotheincludepath.Line447ofphp.ininowlookslikethisinclude_path.;cphpincludes;cphpPEARApachemustberestartedbeforetheDBpackagecanbeused.HostedISPMostISPshavePEARDBinstalled.AskyourISPtoinstallitiftheyhaventalready.YoucantellwhetherPEARDBhasbeeninstalledbytryingthePHPcodeinExample9-8toseewhethertherequire_onceDB.php;linecausesanerrorwhenthescriptiscuted.AddingAdditionalPackagesOncethatscomplete,youcanaccessthePEARPackageManagerbyenteringpearatthecommandprompt.Addingnewmodulesisaseasyascutingpearpackagename.YouwontneedtodoanythingbecausetheDBpackagewasinstalledalongwiththeinstallbydefault.However,ifyourerunningWindowsXPHome,youllneedtotakethesestepstoinstallthePEARDBCcdcphpCpearinstallDBCpearlistTofindoutwhichversionsofPEARpackagesareinstalled,cutepearlist.ThatreturnsalistingsuchastheoneshowninFigure9-6.Figure9-6.AlistingofinstalledPEARpackagesandversionsOnceyouvegotPEARinstalled,yourereadytotryitout.RewritingtheBooksExamplewithPEARWhenusingthePEARDBpackage,youfollowthesamesteps.However,thefunctionsyntaxisslightlydifferent.WellgolinebylineandexplainthedifferencesastheyappearinExample9-7.Example9-7.DisplayingthebookstablewithPEARDB1.DBerrorMessageconnection;101112querySELECT*FROMbooksNATURALJOINauthors;13resultconnection-queryquery;1415ifDBisErrorresult16dieCouldnotquerythedatabasequery.DBerrorMessageresult;171819echo;20echoTitleAuthorPages;2122whileresult_rowresult-fetchRow23echo;24echoresult_row1.;25echoresult_row4.;26echoresult_row2.;272829echo;30connection-disconnect;3132Example9-7displaysthescreenshowninFigure9-7.Figure9-7.SwitchingtothePEARDBfunctionsdidntchangetheoutputNoticethatFigure9-7isidenticaltotheoutputinFigure9-4.Line3includesyourdatabaselogininationandremainsunchangedincludedb_login.php;Line4hasanewrequirestatementrequire_onceDB.php;ThisrequiresthefileDB.php,whichprovidesthePEARDBfunctions.Therequire_oncefunctionstopsyourcodefromcutingandreturnsanerroriftheDB.phpfil