Author: Adrian W. West
Pub Date: 2013
Size: 13 Mb
Practical PHP and MySQL Website Databases is a project-oriented book that demystifies building interactive, database-driven websites. The focus is on getting you up and running as quickly as possible. In the first two chapters you will set up your development and testing environment, and then build your first PHP and MySQL database-driven website. You will then increase its sophistication, security, and functionality throughout the course of the book. The PHP required is taught in context within each project so you can quickly learn how PHP integrates with MySQL to create powerful database-driven websites.
Each project is fully illustrated, so you will see clearly what you are building as you create your own database-driven website. You will build a form for registering users, and then build an interface so that an administrator can view and administer the user database. You will create a message board for users and a method for emailing them. You will also learn the best practices for ensuring that your website databases are secure. Later chapters describe how to create a blog, a product catalog, and a simple e-commerce site. You will also discover how to migrate a database to a remote host. Because you are building the interactive pages yourself, you will know exactly how the MySQL and PHP work, and you will be able to add database interactivity to your own websites with ease.
The locationtable contains the location_id, location, and location_type. In addition, it contains a column named bird_idthat duplicates the bird_idcolumn that is the PRIMARY key in the birdstable. The birds_idcolumn in the locationtable is called a foreign keybecause this is a key from a different table (the birdstable). This foreign key
enables us to join the two tables by means of a SELECT and INNER JOIN statement; the various JOIN methods will be explained later. By this means, we can select certain data from both tables and display the result in a browser as a single table.
IMPORTANT: Foreign keys link to PRIMARY keys in another table, and they must be the same type and length as the PRIMARY key. Also, foreign keys must all be indexed. Indexing these keys ensures fast queries. In our tutorial, all the PRIMARY keys and their linked foreign keys are of the type MEDIUMINT and their length is 4.
PRIMARY keys are automatically indexed, but foreign keys must be indexed by the web-site developer. This can be done in phpMyAdmin, as you will see shortly. If you try to set a foreign key that links to a PRIMARY key with a different length or value, you will see an error message stating that the reference to the PRIMARY key could not be created.
Caution do not enter data in the tables. We will do this later when the tables have been prepared for joining.
Meanwhile, we must begin with some preparation.
Preparing the Tables for Joining
So that we can join the tables, we must establish a link between the bird_idin the first table and the bird_idin the second table. But first, we need to set the storage method.
MySQL uses two methods for storing tables; they are called INNODB, the default for MySQL, and MyISAM. Note that MySQL supports foreign keys only when INNODB storage is used; therefore, we must check that both tables are using the INNODB storage engine. You can quickly check the storage method for all tables by looking on the database Structuretab. In the Typecolumn, it will list the storage type. Alternatively, in phpMyAdmin, select the box next to the birdstable and click the Operationstab. Then go to the Table optionssection If the storage engine is not listed as INNODB, change it to INNODB. Repeat this for the second table. The Operationsdialog screen is
shown in Figure 9-1.
In this chapter, I introduced the theory and practice of using multiple tables. You learned that such tables are virtual tables that are present only in the volatile memory of the server and that they can be viewed in a browser. The difference between various join methods was described. I then demonstrated that the virtual tables could be made visible on the screen by using SQL queries and PHP. A tutorial then showed you how to implement membership-fee payments by check. This was augmented by a demonstration of economical form printing, so that an application form and a check could be sent to the organization. In the next chapter, I will introduce you to an online message board.