book

PHP and MySQL® Web Development, Fifth Edition

by Luke Welling, Laura Thomson

September 2016

Intermediate to advanced

688 pages

19h 38m

English

Addison-Wesley Professional

Read now

Unlock full access

Why You Should Read This BookWhat You Will Learn from This BookWhat Is PHP?What Is MySQL?Why Use PHP and MySQL?Some of PHP’s StrengthsPerformanceScalabilityDatabase IntegrationBuilt-in LibrariesCostEase of Learning PHPObject-Oriented SupportPortabilityFlexibility of Development ApproachSource CodeAvailability of Support and DocumentationKey Features of PHP 7Some of MySQL’s StrengthsPerformanceLow CostEase of UsePortabilitySource CodeAvailability of SupportWhat Is New in MySQL (5.x)?How Is This Book Organized?Accessing the Free Web EditionFinally
Before You Begin: Accessing PHPCreating a Sample Application: Bob’s Auto PartsCreating the Order FormProcessing the FormEmbedding PHP in HTMLPHP TagsPHP StatementsWhitespaceCommentsAdding Dynamic ContentCalling FunctionsUsing the date() FunctionAccessing Form VariablesForm VariablesString ConcatenationVariables and LiteralsUnderstanding IdentifiersExamining Variable TypesPHP’s Data TypesType StrengthType CastingVariable VariablesDeclaring and Using ConstantsUnderstanding Variable ScopeUsing OperatorsArithmetic OperatorsString OperatorsAssignment OperatorsComparison OperatorsLogical OperatorsBitwise OperatorsOther OperatorsWorking Out the Form TotalsUnderstanding Precedence and AssociativityUsing Variable Handling FunctionsTesting and Setting Variable TypesTesting Variable StatusReinterpreting VariablesMaking Decisions with Conditionalsif StatementsCode Blockselse Statementselseif Statementsswitch StatementsComparing the Different ConditionalsRepeating Actions Through Iterationwhile Loopsfor and foreach Loopsdo...while LoopsBreaking Out of a Control Structure or ScriptEmploying Alternative Control Structure SyntaxUsing declareNext
Saving Data for LaterStoring and Retrieving Bob’s OrdersProcessing FilesOpening a FileChoosing File ModesUsing fopen() to Open a FileOpening Files Through FTP or HTTPAddressing Problems Opening FilesWriting to a FileParameters for fwrite()File FormatsClosing a FileReading from a FileOpening a File for Reading: fopen()Knowing When to Stop: feof()Reading a Line at a Time: fgets(), fgetss(), and fgetcsv()Reading the Whole File: readfile(), fpassthru(), file(), and file_get_contents()Reading a Character: fgetc()Reading an Arbitrary Length: fread()Using Other File FunctionsChecking Whether a File Is There: file_exists()Determining How Big a File Is: filesize()Deleting a File: unlink()Navigating Inside a File: rewind(), fseek(), and ftell()Locking FilesA Better Way: DatabasesProblems with Using Flat FilesHow RDBMSs Solve These ProblemsFurther ReadingNext
What Is an Array?Numerically Indexed ArraysInitializing Numerically Indexed ArraysAccessing Array ContentsUsing Loops to Access the ArrayArrays with Different IndicesInitializing an ArrayAccessing the Array ElementsUsing LoopsArray OperatorsMultidimensional ArraysSorting ArraysUsing sort()Using asort() and ksort() to Sort ArraysSorting in ReverseSorting Multidimensional ArraysUsing the array_multisort() functionUser-Defined SortsReverse User SortsReordering ArraysUsing shuffle()Reversing an ArrayLoading Arrays from FilesPerforming Other Array ManipulationsNavigating Within an Array: each(), current(), reset(), end(), next(), pos(), and prev()Applying Any Function to Each Element in an Array: array_walk()Counting Elements in an Array: count(), sizeof(), and array_count_values()Converting Arrays to Scalar Variables: extract()Further ReadingNext
Creating a Sample Application: Smart Form MailFormatting StringsTrimming Strings: chop(), ltrim(), and trim()Formatting Strings for OutputJoining and Splitting Strings with String FunctionsUsing explode(), implode(), and join()Using strtok()Using substr()Comparing StringsPerforming String Ordering: strcmp(), strcasecmp(), and strnatcmp()Testing String Length with strlen()Matching and Replacing Substrings with String FunctionsFinding Strings in Strings: strstr(), strchr(), strrchr(), and stristr()Finding the Position of a Substring: strpos() and strrpos()Replacing Substrings: str_replace() and substr_replace()Introducing Regular ExpressionsThe BasicsDelimitersCharacter Classes and TypesRepetitionSubexpressionsCounted SubexpressionsAnchoring to the Beginning or End of a StringBranchingMatching Literal Special CharactersReviewing Meta CharactersEscape SequencesBackreferencesAssertionsPutting It All Together for the Smart FormFinding Substrings with Regular ExpressionsReplacing Substrings with Regular ExpressionsSplitting Strings with Regular ExpressionsFurther ReadingNext
The Advantages of Reusing CodeCostReliabilityConsistencyUsing require() and include()Using require() to Include CodeUsing require() for Website TemplatesUsing auto_prepend_file and auto_append_fileUsing Functions in PHPCalling FunctionsCalling an Undefined FunctionUnderstanding Case and Function NamesDefining Your Own FunctionsExamining Basic Function StructureNaming Your FunctionUsing ParametersUnderstanding ScopePassing by Reference Versus Passing by ValueUsing the return KeywordReturning Values from FunctionsImplementing RecursionImplementing Anonymous Functions (or Closures)Further ReadingNext
Understanding Object-Oriented ConceptsClasses and ObjectsPolymorphismInheritanceCreating Classes, Attributes, and Operations in PHPStructure of a ClassConstructorsDestructorsInstantiating ClassesUsing Class AttributesCalling Class OperationsControlling Access with private and publicWriting Accessor FunctionsImplementing Inheritance in PHPControlling Visibility Through Inheritance with private and protectedOverridingPreventing Inheritance and Overriding with finalUnderstanding Multiple InheritanceImplementing InterfacesUsing TraitsDesigning ClassesWriting the Code for Your ClassUnderstanding Advanced Object-Oriented Functionality in PHPUsing Per-Class ConstantsImplementing Static MethodsChecking Class Type and Type HintingLate Static BindingsCloning ObjectsUsing Abstract ClassesOverloading Methods with __call()Using __autoload()Implementing Iterators and IterationGeneratorsConverting Your Classes to StringsUsing the Reflection APINamespacesUsing SubnamespacesUnderstanding the Global NamespaceImporting and Aliasing NamespacesNext
Exception Handling ConceptsThe Exception ClassUser-Defined ExceptionsExceptions in Bob’s Auto PartsExceptions and PHP’s Other Error Handling MechanismsFurther ReadingNext
Relational Database ConceptsTablesColumnsRowsValuesKeysSchemasRelationshipsDesigning Your Web DatabaseThink About the Real-World Objects You Are ModelingAvoid Storing Redundant DataUse Atomic Column ValuesChoose Sensible KeysThink About What You Want to Ask the DatabaseAvoid Designs with Many Empty AttributesSummary of Table TypesWeb Database ArchitectureFurther ReadingNext
Using the MySQL MonitorLogging In to MySQLCreating Databases and UsersSetting Up Users and PrivilegesIntroducing MySQL’s Privilege SystemPrinciple of Least PrivilegeUser Setup: The CREATE USER and GRANT CommandsTypes and Levels of PrivilegesThe REVOKE CommandExamples Using GRANT and REVOKESetting Up a User for the WebUsing the Right DatabaseCreating Database TablesUnderstanding What the Other Keywords MeanUnderstanding the Column TypesLooking at the Database with SHOW and DESCRIBECreating IndexesUnderstanding MySQL IdentifiersChoosing Column Data TypesNumeric TypesDate and Time TypesString TypesFurther ReadingNext
What Is SQL?Inserting Data into the DatabaseRetrieving Data from the DatabaseRetrieving Data with Specific CriteriaRetrieving Data from Multiple TablesRetrieving Data in a Particular OrderGrouping and Aggregating DataChoosing Which Rows to ReturnUsing SubqueriesUpdating Records in the DatabaseAltering Tables After CreationDeleting Records from the DatabaseDropping TablesDropping a Whole DatabaseFurther ReadingNext
How Web Database Architectures WorkQuerying a Database from the WebChecking and Filtering Input DataSetting Up a ConnectionChoosing a Database to UseQuerying the DatabaseUsing Prepared StatementsRetrieving the Query ResultsDisconnecting from the DatabasePutting New Information in the DatabaseUsing Other PHP-Database InterfacesUsing a Generic Database Interface: PDOFurther ReadingNext
Understanding the Privilege System in DetailThe user TableThe db TableThe tables_priv, columns_priv, and procs priv TablesAccess Control: How MySQL Uses the Grant TablesUpdating Privileges: When Do Changes Take Effect?Making Your MySQL Database SecureMySQL from the Operating System’s Point of ViewPasswordsUser PrivilegesWeb IssuesGetting More Information About DatabasesGetting Information with SHOWGetting Information About Columns with DESCRIBEUnderstanding How Queries Work with EXPLAINOptimizing Your DatabaseDesign OptimizationPermissionsTable OptimizationUsing IndexesUsing Default ValuesOther TipsBacking Up Your MySQL DatabaseRestoring Your MySQL DatabaseImplementing ReplicationSetting Up the MasterPerforming the Initial Data TransferSetting Up the Slave or SlavesFurther ReadingNext
The LOAD DATA INFILE StatementStorage EnginesTransactionsUnderstanding Transaction DefinitionsUsing Transactions with InnoDBForeign KeysStored ProceduresBasic ExampleLocal VariablesCursors and Control StructuresTriggersFurther ReadingNext
Identifying the Threats We FaceAccess to Sensitive DataModification of DataLoss or Destruction of DataDenial of ServiceMalicious Code InjectionCompromised ServerRepudiationUnderstanding Who We’re Dealing WithAttackers and CrackersUnwitting Users of Infected MachinesDisgruntled EmployeesHardware ThievesOurselvesNext
Strategies for Dealing with SecurityStart with the Right MindsetBalancing Security and UsabilityMonitoring SecurityOur Basic ApproachSecuring Your CodeFiltering User InputEscaping OutputCode OrganizationWhat Goes in Your CodeFile System ConsiderationsCode Stability and BugsExecuting CommandsSecuring Your Web Server and PHPKeep Software Up-to-DateBrowse the php.ini fileWeb Server ConfigurationShared Hosting of Web ApplicationsDatabase Server SecurityUsers and the Permissions SystemSending Data to the ServerConnecting to the ServerRunning the ServerProtecting the NetworkFirewallsUse a DMZPrepare for DoS and DDoS AttacksComputer and Operating System SecurityKeep the Operating System Up to DateRun Only What Is NecessaryPhysically Secure the ServerDisaster PlanningNext
Identifying VisitorsImplementing Access ControlStoring PasswordsSecuring PasswordsProtecting Multiple PagesUsing Basic AuthenticationUsing Basic Authentication in PHPUsing Basic Authentication with Apache’s .htaccess FilesCreating Your Own Custom AuthenticationFurther ReadingNext
Uploading FilesHTML for File UploadWriting the PHP to Deal with the FileSession Upload ProgressAvoiding Common Upload ProblemsUsing Directory FunctionsReading from DirectoriesGetting Information About the Current DirectoryCreating and Deleting DirectoriesInteracting with the File SystemGetting File InformationChanging File PropertiesCreating, Deleting, and Moving FilesUsing Program Execution FunctionsInteracting with the Environment: getenv() and putenv()Further ReadingNext
Examining Available ProtocolsSending and Reading EmailUsing Data from Other WebsitesUsing Network Lookup FunctionsBacking Up or Mirroring a FileUsing FTP to Back Up or Mirror a FileUploading FilesAvoiding TimeoutsUsing Other FTP FunctionsFurther ReadingNext
Getting the Date and Time from PHPUnderstanding TimezonesUsing the date() FunctionDealing with Unix TimestampsUsing the getdate() FunctionValidating Dates with checkdate()Formatting TimestampsConverting Between PHP and MySQL Date FormatsCalculating Dates in PHPCalculating Dates in MySQLUsing MicrosecondsUsing the Calendar FunctionsFurther ReadingNext
Localization Is More than TranslationUnderstanding Character SetsSecurity Implications of Character SetsUsing Multibyte String Functions in PHPCreating a Basic Localizable Page StructureUsing gettext() in an Internationalized ApplicationConfiguring Your System to Use gettext()Creating Translation FilesImplementing Localized Content in PHP Using gettext()Further ReadingNext
Setting Up Image Support in PHPUnderstanding Image FormatsJPEGPNGGIFCreating ImagesCreating a Canvas ImageDrawing or Printing Text on the ImageOutputting the Final GraphicCleaning UpUsing Automatically Generated Images in Other PagesUsing Text and Fonts to Create ImagesSetting Up the Base CanvasFitting the Text onto the ButtonPositioning the TextWriting the Text onto the ButtonFinishing UpDrawing Figures and Graphing DataUsing Other Image FunctionsNext
What Is Session Control?Understanding Basic Session FunctionalityWhat Is a Cookie?Setting Cookies from PHPUsing Cookies with SessionsStoring the Session IDImplementing Simple SessionsStarting a SessionRegistering Session VariablesUsing Session VariablesUnsetting Variables and Destroying the SessionCreating a Simple Session ExampleConfiguring Session ControlImplementing Authentication with Session ControlNext
Understanding AJAXA Brief Introduction to jQueryUsing jQuery in Web ApplicationsBasic jQuery Techniques and ConceptsUsing jQuery SelectorsIntroduction to jQuery EventsUsing jQuery and AJAX with PHPThe AJAX-Enabled Chat Script/ServerThe jQuery AJAX MethodsThe Chat Client/jQuery ApplicationFurther ReadingNext
Evaluating Strings: eval()Terminating Execution: die() and exit()Serializing Variables and ObjectsGetting Information About the PHP EnvironmentFinding Out What Extensions Are LoadedIdentifying the Script OwnerFinding Out When the Script Was ModifiedTemporarily Altering the Runtime EnvironmentHighlighting Source CodeUsing PHP on the Command LineNext
Applying Software Engineering to Web DevelopmentPlanning and Running a Web Application ProjectReusing CodeWriting Maintainable CodeCoding StandardsBreaking Up CodeUsing a Standard Directory StructureDocumenting and Sharing In-House FunctionsImplementing Version ControlChoosing a Development EnvironmentDocumenting Your ProjectsPrototypingSeparating Logic and ContentOptimizing CodeUsing Simple OptimizationsTestingFurther ReadingNext
Programming ErrorsSyntax ErrorsRuntime ErrorsLogic ErrorsVariable Debugging AidError Reporting LevelsAltering the Error Reporting SettingsTriggering Your Own ErrorsLogging Errors GracefullyLogging Errors to a Log FileNext
Solution ComponentsUser Identification and PersonalizationStoring BookmarksRecommending BookmarksSolution OverviewImplementing the DatabaseImplementing the Basic SiteImplementing User AuthenticationRegistering UsersLogging InLogging OutChanging PasswordsResetting Forgotten PasswordsImplementing Bookmark Storage and RetrievalAdding BookmarksDisplaying BookmarksDeleting BookmarksImplementing RecommendationsConsidering Possible Extensions
Installing Apache, PHP, and MySQL Under UNIXBinary InstallationSource InstallationBasic Apache Configuration ModificationsIs PHP Support Working?Is SSL Working?Installing Apache, PHP, and MySQL for Windows and Mac OS X Using All-in-One Installation PackagesTesting Your WorkInstalling PEARInstalling PHP with Other Web Servers

Content preview from PHP and MySQL® Web Development, Fifth Edition

11. Accessing Your MySQL Database from the Web with PHP

Previously, in our work with PHP, we used a flat file to store and retrieve data. When we looked at this file in Chapter 2, “Storing and Retrieving Data,” we mentioned that relational database systems make a lot of these storage and retrieval tasks easier, safer, and more efficient in a web application. Now, having worked with MySQL to create a database, we can begin connecting this database to a web-based front end.

In this chapter, we explain how to access the Book-O-Rama database from the web using PHP. You learn how to read from and write to the database and how to filter potentially troublesome input data.

Key topics covered in this chapter include

How web database architectures work ...