Second Edition
Adrian W. West & Steve Prettyman
Purchase Now !
Just with Paypal
Just with Paypal
Book Details
Price
|
3.00 |
|---|---|
Pages
| 561 p |
File Size
|
15,578 KB |
File Type
|
PDF format |
ISBN-13
| 978-1-4842-3842-4 (pbk) 978-1-4842-3843-1 (electronic) |
Copyright©
| 2018 by Adrian W. West and Steve Prettyman |
Adrian W. West resigned as a chartered design engineer to become the UK director of a correspondence school. He has been teaching in one form or another since 1982. He introduced computers into his workplace in 1987 and taught the staff how to use them. For four years, he taught undergraduates computer skills at a college in Cheshire in the United Kingdom.
Adrian lives in Colyton, a town in Devon, England, and for the last 18 years, he has designed and
produced websites for UK businesses and charities.
Adrian is the author of three books published by Apress: Practical HTML5 Projects, a book of tips and
tricks (now rather out of date); Practical PHP and MySQL Website Databases (the first edition of this book); and Practical Web Design for Absolute Beginners (published in 2016).
Steve Prettyman earned his Bachelor of Arts degree in secondary 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 more than 30 years in the IT industry. The last almost 20 of those 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 chairperson for Florida Keys Community College in Key West, Florida. His primary teaching responsibilities include programming, web design, and web application development.
Introduction
What’s New in This Edition?
The code and instructions in the first edition of this book (written in 2012) were made obsolete by new and very different versions of XAMPP, EasyPHP, phpMyAdmin, PHP, and MySQL/MariaDB. This second edition contains new code and instructions to match the latest versions of the software.
With the massive increase in cybercrime and other cyber threats, this new edition has been fortified
with a much stronger emphasis on security. This book takes the approach of sanitizing any data that has
been accepted from any outside source and, additionally, sanitizing any data before it is displayed on a web page. Most of the examples use prepared statements that ensure that any externally accepted data cannot be executed and therefore cannot cause security vulnerabilities.
We have chosen Bootstrap to provide responsive web design (RWD) for each of the book’s example
websites. While Bootstrap provides the CSS and JavaScript to format the examples in this book for any size device, you can easily reformat these examples with your own CSS code if you desire.
This new edition also takes a brief look at Oracle’s MySQL 8. A comparison is provided of the tools
available in each version. Step-by-step procedures provide you with the ability to upgrade to MySQL 8.
The Teaching Method
This book uses a different way of teaching website database design compared with the majority of manuals. The usual layout starts with several lessons on PHP followed by snippets of code and may eventually conclude with a project or two. This book abandons that approach. The primary focus is on fully worked, practical MySQL/MariaDB database projects built into real-world web pages.
Instead of presenting PHP, SQL, and MySQL/MariaDB as completely separate topics, they are explained in the context of each project. However, you will find a useful quick reference of PHP syntax in Appendix B. In this book, practical databases and interactive web pages are presented as early as possible; in fact, you will create a database and a table in the first chapter. In the second chapter, you will embed a database into an interactive web page and test it. Each subsequent chapter will introduce you to increasingly sophisticated and useful database-driven website pages.
We assume that you have little or no knowledge of PHP and databases. This book will demonstrate:
• How to create a free environment for testing database-driven web pages.
• How to embed PHP and interactive databases into real-world web pages. This is the
primary theme throughout the book.
• How PHP, HTML, and MySQL/MariaDB work together for creating and maintaining
a database and its data.
• How to create a user-friendly interface so that an administrator with minimum
computer skills can monitor the database.
Because databases need to be viewed and tested on a server, the first part of Chapter 1 has instructions
for using a free server that can be downloaded and installed on your computer. This ensures you will have a safe development platform for learning and testing as you explore the book’s practical projects.
Starting with a separate study of PHP theory and syntax can deter learners and prolong the time until
they get their hands on a practical application. Learners are enthused when they achieve something. This book jumps into the database driving seat right from the beginning. Essential PHP and MySQL/MariaDB techniques are presented in context within each tutorial where they are most relevant.
The “Quick and Easy to Learn” Myth
Books frequently state that PHP and MySQL/MariaDB databases are easily and quickly learned, but this discourages beginners, because when they are confronted with the inevitable difficulties (and error messages), they begin to think that they will never grasp even the basic principles.
Beginners should not be discouraged if they remember the following fact: authors claiming that PHP
and MySQL/MariaDB are easily and quickly learned have probably been using PHP and MySQL for more than a decade, and they have forgotten the difficulties they encountered when they first began.
If you accept that some time and effort are required to learn PHP and MySQL/MariaDB, then as
you work through the book, it will become increasingly apparent that you are learning something very
worthwhile. So, have patience and persevere, and you will then begin to enjoy mastering this valuable discipline.
The Origin of This Book
Most of the PHP/MySQL/MariaDB books tend to demonstrate the author’s deep and extensive knowledge of PHP and MySQL/MariaDB instead of teaching how to embed MySQL/MariaDB databases into web pages. In contrast, this book uses fully worked examples to demonstrate how to integrate databases into a website.
The boatload of PHP/MySQL/MariaDB database books that this book’s authors own (or borrowed)
were unnecessarily complicated. The authors of these books had become used to using neat tricks and
shortcuts that were second nature, but these cluttered the code and made it difficult for beginners to discern the essential structure.
This book avoids this mistake; a few useful tricks are introduced gradually and are fully explained in
plain English. This book is based on a quote from the composer Brahms.
It is easy to compose but wonderfully hard to let the superfluous notes fall under the table.
Almost all the PHP/MySQL/MariaDB books were written backward; they grind away for chapter after
chapter with PHP functions and statements (yawn), and then they add the MySQL/MariaDB bit. Practical PHP and MySQL Website Databases explains the necessary PHP and MySQL/MariaDB topics in context within each database tutorial.
MySQL/MariaDB books are nearly always written assuming that the web designer will administer the
databases. However, small e-commerce websites, clubs, and societies cannot afford to do this and would prefer that their membership secretary be able to administer the database using a user-friendly interface. The majority of the databases created in this book can be administered by both an unskilled membership secretary and the web designer.
Eventually, Adrian W. Wood concluded that he must write his own manual based on what he could
learn by concatenating snippets of information from multiple resources. He also based the manual on his own trial-and-error approach as a raw beginner. This automatically ensured that the manual’s content was presented in simple, logical, and progressive steps without suddenly introducing unexplained items.
The homegrown manual was so useful that he decided that it should be shared with other website
designers; the first edition of this book was the result of that decision.
Computer software and database techniques are constantly improving and updating. Because of this,
we have researched the latest versions of the scripts, tools, and the available software. This ensures that, in this second edition, the content and illustrations will remain relevant for as long as possible.
Following the tutorials in this book requires an absolute minimum of software. Some manuals ask
readers to download and learn a new piece of software before they can proceed to each new chapter. In
fact, Adrian came across one book that required readers to download MySQL, Apache, PHP, phpMyAdmin, Prototype 1.5, Scriptaculous, Zend Framework, Smarty Template Engine, FCK editor, jQuery, and Ajax. In this book, besides a code editor, the software required is limited, as described next.
Table of Contents
About the Authors .................................................................................................. xiii
Acknowledgments ...................................................................................................xv
Introduction ...........................................................................................................xvii
■■Chapter 1: Create and Test a Database and Table ................................................. 1
Defining Developer, Administrator, and User .................................................................... 2
Defining Interactive Websites .................................................................................................................2
Summary ........................................................................................................................ 31
■■Chapter 2: Create Web Pages That Interact with Users ....................................... 33
Creating the Folder for Holding the Database Pages ..................................................... 34
Creating the Temporary Template .................................................................................. 34
Introducing the PHP include( ) Function ......................................................................... 36
The Included Header File ......................................................................................................................37
The Included Menu File ........................................................................................................................39
The Included Information Column ........................................................................................................ 40
The Included Footer File .......................................................................................................................41
The Interactive Version of the Template ......................................................................... 42
Connecting to the Database ........................................................................................... 44
The Registration Page ..........................................................................................................................47
The PHP Keyword echo .................................................................................................. 58
The “Thank You” Page .................................................................................................... 58
Displaying Error Messages That Are Collected in an Array ............................................. 60
Hashing the Password .................................................................................................... 61
Viewing Members’ Records ............................................................................................ 61
The View Users Page ...................................................................................................... 61
The Change Password Page ........................................................................................... 65
Confirming a Successful Password Change ................................................................... 72
Testing the Tutorial’s Pages ..................................................................................................................72
More About Arrays .......................................................................................................... 73
Summary ........................................................................................................................ 74
■■ Chapter 3: Create Login/Logout Functionality for Members and
an Administrator ................................................................................................. 75
Creating the logindb Database and users Table ............................................................. 76
Removing or Replacing Redundant Menu Buttons in the Headers ................................. 77
Adding a Login Button to the Home Page Header ................................................................................ 78
Removing Redundant Buttons from the Registration and New Password Headers ............................. 79
The Revised Registration Page .............................................................................................................79
The New Header for the New Password Page ...................................................................................... 80
A New Header Menu for the Members page ........................................................................................ 82
Amend the Header for the “Thank You” Page ....................................................................................... 82
The Registration Page and Undesirable Characters ....................................................... 84
Registering Some Members .................................................................................................................87
Differentiating Between Two Types of Membership ....................................................... 88
Creating User Levels to Limit Access to Private Pages .................................................. 88
Log In ............................................................................................................................. 91
The Header for the Login Page .............................................................................................................91
The Login Page .....................................................................................................................................92
Sessions ...............................................................................................................................................99
A Members-Only Page .................................................................................................. 101
Planning the Administrator’s Role ................................................................................ 104
A New Header for the Administration Page ........................................................................................ 105
Testing the Login/Logout Function ............................................................................... 109
Amending and Deleting Individual Records ........................................................................................109
Summary ...................................................................................................................... 110
■■Chapter 4: Create an Administration Interface .................................................. 111
The Administration Database ....................................................................................... 111
The Users Table ..................................................................................................................................113
Revising the View Users Page to Include Editing and Deleting .................................... 115
Displaying Pages of Records (Pagination) .................................................................... 121
Planning the Search Criteria ........................................................................................ 128
A Temporary Search Page for Displaying Specified Members ..................................... 128
The Search Form .......................................................................................................... 132
The Final Form Handler for Receiving Search Form Input ........................................... 135
Editing Records ............................................................................................................ 139
Deleting Records ................................................................................................................................145
Summary ...................................................................................................................... 150
■■Chapter 5: Expand and Enrich Your Website ..................................................... 151
Creating a New Database, a Table with 15 Columns, and a Price Table ....................... 151
Creating the File for Connecting to the Database ............................................................................... 152
Creating the Tables .............................................................................................................................153
Using ENUM ........................................................................................................................................154
The Importance of Documentation ............................................................................... 154
Extending the Registration Form and Adding a Pull-Down Menu ................................ 156
Always Announce Prices and Fee Payments Up-Front ....................................................................... 157
Adding PayPal Debit/Credit Card Images ..................................................................... 168
Including PayPal on the “Thank You” Page .................................................................. 168
Registering Some Members ......................................................................................... 173
A Small Amendment to the Login Page .............................................................................................. 175
Amending the Administrator’s Header ......................................................................... 175
Adding Class and Paid to the admin_view_users Table ............................................... 177
Searching and Editing Records .................................................................................... 181
Modifying the Form for Editing Records .............................................................................................184
Summary ...................................................................................................................... 190
■■Chapter 6: Add the Finishing Touches: Security and Validation ........................ 191
Creating the Database .................................................................................................. 191
Creating the File for Connecting to the Database ............................................................................... 192
Creating the users Table by Importing a SQL Dump File .............................................. 193
Registering Some Members Manually ................................................................................................195
Adding a Title Column to the users Table ..................................................................... 197
Creating the Prices Table by Importing a SQL Dump File ................................................................... 199
Tidying Up the Folders and Filing System .................................................................... 200
Degrees of Security ...................................................................................................... 200
Adding a Layer of Security ................................................................................................................. 200
An Increased Layer of Protection ........................................................................................................202
Validation and Sanitization ........................................................................................... 202
The filter_var() Function .....................................................................................................................202
Validation ............................................................................................................................................202
Sanitization .........................................................................................................................................204
Validating Telephone Numbers ...........................................................................................................205
A Safer Registration Page ............................................................................................ 205
Searching for a Title, an Address, or a Telephone Number ........................................... 221
Viewing the Retrieved Title, Address, and Phone Number .................................................................. 225
Editing the Title, Addresses, and Telephone Numbers ........................................................................ 228
Summary ...................................................................................................................... 236
■■Chapter 7: Migrate to a Host and Back Up Your Website Database ................... 237
Making Last-Minute Changes ...................................................................................... 237
Creating a New Database ...................................................................................................................238
Allowing Members to Update Their Own Records .............................................................................. 240
A Secure Feedback Form ...................................................................................................................254
The Feedback Form ............................................................................................................................255
The “Thank You” Page and the Error Messages ................................................................................. 266
A Common Header ....................................................................................................... 269
Logging Exceptions and Error ...................................................................................... 272
Migrating the Database and Tables to a Remote Host ................................................. 274
A Puzzling Error Message ...................................................................................................................275
Creating and Exporting the SQL File ...................................................................................................275
Using the GUIs on a Remote Host’s Server ......................................................................................... 281
Connecting to the Database on the Remote Host ............................................................................... 282
Securely Uploading the mysqli_connection.php File ..........................................................................283
Uploading the Interactive Pages to the Host ...................................................................................... 285
Backing Up Your Database ........................................................................................... 286
Summary ...................................................................................................................... 286
■■Chapter 8: Create a Product Catalog ................................................................. 287
Preparing the Database and Administration Plan ......................................................... 287
Creating a New Database ............................................................................................. 288
Creating the File for Connecting to the Database ........................................................ 289
Security ........................................................................................................................ 290
Creating a Home Page with Search Capability ............................................................. 292
The Header for the Majority of the Pages ...........................................................................................293
The Home Page Code .........................................................................................................................294
Displaying the Catalog ................................................................................................. 296
The Header for the Page of Search Results ................................................................. 303
Creating the House Details Page .................................................................................. 304
Creating the Admin/Adding a House Page .................................................................... 309
The Header for the Administrator’s Page ..................................................................... 318
Administrator’s View of the Entire Stock of Houses for Sale ........................................ 320
The Administrator’s Search Page ................................................................................. 325
The Result of a Search .......................................................................................................................327
The Contact Us Page .................................................................................................... 328
Summary ...................................................................................................................... 334
■■Chapter 9: Join Multiple Tables and Other Enhancements ................................ 335
Introduction to Multiple Tables ..................................................................................... 335
Normalization .....................................................................................................................................336
Creating the Database and Tables ......................................................................................................337
Viewing the Connection File ...............................................................................................................338
Preparing the Tables for Joining .........................................................................................................339
Populating the Two Tables ..................................................................................................................340
Joining Data from the Two Tables ................................................................................ 342
Creating the Third Table ......................................................................................................................345
Creating Pages to Display the Data from Your Joined Tables ............................................................. 347
The Home Page ..................................................................................................................................347
The Main Menu for the Pages .............................................................................................................349
The Header for All the Pages ..............................................................................................................350
The Page for Viewing the Birds .......................................................................................................... 351
The Page for Viewing the Locations and Habitats of the Reserves .................................................... 355
Payments by Check ...................................................................................................... 366
A Choice of Payment Method .............................................................................................................367
Printing Online Forms ................................................................................................... 375
Summary ...................................................................................................................... 376
■■Chapter 10: Create a Message Board ................................................................ 377
The Plan ....................................................................................................................... 377
Creating the Database .................................................................................................. 378
Creating the Tables ....................................................................................................... 378
Creating the Second Table ............................................................................................ 379
Creating the Registration Form .................................................................................... 385
The “Thank You” Page ........................................................................................................................393
Populating the Members Table ..................................................................................... 394
The Login Page ............................................................................................................. 395
Logging Out .................................................................................................................. 400
Creating a Gateway to a Choice of Quotes ................................................................... 400
The Form for Posting Quotations .................................................................................. 402
Posting Some Quotations ............................................................................................. 408
The Comical Quotes Page ............................................................................................ 409
The Wise Quotes Page ........................................................................................................................413
Adding Search Facilities ............................................................................................... 417
The Header for ViewPosts.php ..................................................................................... 420
Searching for Specific Words or Phrases ..................................................................... 421
The Full Text Search Form ..................................................................................................................422
Displaying the Search Results ...................................................................................... 424
The Header for the quotes_found Page .............................................................................................. 427
Enhancing the Message Board ..................................................................................... 428
Converting the Message Board to a Forum ........................................................................................ 428
Summary ...................................................................................................................... 429
■■Chapter 11: E-commerce: A Brief Introduction ................................................. 431
Security Warning .......................................................................................................... 433
The Plan ....................................................................................................................... 433
Creating the PayPal Cart Site ....................................................................................... 434
Creating the PayPal Cart Database and Tables ................................................................................... 434
Viewing the Connection File ...............................................................................................................435
Populating the PayPal Cart Tables ......................................................................................................435
The PayPal Cart Home Page ...............................................................................................................438
Using the PayPal Cart Home Page to Search for Paintings ................................................................. 441
Integrating with the PayPal Shopping Cart Buttons ............................................................................ 444
Creating a Custom Cart ................................................................................................ 450
The Custom Cart Home Page .............................................................................................................. 452
Create the Custom Cart Database and Tables .................................................................................... 452
Retrieving a Forgotten Password .......................................................................................................460
The Custom Cart Search Page ............................................................................................................464
Adding Paintings to a Table for a Custom Shopping Cart ................................................................... 465
The Checkout Page .............................................................................................................................477
The Additional Administrative Tasks ................................................................................................... 477
Summary ...................................................................................................................... 478
■■Chapter 12: Take a Brief Look at Oracle MySQL 8 ............................................. 479
Advantages of Upgrading ............................................................................................. 479
Installing MySQL 8 Community Server ......................................................................... 481
Exploring the Features of MySQL Workbench .............................................................. 488
Connecting PHP 7 to the MySQL 8 Community Server ................................................. 495
Migrating to MySQL 8 Community Server .................................................................... 498
Using Our PHP Files with MySQL 8 Community Server ................................................ 502
Summary ...................................................................................................................... 505
■■Appendix A: Troubleshooting ............................................................................. 507
Browser Quirks ............................................................................................................. 508
Tables Not Displaying ................................................................................................... 508
A Style Change Has No Effect ...................................................................................... 509
Included Items Missing from the Display ..................................................................... 509
A Page Fails to Validate ................................................................................................ 509
A PayPal Pull-Down Menu Does Not Work ................................................................... 509
Access Denied .............................................................................................................. 509
PHP Error Levels ........................................................................................................... 510
Call to an Undefined Function ...................................................................................... 510
Cannot Redeclare Function .......................................................................................... 510
Undefined Index or Undefined Variable ........................................................................ 510
Empty Variable Value .................................................................................................... 511
Headers Already Sent ................................................................................................... 511
Blank Screen ................................................................................................................ 511
Unexpected End of File in Line xxx ............................................................................... 511
Parse Error and Unexpected Characters ...................................................................... 512
Unexpected T_STRING .................................................................................................. 512
Unexpected T_ELSE ...................................................................................................... 513
Wrong Equal Sign ......................................................................................................... 513
Failed to Open Stream .................................................................................................. 513
Syntax Errors ................................................................................................................ 513
Warning: Division by Zero ............................................................................................. 514
Display Is Not What Was Expected ............................................................................... 514
Reference to a Primary Key Could Not Be Created ...................................................... 514
Element <style> Not Allowed as Child of Element <div> ............................................ 514
Problem with Prepared Statement ............................................................................... 514
Logical Errors ............................................................................................................... 515
The Internet Is Your Friend ........................................................................................... 515
Summary ...................................................................................................................... 515
■■Appendix B: Resources ...................................................................................... 517
PHP Quick Reference ................................................................................................... 517
Arrays .................................................................................................................................................517
Bootstrap Template ...................................................................................................... 533
MySQL and phpMyAdmin Quick Reference .................................................................. 534
date_time ...........................................................................................................................................535
INSERT ................................................................................................................................................536
SELECT ...............................................................................................................................................536
UPDATE ...............................................................................................................................................536
What Next? ................................................................................................................... 537
Resources .................................................................................................................... 537
Books on PHP and MySQL for Databases ........................................................................................... 537
PHP and MySQL Internet Resources ...................................................................................................538
E-commerce Resources .....................................................................................................................538
Summary ...................................................................................................................... 539
Index ..................................................................................................................... 541
Bookscreen
Who Is This Book For?
The book assumes you are thoroughly familiar with HTML5 and CSS3. However, we assume you have no knowledge of MySQL/MariaDB, PHP, and phpMyAdmin. As the chapters unfold, you will progress from intermediate level to advanced level.
You do not need to acquire an extensive knowledge of PHP to create interactive databases. We
introduce all the PHP you will need in the appropriate place within each project. Each piece of PHP code is explained fully in plain English. The step-by-step, fully worked examples will show you what MySQL/ MariaDB and PHP can do and how to do it. This book is for web designers who want to begin developing database-driven websites.
With this in mind, this book uses a highly motivational step-by-step approach. We recognize fully that
a sense of achievement encourages readers to look forward eagerly to the next step. The book will teach
enough PHP and MySQL/MariaDB to complete all the projects in the book.
Web developers who have not
kept up-to-date with MySQL/MariaDB and PHP will also benefit from this approach. College and university
programming instructors will find that this book provides an excellent text, and the projects can form a basis
for students to adapt for their course work.