Practical PHP 7, MySQL 8, and MariaDB Website Databases (eBook)

Adrian W. West is an active web design and development consultant at amwebdesign.co.uk.  He also writes monthly computer help articles in two local magazines. Adrian is the author of Practical HTML5 Projects, also published by Apress. Frustrated by the over-complicated MySQL manuals that assumed the reader would fill in the many gaps in the instructions, he researched, tested and developed a fully explained and simplified method to eliminate the difficulties he experienced; this book is the result.Steve Prettyman earned his bachelor of arts degree in education from Oglethorpe University in 1979. He quickly began his teaching career as a high school mathematics instructor while continuing his education by earning a master’s degree in business information systems from Georgia State University (1985). Since then, Steve has spent over 30 years in the IT industry. The last, almost 20 years, he has been an instructor and professor at Chattahoochee Technical College, Kennesaw State University, and Southern Polytechnic State University. He is currently the Computer Science Department Chair for Florida Keys Community College, Key West, Florida. His primary teaching responsibilities include programming, web design, and web application development.

Contents 4
About the Authors 14
Acknowledgments 15
Introduction 16
Chapter 1: Create and Test a Database and Table 20
Defining Developer, Administrator, and User 21
Defining Interactive Websites 21
Using MariaDB or MySQL Only for Interactive Database Tables 22
Methods for Developing and Maintaining Databases 22
A Brief Look Inside Web Server Communication 22
A Free Development Platform for Testing 24
Using XAMPP on Your Own Computer 24
Will I Be Able to Transfer the Database from XAMPP or EasyPHP to a Remote Host? 25
Downloading and Installing XAMPP 25
Starting XAMPP 28
Closing XAMPP 29
Where Is MariaDB and MySQL 8? 30
Using EasyPHP on Your Own Computer 31
Download and Install EasyPHP 31
Starting EasyPHP 31
Closing EasyPHP 32
phpMyAdmin Security 32
Accessing phpMyAdmin Directly 36
The Familiar Bits 37
Planning a Database: The Essential First Step 39
Creating a Database Using phpMyAdmin 40
Create a Table Using phpMyAdmin 43
The SQL Alternative 46
Deleting Databases and Tables 49
Summary 50
Chapter 2: Create Web Pages That Interact with Users 51
Creating the Folder for Holding the Database Pages 52
Creating the Temporary Template 52
Introducing the PHP include() Function 54
The Included Header File 55
The Included Menu File 57
The Included Information Column 58
The Included Footer File 59
How Does the Server Process the Page? 60
The Interactive Version of the Template 60
Connecting to the Database 62
Explanation of the Code 63
The Registration Page 65
Explanation of the Code 68
Explanation of the Code 72
The PHP Keyword echo 76
The “Thank You” Page 76
Displaying Error Messages That Are Collected in an Array 78
Hashing the Password 79
Viewing Members’ Records 79
The View Users Page 79
Explanation of the Code 82
The Change Password Page 83
Explanation of the Code 85
Explanation of the Code 87
Confirming a Successful Password Change 90
Testing the Tutorial’s Pages 90
More About Arrays 91
Summary 92
Chapter 3: Create Login/Logout Functionality for Members and an Administrator 93
Creating the logindb Database and users Table 94
Removing or Replacing Redundant Menu Buttons in the Headers 95
Adding a Login Button to the Home Page Header 96
Removing Redundant Buttons from the Registration and New Password Headers 97
The Revised Registration Page 97
The New Header for the New Password Page 98
A New Header Menu for the Members page 100
Amend the Header for the “Thank You” Page 100
The Registration Page and Undesirable Characters 102
Explanation of the Code 104
Registering Some Members 105
Differentiating Between Two Types of Membership 106
Creating User Levels to Limit Access to Private Pages 106
Log In 109
The Header for the Login Page 109
The Login Page 110
Explanation of the Code 112
Explanation of the Code 114
Sessions 117
A Members-Only Page 119
Explanation of the Code 121
Planning the Administrator’s Role 122
A New Header for the Administration Page 123
The Administrator’s Page 124
Explanation of the Code 125
The Logout Page 125
Explanation of the Code 126
Testing the Login/Logout Function 127
Amending and Deleting Individual Records 127
Summary 128
Chapter 4: Create an Administration Interface 129
The Administration Database 129
The Users Table 131
Revising the View Users Page to Include Editing and Deleting 133
Explanation of the Code 136
Displaying Pages of Records (Pagination) 139
Explanation of the Code 143
Planning the Search Criteria 146
A Temporary Search Page for Displaying Specified Members 146
Explanation of the Code 149
The Search Form 150
Explanation of the Code 152
The Final Form Handler for Receiving Search Form Input 153
Explanation of the Code 155
Editing Records 157
Explanation of the Code 160
Deleting Records 163
Explanation of the Code 166
Summary 168
Chapter 5: Expand and Enrich Your Website 169
Creating a New Database, a Table with 15 Columns, and a Price Table 169
Creating the File for Connecting to the Database 170
Creating the Tables 171
Using ENUM 172
The Importance of Documentation 172
Extending the Registration Form and Adding a Pull-Down Menu 174
Always Announce Prices and Fee Payments Up-Front 175
Explanation of the Code 180
Explanation of the Code 185
Adding PayPal Debit/Credit Card Images 186
Including PayPal on the “Thank You” Page 186
Explanation of the Code 190
Registering Some Members 191
A Small Amendment to the Login Page 193
Amending the Administrator’s Header 193
Adding Class and Paid to the admin_view_users Table 195
Explanation of the Code 198
Searching and Editing Records 199
Explanation of the Code 202
Modifying the Form for Editing Records 202
Summary 208
Chapter 6: Add the Finishing Touches: Security and Validation 209
Creating the Database 209
Creating the File for Connecting to the Database 210
Creating the users Table by Importing a SQL Dump File 211
Registering Some Members Manually 213
Adding a Title Column to the users Table 215
Creating the Prices Table by Importing a SQL Dump File 217
Tidying Up the Folders and Filing System 218
Degrees of Security 218
Adding a Layer of Security 218
An Increased Layer of Protection 220
Validation and Sanitization 220
The filter_var() Function 220
Validation 220
Sanitization 222
Validating Telephone Numbers 223
A Safer Registration Page 223
Explanation of the Code 231
Explanation of the Code 238
Searching for a Title, an Address, or a Telephone Number 239
Explanation of the Code 242
Viewing the Retrieved Title, Address, and Phone Number 243
Explanation of the Code 245
Editing the Title, Addresses, and Telephone Numbers 246
Explanation of the Code 253
Summary 254
Chapter 7: Migrate to a Host and Back Up Your Website Database 255
Making Last-Minute Changes 255
Creating a New Database 256
Details of the Downloaded File for Connecting to the Database 257
Allowing Members to Update Their Own Records 258
Explanation of the Code 270
A Secure Feedback Form 272
What Does a Feedback Reply Look Like? 272
The Feedback Form 273
Explanation of the Code 278
Explanation of the Code 282
The “Thank You” Page and the Error Messages 284
A Common Header 287
Explanation of the Code 289
Logging Exceptions and Error 290
Migrating the Database and Tables to a Remote Host 292
A Puzzling Error Message 293
Creating and Exporting the SQL File 293
To Create a Dump File of a Table or Tables 293
What Does a SQL Dump Look Like? 295
Investigate the Remote Host’s Server 298
Using the GUIs on a Remote Host’s Server 299
Connecting to the Database on the Remote Host 300
Securely Uploading the mysqli_connection.php File 301
Uploading the Interactive Pages to the Host 303
Backing Up Your Database 304
Summary 304
Chapter 8: Create a Product Catalog 305
Preparing the Database and Administration Plan 305
Creating a New Database 306
Creating the File for Connecting to the Database 307
Security 308
Creating a Home Page with Search Capability 310
The Header for the Majority of the Pages 311
The Home Page Code 312
Displaying the Catalog 314
Explanation of the Code 320
The Header for the Page of Search Results 321
Creating the House Details Page 322
Explanation of Code 326
Creating the Admin/Adding a House Page 327
The Header for the Administrator’s Page 336
Administrator’s View of the Entire Stock of Houses for Sale 338
The Administrator’s Search Page 343
The Result of a Search 345
The Contact Us Page 346
Summary 352
Chapter 9: Join Multiple Tables and Other Enhancements 353
Introduction to Multiple Tables 353
Normalization 354
Creating the Database and Tables 355
Viewing the Connection File 356
Creating a Second Table 357
Foreign Keys 357
Preparing the Tables for Joining 357
Populating the Two Tables 358
Joining Data from the Two Tables 360
Creating the Third Table 363
Creating Pages to Display the Data from Your Joined Tables 365
The Home Page 365
The Main Menu for the Pages 367
The Header for All the Pages 368
The Page for Viewing the Birds 369
Explanation of the Code 373
The Page for Viewing the Locations and Habitats of the Reserves 373
Displaying Data from the Joined Tables 376
Creating a Page to Display the Three Joined Tables 380
Explanation of the Code 384
Payments by Check 384
A Choice of Payment Method 385
The Check Payment 389
Explanation of the Code 392
Printing Online Forms 393
Summary 394
Chapter 10: Create a Message Board 395
The Plan 395
Creating the Database 396
Creating the Tables 396
Creating the Second Table 397
Creating the Registration Form 403
The “Thank You” Page 411
Populating the Members Table 412
The Login Page 413
Explanation of the Code 415
Logging Out 418
Creating a Gateway to a Choice of Quotes 418
The Form for Posting Quotations 420
Explanation of the Code 423
Processing the Postings 424
Explanation of the Code 425
Posting Some Quotations 426
The Comical Quotes Page 427
Explanation of the Code 429
The Header for the Comical Quotes Page 430
The Wise Quotes Page 431
Explanation of the Code 433
The Header for the Wise Quotes Page 434
Adding Search Facilities 435
Explanation of the Code 437
The Header for ViewPosts.php 438
Searching for Specific Words or Phrases 439
The Full Text Search Form 440
Displaying the Search Results 442
Explanation of the Code 444
The Header for the quotes_found Page 445
Enhancing the Message Board 446
Converting the Message Board to a Forum 446
Summary 447
Chapter 11: E-commerce: A Brief Introduction 448
Security Warning 450
The Plan 450
Creating the PayPal Cart Site 451
Creating the PayPal Cart Database and Tables 451
Viewing the Connection File 452
Populating the PayPal Cart Tables 452
The PayPal Cart Home Page 455
Using the PayPal Cart Home Page to Search for Paintings 458
Explanation of the Code 460
Integrating with the PayPal Shopping Cart Buttons 461
Explanation of the Code 467
Creating a Custom Cart 467
The Custom Cart Home Page 469
Create the Custom Cart Database and Tables 469
Exploring the Custom Shopping Cart 471
The Custom Cart Login Page 473
Explanation of the Code 477
Retrieving a Forgotten Password 477
Explanation of the Code 480
The Custom Cart Search Page 481
Adding Paintings to a Table for a Custom Shopping Cart 482
Explanation of the Code 486
Explanation of the Code 489
Explanation of the Code 493
The Checkout Page 494
The Additional Administrative Tasks 494
Summary 495
Chapter 12: Take a Brief Look at Oracle MySQL 8 496
Advantages of Upgrading 496
Installing MySQL 8 Community Server 498
Exploring the Features of MySQL Workbench 505
Connecting PHP 7 to the MySQL 8 Community Server 512
Migrating to MySQL 8 Community Server 515
Using Our PHP Files with MySQL 8 Community Server 519
Summary 522
Appendix A: Troubleshooting 523
Browser Quirks 524
Tables Not Displaying 524
A Style Change Has No Effect 525
Included Items Missing from the Display 525
A Page Fails to Validate 525
A PayPal Pull-Down Menu Does Not Work 525
Access Denied 525
PHP Error Levels 526
Call to an Undefined Function 526
Cannot Redeclare Function 526
Undefined Index or Undefined Variable 526
Empty Variable Value 527
Headers Already Sent 527
Blank Screen 527
Unexpected End of File in Line xxx 527
Parse Error and Unexpected Characters 528
Unexpected T_STRING 528
Unexpected T_ELSE 529
Wrong Equal Sign 529
Failed to Open Stream 529
Syntax Errors 529
Warning: Division by Zero 530
Display Is Not What Was Expected 530
Reference to a Primary Key Could Not Be Created 530
Element < style>
Problem with Prepared Statement 530
Logical Errors 531
The Internet Is Your Friend 531
Summary 531
Appendix B: Resources 533
PHP Quick Reference 533
Arrays 533
Associative Arrays 534
Comments 535
Concatenation 536
Constants 536
E-mailing with PHP 536
Explanation of the Code 538
Functions 538
include() vs. require() 538
if, else, and elseif 538
The Explanation 540
Loops 540
The while Loop 540
The for Loop 541
The foreach Loop 541
The do while Loop 542
Numbers 542
Prepared Statements 543
Sessions 544
Logging In with a Session 544
Logging Out 545
Ternary Operator 546
Using Try/Catch 546
Validation and Sanitization Filters 548
Variables 549
Variables: Predefined 549
Variables: String 549
Bootstrap Template 549
MySQL and phpMyAdmin Quick Reference 550
date_time 551
INSERT 552
SELECT 552
UPDATE 552
Storage Engines and phpMyAdmin 553
Changing the Storage Engine on an Existing Populated Table 553
What Next? 553
Resources 553
Books on PHP and MySQL for Databases 553
PHP and MySQL Internet Resources 554
E-commerce Resources 554
Online Tutorials 555
Resources for Creating a Forum 555
PayPal Forums 555
Third-Party Shopping Carts 555
Summary 555
Index 556