Practical PHP 7, MySQL 8, and MariaDB Website Databases - A Simplified Approach to Developing Database-Driven Websites

Contents

About the Authors

Acknowledgments

Introduction

Chapter 1: Create and Test a Database and Table

Defining Developer, Administrator, and User

Defining Interactive Websites

Using MariaDB or MySQL Only for Interactive Database Tables

Methods for Developing and Maintaining Databases

A Brief Look Inside Web Server Communication

A Free Development Platform for Testing

Using XAMPP on Your Own Computer

Will I Be Able to Transfer the Database from XAMPP or EasyPHP to a Remote Host?

Downloading and Installing XAMPP

Starting XAMPP

Closing XAMPP

Where Is MariaDB and MySQL 8?

Using EasyPHP on Your Own Computer

Download and Install EasyPHP

Starting EasyPHP

Closing EasyPHP

phpMyAdmin Security

Accessing phpMyAdmin Directly

The Familiar Bits

Planning a Database: The Essential First Step

Creating a Database Using phpMyAdmin

Create a Table Using phpMyAdmin

The SQL Alternative

Deleting Databases and Tables

Summary

Chapter 2: Create Web Pages That Interact with Users

Creating the Folder for Holding the Database Pages

Creating the Temporary Template

Introducing the PHP include() Function

The Included Header File

The Included Menu File

The Included Information Column

The Included Footer File

How Does the Server Process the Page?

The Interactive Version of the Template

Connecting to the Database

Explanation of the Code

The Registration Page

Explanation of the Code

Explanation of the Code

The PHP Keyword echo

The “Thank You” Page

Displaying Error Messages That Are Collected in an Array

Hashing the Password

Viewing Members’ Records

The View Users Page

Explanation of the Code

The Change Password Page

Explanation of the Code

Explanation of the Code

Confirming a Successful Password Change

Testing the Tutorial’s Pages

More About Arrays

Summary

Chapter 3: Create Login/Logout Functionality for Members and an Administrator

Creating the logindb Database and users Table

Removing or Replacing Redundant Menu Buttons in the Headers

Adding a Login Button to the Home Page Header

Removing Redundant Buttons from the Registration and New Password Headers

The Revised Registration Page

The New Header for the New Password Page

A New Header Menu for the Members page

Amend the Header for the “Thank You” Page

The Registration Page and Undesirable Characters

Explanation of the Code

Registering Some Members

Differentiating Between Two Types of Membership

Creating User Levels to Limit Access to Private Pages

Log In

The Header for the Login Page

The Login Page

Explanation of the Code

Explanation of the Code

Sessions

A Members-Only Page

Explanation of the Code

Planning the Administrator’s Role

A New Header for the Administration Page

The Administrator’s Page

Explanation of the Code

The Logout Page

Explanation of the Code

Testing the Login/Logout Function

Amending and Deleting Individual Records

Summary

Chapter 4: Create an Administration Interface

The Administration Database

The Users Table

Revising the View Users Page to Include Editing and Deleting

Explanation of the Code

Displaying Pages of Records (Pagination)

Explanation of the Code

Planning the Search Criteria

A Temporary Search Page for Displaying Specified Members

Explanation of the Code

The Search Form

Explanation of the Code

The Final Form Handler for Receiving Search Form Input

Explanation of the Code

Editing Records

Explanation of the Code

Deleting Records

Explanation of the Code

Summary

Chapter 5: Expand and Enrich Your Website

Creating a New Database, a Table with 15 Columns, and a Price Table

Creating the File for Connecting to the Database

Creating the Tables

Using ENUM

The Importance of Documentation

Extending the Registration Form and Adding a Pull-Down Menu

Always Announce Prices and Fee Payments Up-Front

Explanation of the Code

Explanation of the Code

Adding PayPal Debit/Credit Card Images

Including PayPal on the “Thank You” Page

Explanation of the Code

Registering Some Members

A Small Amendment to the Login Page

Amending the Administrator’s Header

Adding Class and Paid to the admin_view_users Table

Explanation of the Code

Searching and Editing Records

Explanation of the Code

Modifying the Form for Editing Records

Summary

Chapter 6: Add the Finishing Touches: Security and Validation

Creating the Database

Creating the File for Connecting to the Database

Creating the users Table by Importing a SQL Dump File

Registering Some Members Manually

Adding a Title Column to the users Table

Creating the Prices Table by Importing a SQL Dump File

Tidying Up the Folders and Filing System

Degrees of Security

Adding a Layer of Security

An Increased Layer of Protection

Validation and Sanitization

The filter_var() Function

Validation

Sanitization

Validating Telephone Numbers

A Safer Registration Page

Explanation of the Code

Explanation of the Code

Searching for a Title, an Address, or a Telephone Number

Explanation of the Code

Viewing the Retrieved Title, Address, and Phone Number

Explanation of the Code

Editing the Title, Addresses, and Telephone Numbers

Explanation of the Code

Summary

Chapter 7: Migrate to a Host and Back Up Your Website Database

Making Last-Minute Changes

Creating a New Database

Details of the Downloaded File for Connecting to the Database

Allowing Members to Update Their Own Records

Explanation of the Code

A Secure Feedback Form

What Does a Feedback Reply Look Like?

The Feedback Form

Explanation of the Code

Explanation of the Code

The “Thank You” Page and the Error Messages

A Common Header

Explanation of the Code

Logging Exceptions and Error

Migrating the Database and Tables to a Remote Host

A Puzzling Error Message

Creating and Exporting the SQL File

To Create a Dump File of a Table or Tables

What Does a SQL Dump Look Like?

Investigate the Remote Host’s Server

Using the GUIs on a Remote Host’s Server

Connecting to the Database on the Remote Host

Securely Uploading the mysqli_connection.php File

Uploading the Interactive Pages to the Host

Backing Up Your Database

Summary

Chapter 8: Create a Product Catalog

Preparing the Database and Administration Plan

Creating a New Database

Creating the File for Connecting to the Database

Security

Creating a Home Page with Search Capability

The Header for the Majority of the Pages

The Home Page Code

Displaying the Catalog

Explanation of the Code

The Header for the Page of Search Results

Creating the House Details Page

Explanation of Code

Creating the Admin/Adding a House Page

The Header for the Administrator’s Page

Administrator’s View of the Entire Stock of Houses for Sale

The Administrator’s Search Page

The Result of a Search

The Contact Us Page

Summary

Chapter 9: Join Multiple Tables and Other Enhancements

Introduction to Multiple Tables

Normalization

Creating the Database and Tables

Viewing the Connection File

Creating a Second Table

Foreign Keys

Preparing the Tables for Joining

Populating the Two Tables

Joining Data from the Two Tables

Creating the Third Table

Creating Pages to Display the Data from Your Joined Tables

The Home Page

The Main Menu for the Pages

The Header for All the Pages

The Page for Viewing the Birds

Explanation of the Code

The Page for Viewing the Locations and Habitats of the Reserves

Displaying Data from the Joined Tables

Creating a Page to Display the Three Joined Tables

Explanation of the Code

Payments by Check

A Choice of Payment Method

The Check Payment

Explanation of the Code

Printing Online Forms

Summary

Chapter 10: Create a Message Board

The Plan

Creating the Database

Creating the Tables

Creating the Second Table

Creating the Registration Form

The “Thank You” Page

Populating the Members Table

The Login Page

Explanation of the Code

Logging Out

Creating a Gateway to a Choice of Quotes

The Form for Posting Quotations

Explanation of the Code

Processing the Postings

Explanation of the Code

Posting Some Quotations

The Comical Quotes Page

Explanation of the Code

The Header for the Comical Quotes Page

The Wise Quotes Page

Explanation of the Code

The Header for the Wise Quotes Page

Adding Search Facilities

Explanation of the Code

The Header for ViewPosts.php

Searching for Specific Words or Phrases

The Full Text Search Form

Displaying the Search Results

Explanation of the Code

The Header for the quotes_found Page

Enhancing the Message Board

Converting the Message Board to a Forum

Summary

Chapter 11: E-commerce: A Brief Introduction

Security Warning

The Plan

Creating the PayPal Cart Site

Creating the PayPal Cart Database and Tables

Viewing the Connection File

Populating the PayPal Cart Tables

The PayPal Cart Home Page

Using the PayPal Cart Home Page to Search for Paintings

Explanation of the Code

Integrating with the PayPal Shopping Cart Buttons

Explanation of the Code

Creating a Custom Cart

The Custom Cart Home Page

Create the Custom Cart Database and Tables

Exploring the Custom Shopping Cart

The Custom Cart Login Page

Explanation of the Code

Retrieving a Forgotten Password

Explanation of the Code

The Custom Cart Search Page

Adding Paintings to a Table for a Custom Shopping Cart

Explanation of the Code

Explanation of the Code

Explanation of the Code

The Checkout Page

The Additional Administrative Tasks

Summary

Chapter 12: Take a Brief Look at Oracle MySQL 8

Advantages of Upgrading

Installing MySQL 8 Community Server

Exploring the Features of MySQL Workbench

Connecting PHP 7 to the MySQL 8 Community Server

Migrating to MySQL 8 Community Server

Using Our PHP Files with MySQL 8 Community Server

Summary

Appendix A: Troubleshooting

Browser Quirks

Tables Not Displaying

A Style Change Has No Effect

Included Items Missing from the Display

A Page Fails to Validate

A PayPal Pull-Down Menu Does Not Work

Access Denied

PHP Error Levels

Call to an Undefined Function

Cannot Redeclare Function

Undefined Index or Undefined Variable

Empty Variable Value

Headers Already Sent

Blank Screen

Unexpected End of File in Line xxx

Parse Error and Unexpected Characters

Unexpected T_STRING

Unexpected T_ELSE

Wrong Equal Sign

Failed to Open Stream

Syntax Errors

Warning: Division by Zero

Display Is Not What Was Expected

Reference to a Primary Key Could Not Be Created

Element