Section 5.3 Accessing a MySQL Database with PHP

Learning Objectives

After you have completed this section, you should be able to

• access MySQL database with PHP.
• use PHP and MySQL together to implement authentications and secure transactions in e-commerce systems.

Activities

• Carefully read the learning objectives.
• Find and study some PHP scripts on the web.
• Find some examples of websites that use PHP with MySQL and write a summary of the function and implementation of each website.
• Watch the related sections of the presentation for Chapter 19;
• Read section 19.9 of Chapter 19.
• See the list of functions at https://www.php.net/manual/en/ref.mysql.php
• Find answers to the study questions below.
• Write a web page that uses passwords stored in a file to authenticate a web user.
• Design a web page using PHP that can serve as an interface to MySQL databases. You should document your design in detail because this exercise will be expanded in the Assignment 2 project.

Study Questions

1. How do web database architectures work?
2. What are the basic steps in querying a database from the Web?
3. How do you connect a database from the Web?
4. How do you check and filter input data from a web user?
5. How do you get information about available databases in PHP?
6. How do you choose a database in PHP?
7. How do you query a database?
8. How do you retrieve the query result?
9. How do you disconnect from the database?
10. How do you add new information into the database?
11. How do you make your database secure?
12. How do you free up resources?
13. How do you create and delete a database in PHP?
14. In addition to MySQL, what other databases does PHP provide interfaces to?

Implementing Authentication with PHP and MySQL

1. Why do you need to authenticate web users?
2. How do you identify visitors to your website?
3. How can you implement access control in PHP without using MySQL?
4. How can you implement access control in PHP with MySQL?
5. How do you encrypt passwords in PHP?
6. How do you protect multiple pages with access control?
7. How does HTTP support authentication?
8. How do you use basic authentication provided by HTTP in PHP?
9. In PHP, how do you use basic authentication with Apache’s .htaccess files?
10. What is mod_auth_mysql authentication? How do you use it?

Implementing Secure Transactions with PHP and MySQL

1. What are secure transactions on the Web?
2. What do we mean by providing secure transactions?
3. How can secure transactions be provided on the Web?
4. What mechanisms are ready for use to provide secure transactions on the Web?
5. What is secure socket layer (SSL)? 
6. Where does SSL sit in the Internet protocol stack?
7. How is an SSL connection established between a web browser and a web server?
8. How is data sent over an SSL connection?
9. How do you screen user input in PHP?
10. How do you provide secure storage in PHP and MySQL?
11. Where should you store credit card numbers at your website?
12. How do you use encryption in PHP?
13. How do you install PGP on your site?
14. What can PGP be used to do? How is it used?

Summary

PHP and MySQL seem to be a perfect combination for website development. First of all, both systems are free for non-commercial uses, and secondly, they have high performance: speed, functionality, and scalability. Although there are many other systems you can use to develop your websites, PHP and MySQL are worth your close attention.