Table126headersalreadysent

PracticalPHPandMySQLWebSiteDatabasesASimplifiedApproachAdrianW.
WestPracticalPHPandMySQLWebSiteDatabases:ASimplifiedApproachCopyright2013byAdrianW.
WestThisworkissubjecttocopyright.
AllrightsarereservedbythePublisher,whetherthewholeorpartofthematerialisconcerned,specificallytherightsoftranslation,reprinting,reuseofillustrations,recitation,broadcasting,reproductiononmicrofilmsorinanyotherphysicalway,andtransmissionorinformationstorageandretrieval,electronicadaptation,computersoftware,orbysimilarordissimilarmethodologynowknownorhereafterdeveloped.
Exemptedfromthislegalreservationarebriefexcerptsinconnectionwithreviewsorscholarlyanalysisormaterialsuppliedspecificallyforthepurposeofbeingenteredandexecutedonacomputersystem,forexclusiveusebythepurchaserofthework.
DuplicationofthispublicationorpartsthereofispermittedonlyundertheprovisionsoftheCopyrightLawofthePublisher'slocation,initscurrentversion,andpermissionforusemustalwaysbeobtainedfromSpringer.
PermissionsforusemaybeobtainedthroughRightsLinkattheCopyrightClearanceCenter.
ViolationsareliabletoprosecutionundertherespectiveCopyrightLaw.
ISBN-13(pbk):978-1-4302-6076-9ISBN-13(electronic):978-1-4302-6077-6Trademarkednames,logos,andimagesmayappearinthisbook.
Ratherthanuseatrademarksymbolwitheveryoccurrenceofatrademarkedname,logo,orimageweusethenames,logos,andimagesonlyinaneditorialfashionandtothebenefitofthetrademarkowner,withnointentionofinfringementofthetrademark.
Theuseinthispublicationoftradenames,trademarks,servicemarks,andsimilarterms,eveniftheyarenotidentifiedassuch,isnottobetakenasanexpressionofopinionastowhetherornottheyaresubjecttoproprietaryrights.
Whiletheadviceandinformationinthisbookarebelievedtobetrueandaccurateatthedateofpublication,neithertheauthorsnortheeditorsnorthepublishercanacceptanylegalresponsibilityforanyerrorsoromissionsthatmaybemade.
Thepublishermakesnowarranty,expressorimplied,withrespecttothematerialcontainedherein.
PresidentandPublisher:PaulManningLeadEditor:BenRenow-ClarkeTechnicalReviewer:AndrewZackEditorialBoard:SteveAnglin,EwanBuckingham,GaryCornell,LouiseCorrigan,JamesDeWolf,JonathanGennick,JonathanHassell,RobertHutchinson,MichelleLowman,JamesMarkham,MatthewMoodie,JeffOlson,JeffreyPepper,DouglasPundick,BenRenow-Clarke,DominicShakeshaft,GwenanSpearing,MattWade,SteveWeiss,TomWelshCoordinatingEditor:KevinSheaCopyEditor:RogerLeBlancCompositor:SPiGlobalIndexer:SPiGlobalArtist:SPiGlobalCoverDesigner:AnnaIshchenkoDistributedtothebooktradeworldwidebySpringerScience+BusinessMediaNewYork,233SpringStreet,6thFloor,NewYork,NY10013.
Phone1-800-SPRINGER,fax(201)348-4505,e-mailorders-ny@springer-sbm.
com,orvisitwww.
springeronline.
com.
Forinformationontranslations,pleasee-mailrights@apress.
com,orvisitwww.
apress.
com.
ApressandfriendsofEDbooksmaybepurchasedinbulkforacademic,corporate,orpromotionaluse.
eBookversionsandlicensesarealsoavailableformosttitles.
Formoreinformation,referenceourSpecialBulkSales–eBookLicensingwebpageatwww.
apress.
com/bulk-sales.
Anysourcecodeorothersupplementarymaterialsreferencedbytheauthorinthistextisavailabletoreadersatwww.
apress.
com.
Fordetailedinformationabouthowtolocateyourbook'ssourcecode,gotowww.
apress.
com/source-code.
Idedicatethisbooktotheopensourcecommunity,withoutwhichtherewouldbenoApache,PHP,MySQL,XAMPP,MAMPP,EASYPHPorphpMyAdmintowriteabout.
Becausethesoftwareisfreeandsupportedbymanyforumsandlistsrunbyknowledgeableenthusiasts,thereisnoobstaclepreventingme(ormyreaders)fromlearninghowtoproducedatabase-drivenwebsites.
—AdrianW.
WestvContentsataGlanceAbouttheAuthorxixAbouttheTechnicalReviewerxxiAcknowledgmentsxxiiiIntroductionxxvChapter1:CreateandTestaMySQLDatabaseandTable1Chapter2:CreateWebPagesThatInteractwithUsers25Chapter3:Login/LogoutforMembersandanAdministrator61Chapter4:DisplayMembershipTablesfortheAdministrator93Chapter5:RegisterAddressesandPhoneNumbers125Chapter6:TheFinishingTouches157Chapter7:MigratingtoaHostandBackingUpYourWebSiteDatabase187Chapter8:CreatingaProductCatalog223Chapter9:AddingMultipleTablesandOtherEnhancements259Chapter10:CreatingaMessageBoard295Chapter11:E-Commerce:ABriefIntroduction335Chapter12:TroubleshootingYourDatabase-DrivenWebSite379Appendix387Index407viiContentsAbouttheAuthorxixAbouttheTechnicalReviewerxxiAcknowledgmentsxxiiiIntroductionxxvChapter1:CreateandTestaMySQLDatabaseandTable1DefiningtheTermDatabase1DefiningDeveloper,Administrator,andUser2DefiningInteractiveWebSites2OnlyUseMySQLforInteractiveDatabaseTables3MethodsforDevelopingandMaintainingDatabases3ABriefLookInsidetheMachinery3AFreeDevelopmentPlatformforTesting5UsingXAMPPonYourOwnComputer5WillIBeAbletoTransfertheDatabasefromXAMPPtoaRemoteHost5DownloadandInstallXAMPP6AccessingphpMyAdminUsingXAMPP10PlanningaDatabase:TheEssentialFirstStep12CreateaDatabaseUsingphpMyAdmin13CreateaTableUsingphpMyAdmin16TheSQLAlternative19DeletingDatabasesandTables22Summary24ContentsviiiChapter2:CreateWebPagesThatInteractwithUsers25CreatetheFolderforHoldingtheDatabasePages26CreatetheTemporaryTemplate26IntroducingthePHPinclude()Function28TheIncludedHeaderFile28TheIncludedMenuFile29TheIncludedInformationColumn31TheIncludedFooterFile31StylingtheTemporaryTemplate32TheInteractiveVersionoftheTemplate33ConnectingtotheDatabase35ExplanationoftheCode35TheRegistrationPage36ThePHPKeywordecho42StylingtheFormFields43StickyForms45ExplanationoftheCode46TheThankYouPage46DisplayingErrorMessagesThatAreCollectedinanArray47ViewingMembers'Records48TheViewUsersPage49ExplanationoftheCode50TheChangePasswordPage51ExplanationoftheCode54ConfirmingaSuccessfulPasswordChange54DealingwithanApostrophe54TutorialDealingwiththeApostrophe55ApostrophesWithinPHPcode57TestingtheTutorial'sPages57ContentsixMoreAboutArrays58Summary59Chapter3:Login/LogoutforMembersandanAdministrator61CreatetheLogindbDatabaseandTable62TidytheStyling63RemoveorReplaceRedundantMenuButtonsintheHeaders64AddaLoginButtontotheHomePageHeader65RemoveRedundantButtonsfromtheRegistrationandNewPasswordHeaders65TheRevisedRegistrationPage66TheNewHeaderfortheNewPasswordPage67ANewHeaderMenufortheMembers'Page67AmendtheHeaderfortheThank-YouPage68TheRegistrationPageandUndesirableCharacters69RegisterSomeMembers72DifferentiatingBetweenTwoTypesofMembership73CreateUserLevelstoLimitAccesstoPrivatePages73LogIn76TheHeaderfortheLoginPage77TheLoginPage78ExplanationoftheCode80TheLoginFormFields81Sessions82AMembers-onlyPage84ExplanationoftheCode86PlanningtheAdministrator'sRole87ANewHeaderfortheAdministrationPage87TheAdministrator'sPage88ExplanationoftheCode89ContentsxTheLogoutPage90ExplanationoftheCode90TestingtheLogin/logoutFunction91AmendingandDeletingIndividualRecords91Summary92Chapter4:DisplayMembershipTablesfortheAdministrator93TheAdministrationDatabase93TheUsersTable94TheRevisedAdministrationPage95ExplanationoftheCode97RevisingtheViewUsersPagetoIncludeEditingandDeleting98ExplanationoftheCode100DisplayingPagesofRecords(Pagination)102ExplanationoftheCode106PlanningtheSearchCriteria108ATemporaryPageforDisplayingSpecifiedMembers109ExplanationoftheCode111TheSearchForm112ExplanationoftheCode113TheFinalFormHandlerforReceivingSearchFormInput114EditingRecords116ExplanationoftheCode118DeletingRecords120ExplanationoftheCode122Summary123Chapter5:RegisterAddressesandPhoneNumbers125CreateaNewDatabaseandaTablewith17Columns125CreatetheFileforConnectingtotheDatabase126CreatetheTable126UsingENUM127ContentsxiTheImportanceofDocumentation128ExtendtheRegistrationForm,andAddaPull-downMenu129AlwaysAnnouncePricesandFeePaymentsUpFront130ExplanationoftheCode134StickyFieldsforaPull-downMenu136AddPayPalDebit/CreditCardImages136TheHeaderfortheRegistrationPage137IncludePayPalontheThankYouPage138ExplanationoftheCode140RegisterSomeMembers140ASmallAmendmenttotheLoginPage142AmendtheAdministrator'sHeader142ApplyPaginationtotheadmin_view_usersTable143ExplanationoftheCode146SearchingandEditingRecords147ExplanationoftheCode150ModifytheFormforEditingRecords150SearchingforMembers'AddressesandPhoneNumbers154Summary156Chapter6:TheFinishingTouches157CreatetheDatabase157CreatetheFileforConnectingtotheDatabase158CreatetheTablebyImportinganSQLDumpFile158IfyouwishtoCreatetheTableManually159RegisteringSomeMembersManually160TidytheFoldersandFilingSystem162TheStyleSheet163DegreesofSecurity164TheMinimumLayerofSecurity164AnIncreasedLayerofSecurity165ContentsxiiValidationandSanitization165Thefilter_var()Function166Validation166ExplanationoftheCode167Sanitization168TextAreasandSanitization168ValidatingTelephoneNumbers169AMoreSecureRegistrationPage169ExplanationoftheCode175SearchforanAddressandTelephoneNumber178ViewingtheRetrievedAddressandPhoneNumber179ExplanationoftheCode181EditAddressesandTelephoneNumbers182ExplanationoftheCode186Summary186Chapter7:MigratingtoaHostandBackingUpYourWebSiteDatabase187MakingLast-MinuteChanges187CreateaNewDatabase188DisplayingtheMembers'TitlesinthePaginatedTable190AllowMemberstoUpdateTheirOwnRecords191SafeE-mailing201AMinorProblem202ASecureFeedbackForm202TheFeedbackForm204TheStyleSheetsfortheFeedbackForm210TheThankYouPageandtheErrorMessages211MigratingtheDatabaseandTablestoaRemoteHost213APuzzlingErrorMessage214CreatingandExportingtheSQLFile214UsingtheGUIsonaRemoteHost'sServer218ConnectingtotheDatabaseontheRemoteHost219ContentsxiiiSecurelyUploadthemysqli_connect.
phpFile220UploadingtheInteractivePagestotheHost221BackUpYourDatabase222Summary222Chapter8:CreatingaProductCatalog223PreparetheDatabaseandAdministrationPlan223CreateaNewDatabase224CreatetheFileforConnectingtotheDatabase225Security226CreatingaHomePagewithSearchCapability226TheHeaderfortheMajorityofthePages228TheHomePageCode228DisplayingtheCatalog232ExplanationoftheCode235TheHeaderforthePageofSearchResults236CreatingtheAdmin/AddaHousePage236TheHeaderfortheAdministrator'sPage242TheHeaderwithTwoExtraButtons243Administrator'sViewoftheEntireStockofHousesforSale244TheAdministrator'sSearchPage247TheResultofaSearch248DisplayingtheFullDescriptionofaHouse250ExplanationoftheCode253TheContactUsPage253Summary258Chapter9:AddingMultipleTablesandOtherEnhancements259IntroductiontoMultipleTables259Normalization260CreatetheDatabaseandTables260ViewtheConnectionFile261ContentsxivPreparingtheTablesforJoining262AddSomeData266Joins267TheHomePagefortheMultipleTablesTutorial271TheMainMenufortheHomePage273PaymentsbyCheck285AChoiceofPaymentMethod287PrintingOnlineForms292ExplanationoftheCode293Summary294Chapter10:CreatingaMessageBoard295ThePlan296CreatetheDatabase296CreatetheTables297CreatetheTemplatefortheMessageBoardWebSite298CreatetheRegistrationForm301TheThankYouPage304RegisterSomeMembers306TheLoginPage306LoggingOut310CreatingaGatewaytotheTwoCategoriesofQuotes311TheFormforPostingQuotations312ExplanationoftheCode314ProcessingthePostings315ExplanationoftheCode316PostSomeQuotations317TheHomePage318ExplanationoftheCode320TheComicalQuotesPage320TheWiseQuotesPage322ContentsxvAddingSearchFacilities325ExplanationoftheCode327TheHeaderforViewPosts.
php327SearchforSpecificWordsorPhrases327PreparingtheTableforFullTextSearches328TheFullTextSearchForm328DisplayingtheSearchResults330ExplanationoftheCode332TheHeaderforthequotes_foundPage332EnhancingtheMessageBoard333ConvertingtheMessageBoardtoaForum333Summary334Chapter11:E-Commerce:ABriefIntroduction335ItemsCommontoBothShoppingCarts337SecurityWarning337ThePlan337TheHomePage338RegisteringUsers339TheLoginPagewithaForgotten-PasswordLink341RetrievingaForgottenPassword344Administration347CreatingtheAdministrationPage348SearchingandDisplayingProducts349FeaturesApplicableOnlytothePayPalShoppingCart351IntegratingwiththePayPalShoppingCart356DisplayingtheResultofaSearch358ACustomShoppingCart361TheDatabaseandTables363TheTables364AddPaintingstoaTableforaCustomShoppingCart366ContentsxviDisplayingtheProductsUsingCustomShoppingCartLinks368AddingPurchasestotheCart371TheCheckoutPage377AdditionalAdministrativeTasks378Summary378Chapter12:TroubleshootingYourDatabase-DrivenWebSite379HTMLCodeErrors380BrowserQuirks380AStyleChangeHasNoEffect380APageFailstoValidate380APayPalPull-DownMenuDoesNotWork381PHPScriptErrors381IncludedItemsMissingfromtheDisplay381CalltoanUndefinedFunction381CannotRedeclareFunction381UndefinedIndexorUndefinedVariable381EmptyVariableValue381GeneralVariableErrors382HeadersAlreadySent382BlankScreen382UnexpectedEndofFileinLinexxx382CommonPHPParseErrors383UnexpectedT_STRING383UnexpectedT_ELSE384WrongEqualsSign384FailedtoOpenStream384Warning:DivisionbyZero384DisplayIsNotWhatWasExpected384MySQLErrors385TableDisplaysHeadingsOnly385AccessDenied386ContentsxviiSyntaxErrors386ReferencetoaPrimaryKeyCouldNotbeCreated386Summary386Appendix387PHPQuickReference387Arrays387AssociativeArrays388Comments390Concatenation390Constants390E-mailingwithPHP390Functions392include()vs.
require()392if,else,andelseif393Loops394Numbers396QuotationMarks397Sessions397TernaryOperator399ValidationandSanitizationFilters400Variables400MySQLandphpMyAdminQuickReference401INSERT401SELECT401UPDATE401StorageEnginesandphpMyAdmin401ChangingtheStorageEngineonanExistingPopulatedTable402WhatNext403ResourcesforPHPandMySQL404HTMLandPHPEditingSoftware404ContentsxviiiPHPandMySQLInternetResources405ResourceforCreatingaForum405E-CommerceResources405OnlineTutorials405IntegratingPayPalwithaCustomShoppingCart406PayPalForums406Third-PartyShoppingCarts406Summary406Index407xixAbouttheAuthorAdrianWestresignedasacharteredengineertobecometheUKdirectorofacorrespondenceschool.
Hehasbeenteachinginoneformoranothersince1982.
Heintroducedcomputersintohisworkplacein1987andtaughtthestaffhowtousethem.
Forfouryears,hetaughtundergraduatescomputerskillsatacollegeinCheshireintheUnitedKingdom.
AdrianlivesinColyton,atowninDevon,England,andforthelast14years,hehasdesignedandproducedwebsitesforlocalbusinessesandcharities.
Foratime,healsoservedasacomputertechnicianandteachertoabout100peopleinhiscommunity.
Thenhedecidedtoconcentrateonhisfavoriteoccupation,designingwebsites.
Toavoiddisappointinghisformerclients,helaunchedafreecomputer-helpwebsiteathttp://www.
colycomputerhelp.
co.
uk.
Adrianalsowritesmonthlycomputer-helparticlesfortwolocalmagazines.
AdrianistheauthorofPracticalHTML5Projects(Apress.
com,2012),abookdedicatedtoimprovingthedesignofwebsites.
Thatbookwaspromptedbythelackofinformationoncertainaspectsofwebdesign.
Heresearched,tested,anddevelopedsolutionsfortheseusefulbutotherwisepoorlydocumentedtechniques.
xxiAbouttheTechnicalReviewerAndrewZackistheCEOofZTMC,Inc.
(ztmc.
com)specializinginSearchEngineOptimization(SEO)andInternetmarketingstrategies.
Hisprojectbackgroundincludesalmost20yearsofsitedevelopmentandprojectmanagementexperienceandover15yearsasaSEOandInternetmarketingexpert.
Mr.
Zackhasalsobeenveryactiveinthepublishingindustry,havingco-authoredFlash5Studioandservedasatechnicalrevieweronovertenbooksandindustrypublications.
xxiiiAcknowledgmentsIthankmywife,Janice,forherloveandsupport,fortakingovermyshareofthechoressothatIcouldconcentrateonthisbook,forherencouragement,andforputtingupwithmyabsenceasIhunchedoverthekeyboard.
Icouldneverhavemanagedwithouthermeticulousproofreading,whichshepatientlyrepeatedfourtimesperchapteraseacheditorialstagewasreached.
MythanksalsogotothemagnificentteamatApress:BenRenow-Clarkeforhisencouragementandforhisadviceonthelayoutandcontentofthechapters;KevinShea,whocoordinatedeverybodyandensuredthatIsentchaptersandfilesontime;andRogerLeBlanc,thecopyeditor,whopolishedmychaptersandhelpedmeconformtotheApresshousestyle.
IthankAndrewZack,thetechnicalreviewer,whocheckedmycodeandsuggestedseveralusefulresourcesforinclusioninthebook.
IthanktheApressproductionteamandSPiGlobalforpromptlyandefficientlydealingwithtweaksandrevisionstothepageproofs.
AndmythanksgotoallthepeopleinforumswhohelpedmeandrepliedtomyqueriesandtoallthosewhoplacedinformationontheInternet,fromwhichIlearnedsomuch.
—AdrianW.
WestxxvIntroductionTheTeachingMethodIamawebsitedesignerratherthanaprogrammer.
Mychoiceofabookisbasedonhowmuchpracticalapplicationitcontains,notonhowmuchabookconcentratesonthesyntaxofalanguageasanendinitself.
Thisbookfollowsmypreference;therefore,PracticalPHPandMySQLWebSiteDatabases-ASimplifiedApproachusesadifferentwayofteachingwebsitedatabasedesignthanthemajorityofmanuals.
TheusuallayoutstartswithseverallessonsonPHPfollowedbysnippetsofcommand-linecode,anditmayeventuallyconcludewithaprojectortwo.
Thisbookabandonsthatapproach.
Theprimaryfocusisonfullyworked,practicalMySQLdatabaseprojectsbuiltintoreal-worldwebpages.
Inthisbook,practicaldatabasesandinteractivewebpagesarepresentedasearlyaspossible;infact,youwillcreateadatabaseandatableinthefirstchapter.
Inthesecondchapter,youwillembedadatabaseintoaninteractive(dynamic)webpageandtestit.
Eachsubsequentchapterwillintroduceyoutoincreasinglysophisticatedandusefuldatabase-drivenwebsitepages.
ToabusywebdesignerwhoisunfamiliarwithPHPanddatabases,therequirementsinorderofimportanceareasfollows:HowtoembedPHPandinteractivedatabasesintoreal-worldwebpages.
Thisistheprimarythemethroughoutthebook.
Howtocreateafreeenvironmentfortestingdatabase-drivenwebpages.
Howtocreateauser-friendlyinterfacesothatanadministratorwithminimumcomputerskillscanmonitorthedatabase.
TounderstandhowPHP,HTML,andMySQLworktogethertocreateandmaintainadatabaseanditsdata.
InsteadofpresentingPHP,SQL,andMYSQLascompletelyseparatetopics,theseareexplainedincontextastheprojectsunfold.
However,whenyoueventuallybecomeproficientintheselanguages,aquickPHP/MySQLreferencewouldbehelpful;therefore,youwillfindthisintheAppendix.
Becausedatabasescanonlybeviewedandtestedonaserver,thefirstpartofChapter1hasinstructionsforusingafreeserverthatcanbedownloadedandinstalledonthereader'scomputer.
Thisensuresthatreaderswillhaveasafedevelopmentplatformforlearningandtestingastheyexplorethebook'spracticalprojects.
Astudyofthetheoryandsyntaxcandeterlearnersandprolongthetimeuntiltheygettheirhandsonapracticalapplication.
Thehistoryanddevelopmentofthecarandastudyoftheinternalcombustionenginewillnothelpawould-bedriver.
However,jumpingintoacaranddrivingitwillproducequickerresults,andlearnersareenthusedwhentheyachievesomething.
Thisbookjumpsintothedatabasedrivingseatrightfromthebeginning.
EssentialPHPandMySQLtechniquesarepresentedincontextwithineachtutorial,wheretheyaremostrelevant.
Somedatabasetextbooksadvocateusingaframework;theysuggestthatthisfacilitatesthedevelopmentofadatabase-drivenwebsite.
Ifindframeworksutterlyconfusing,eventhoughIhaveexperienceprogrammingdatabasesusingrawcode.
Ifyouareabeginner,Isuggestyousteerclearofframeworksuntilyouhavegainedsomemoreexperienceandunderstandthefundamentalsofhowadatabase-drivenwebsiteworks.
IntroductionxxviWhoIsThisBookForThebookassumesthatthereaderisthoroughlyfamiliarwithHTMLandCSS.
However,concerningMySQL,PHP,andphpMyAdmin,thebookstartsfromanabsolutebeginner'spointofview.
Asthechaptersunfold,theyprogresstowardsintermediatelevel.
Becausecommand-lineprogrammingwouldnotbewelcomedbythemoderngenerationofreaders,thebookconcentratesonmouse-operatedGraphicalUserInterfaces(GUIs)andPHPfilesforcreatingandmanagingdatabases.
YoudonotneedtoacquireanextensiveknowledgeofPHPtocreateinteractivedatabases.
IintroduceallthePHPyouwillneedintheappropriateplacewithineachproject.
EachpieceofPHPcodeisexplainedfullyinplainEnglish.
Thestep-by-step,fully-workedexampleswillshowyouwhatMySQLandPHPcandoandhowtodoit.
PracticalPHPandMySQLWebSiteDatabases-ASimplifiedApproachisforwebdesignerswhowishtobegindevelopingdatabase-drivenwebsites.
Liketheauthor,theymayhavestruggledwiththecurrentmanualsanddespaired.
Theymayalsohavebeenfrustratedbythelimitationsofpaint-by-numberscontentmanagementsystemssuchasJoomlaandWordpress.
Withthisinmind,PracticalPHPandMySQLWebSiteDatabases-ASimplifiedApproachusesahighlymotivational,step-by-stepapproach.
Theauthorrecognizesfullythatasenseofachievementencouragesthereadertolookforwardeagerlytothenextstep.
ForreaderswhohavelittleornoknowledgeofPHP,thebookwillteachenoughPHPtocompletealltheprojectsinthebook.
WebdeveloperswhoarereadytomovebeyondtheMySQLbasics,orwhohavenotkeptuptodatewiththeirMySQLandPHP,willalsobenefitfromPracticalPHPandMySQLWebSiteDatabases-ASimplifiedApproach.
CollegeanduniversityITteacherswillfindthatthebookprovidesanexcellentsettext;theprojectscanformabasisforstudentstoadaptfortheircoursework.
The"QuickandEasy-to-Learn"MythManualsfrequentlystatethatPHPandMySQLdatabasesareeasilyandquicklylearned.
Thisdiscouragesbeginners,becausewhentheyareconfrontedwiththeinevitabledifficulties(anderrormessages),theybegintothinkthattheywillnevergraspeventhebasicprinciples.
Beginnersshouldnotbediscouragediftheyrememberthefollowingfact:authorsclaimingthatPHPandMySQLareeasilyandquicklylearnedarenotbeingdeceptive;theyhaveprobablybeenusingPHPandMySQLformorethanadecadeandhaveforgottenthedifficultiestheyencounteredwhentheyfirstbegan.
IfyouacceptthatsometimeandeffortisrequiredtolearnPHPandMySQL,thenastimepasses,itwillbecomeincreasinglyapparentthatyouarelearningsomethingveryworthwhile.
Thetaskwillbecomeprogressivelyeasier,sohavepatienceandpersevere.
Youwillthenbegintoenjoymasteringthisvaluablenewdiscipline.
TheOriginofThisBookIwasaskedtoenhanceoneofmyclient'swebsitesbyaddingamembershipdatabaseandamembers'registrationform.
AlthoughIhavedesigned,developed,andmaintainedwebsitesformanyyears,IhadnoknowledgeofMySQLdatabases.
IboughtandborrowedaboatloadofbooksandsearchedtheInternetfortutorials.
Iwasverydisappointedwiththemajorityofthebooks.
MostoftheMySQLmanualstendedtodemonstratetheauthor'sdeepandextensiveknowledgeofPHPandMySQLinsteadofteachinghowtoembedMySQLdatabasesintowebpages.
Incontrast,thisbookusesfullyworkedexamplestodemonstratehowtointegratedatabasesintoawebsite.
PHP/MySQLmanualscanhaveupto800pages,whichwoulddeteranybeginner.
Ionceboughtsuchamanual.
Itcontainedhardlyanypracticalworkedexamples.
ThismeantthatIcouldonlyusethebooktostandonwhenchangingalightbulb.
(Themanualwasalmost2inchesthick.
)IntroductionxxviiThemanyMySQLdatabasemanualsthatIown(orborrowed)wereunnecessarilycomplicated.
Theauthorshadbecomeusedtousingneattricksandshortcutsthatweresecondnaturetothem.
Theseclutteredthecode,makingitdifficultforbeginnerstodiscernthebasicstructure.
PracticalPHPandMySQLWebSiteDatabases-ASimplifiedApproachavoidsthismistake.
AfewusefultricksareintroducedgraduallyandarefullyexplainedinplainEnglish.
IbasedthebookonaquotefromthecomposerBrahms,whosaid:Itiseasytocomposebutwonderfullyhardtoletthesuperfluousnotesfallunderthetable.
MySQLmanualsarenearlyalwayswrittenassumingthatthewebdesignerwilladministerthedatabases.
Smallere-commercewebsites,clubs,andsocietiescannotaffordtodothisandwouldpreferthattheirmembershipsecretarywasabletoadministerthedatabaseusingauser-friendlyinterface.
Ofcourse,thewebdesignershouldalwaysbeavailableformajoradministrativejobs,suchasaddinganewcolumnoratable.
Themajorityofthedatabasescreatedinthisbookcanbeadministeredbybothanunskilledmembershipsecretaryandthewebdesigner.
SomemanualsprovideinstructionsusingonlyMS-DOSstylecommand-lines.
HavingusedaGUI(mouse-operatedsystem)fromthestartoftheircomputingexperience,anyoneundertheageof40wouldnotknowwhatanMS-DOScommandlinewas.
Somemanualspublishedin2012stillusecommand-linelistings.
WhatwouldabeginnermakeofthesortofcodeshowninFigure1Figure1.
Anexampleofthecommand-linecodethatwouldfrightenmostbeginnersSometimesIhadasmanyassevenMySQL/PHPmanualsopenatthesametimetopiecetogetherenoughinformationtocompleteasimpletask.
Inparallel,IranInternetsearchestosupplementthemostobscuremanuals;sadly,someforumstendedtodealmorewithpaint-by-numbers(CMS)websitesratherthanHTMLwebsites.
Onlytwoofthesevenmanualstookthetroubletoembeditsdatabasesintoreal-worldwebpages.
Eventually,IconcludedthatImustwritemyownmanualbasedonwhatIcouldlearnbyconcatenatingsnippetsofinformationfrommultipleresources.
Ialsobasedthemanualonmyowntrial-and-errorapproachasarawbeginner.
Thisautomaticallyensuredthatthemanual'scontentwaspresentedinsimple,logical,andprogressivestepswithoutsuddenlyintroducingunexplaineditems.
Myhome-grownmanualwassousefulthatIdecidedthatitshouldbesharedwithotherwebsitedesigners.
Thisbookistheresultofthatdecision.
Computersoftwareanddatabasetechniquesareconstantlyimprovingandbeingupdated.
Becauseofthis,mostoftheavailablemanualsandInternettutorialswereobsolescent,soIhadtoresearchthelatestversionsofthescripts,tools,andtheavailablesoftware.
Thisensuredthatmycontentandillustrationswouldremainrelevantforaslongaspossible.
Tofollowthetutorialsinthisbookrequiresanabsoluteminimumofsoftware.
Somemanualsaskreaderstodownloadandlearnanewpieceofsoftwarebeforetheycanproceedtoeachnewchapter.
IcameacrossonebookthatrequiredreaderstodownloadMySQL,Apache,PHP,phpMyAdmin,Prototype1.
5,Scriptaculous,ZendFramework,SmartyTemplateEngine,FCKeditor,Jquery,andAjax.
Inthisbook,inadditiontoacodeeditor,Ihavelimitedthesoftwaretooneitemasdescribednext.
IntroductionxxviiiWhatEquipmentIsRequiredThebookassumesthat,asawebdesigner,youwillalreadyhaveanHTMLeditorsuchasDreamweaver,MSExpressionWeb(nowfree),Kompozer(free),orNotePad++(free).
IusedMSExpressionWebbecauseitwasaboutonethirdofthepriceofDreamweaveranditusedaninterfacesimilartoMSWord.
IwasconsideringanupdatetomyExpressionWeb,butitwouldcostabout199,whichdeterredme.
Then,suddenly,MicrosoftdecidedtodiscontinuethedevelopmentandmaintenanceofExpressionWebandoffereditfreeofcharge.
Naturally,IwasdelightedandpromptlydownloadedthelatestversionofExpressionWebVersion4;Icanthoroughlyrecommendit.
Inadditionyouwillneed:Anotebookforrecordingthepasswordsandfilenamesforyourdatabasesandtableentries.
DON'TRELYONMEMORY;WRITEEVERYTHINGDOWNYouwillneedtodownload:Thesamplecodefromthebook'spage,availableatwww.
apress.
com.
XAMPP,afree,all-in-onepackagefortestingyourwork.
Thelatestbrowsers(allfree):InternetExplorer,MozillaFirefox,Safari,Chrome,andOpera.
TheConventionsUsedinThisBookCarehasbeentakentorelateeverylistingtoitsscreenshot.
Forinstance,Figure3-6willbedescribedbyListing3-6.
Iftwolistingsareneeded,suchastheHTMLcodeandtheCSS,bothwillrelatetothescreenshotbyusingListing3-6aandListing3-6b.
IfascreenshotsuchasFigure4-6doesnotneedalisting,thenextscreenshotandlistingwilluseFigure4-7andListing4-7.
Specialtips,notes,andwarningsareshowninthefollowingformat:NoteSecurityisveryimportantwhendealingwithdatabases,especiallyiftheycontainpersonaldata.
Thetechniqueformakingyourworksecureiswovenintoeachstepoftheinstructions.
AllcodelistingsuseHTML5andPHP.
Themetadescriptionandmetakeywordshavebeenomittedfromeachsectiontosavespace.
Codelistingsareshownasfollows:Codeshowninboldtypeindicateseitheranewfeatureorachangefromapreviousversionofthecode.
Codelinesaresometimesnumberedtohelpwiththeexplanationsasfollows:if(empty($errors)){//Ifnoproblemsoccured,registertheuserinthedatabase#1Thelinenumbersareforexplanationonlyandmustnotbeincludedinyourowncode.
IntroductionxxixInteractivevs.
DynamicMostmanualsusetheterm"dynamic"webpageswhenreferringtointeractivepages.
Thewordsdynamicandinteractivebothdescribepagesthatprovidealivelinkbetweenauserandadatabase.
Forinstance,ausercanregisterformembershipandviewhis/heraccountdetails.
Amembershipsecretarycanviewatableofmembers,butthetableishiddenfromordinarymembers.
Becausetheworddynamiccanhavesomanyconnotationsandmeanings,Ihavechosentousethemorepreciseterminteractiveinthisbook.