Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
This course was updated on 6/12/2012.
- Understanding dynamic versus static content
- Adding PHP commands to web pages
- Setting and outputting variables
- Using server-side includes
- Creating PHP custom classes
- Adding the Zend Framework to a PHP installation
- Creating a MySQL database
- Adding data in phpMyAdmin
- Building recordsets
- Formatting dynamic data
- Building data entry forms
- Authenticating users
- Deploying a dynamic site
Skill Level Intermediate
MySQL is an example of a relational database engine. It manages data in multiple tables and columns, and gives you the ability to join these tables together at runtime in SQL statements. It's important to understand a few basics about the nature of databases. Data is stored in tables. In this example, data is stored for a library in a number of tables, including Authors, Books, Customers, and Checkouts. Each of the tables manages data for a particular entity.
For example, the Authors table manages data for each author. You define the table with multiple columns. Columns, also known as fields, represent the individual elements of data in a particular entity. When you design your tables, it's very important to include a single column, known as the primary key. In this table, the authorId is the primary key. The asterisk indicates this. Then each of the other columns represents a particular bit of data. For example, an author might have the firstName, the lastName, and the dateOfBirth.
The authorId in MySQL is commonly set up as an integer, or numeric value, which means that when you add a new record to the table, MySQL assigns the next available number for the primary key column. You should design your web sites so that the user never sees this primary key explicitly on the screen. It's a meaningless value, so that the user doesn't have to enter the value or take any responsibility for it. It's completely managed by your web site, by your PHP code, and by your MySQL database.
A well-designed relational database structure is said to be normalized. That means that you don't repeat data, but instead break it up into different tables, so it can easily be joined together as needed at runtime. As mentioned previously, each table should have a primary key. The value for each row in the primary key column uniquely identifies that row, so that if you have a row, say, with a value of 5 in the primary key, there will be no other row with that value. This makes it possible to select data from the table very easily.
Most tables have single column primary keys. For example, an author's table would have an authorId, a book's table would have a bookId, and so on. You don't have to use that naming convention, but it's the one I typically use. There are also sometimes tables with multiple column primary keys, where two or more values together make up a unique value. I won't use any of those tables in the sample database for this video series, but you will see them occur in more complex database structures.
In order to join tables together, you can add foreign key columns. A foreign key column in one table would match the primary key column in another table. That would allow you to join them together as needed. Here is a way to visualize the relationship between two tables. You create the relationships between primary keys in one table and a foreign key in another table. In this table, there are four columns named bookId, title, authorId, and isbncode.
The asterisk indicates that bookId is the primary key for this table. authorId is also a numeric value, but it's not unique in this table; instead, it's designed to join this table to another table. This table would be the Authors table. It would also have four columns perhaps, with column names of authorId, firstName, lastName, and dateOfBirth. This table's primary key is authorId, indicated by the asterisk. Assuming that there are values in the two tables, Books and Authors that match each other, you would join them together at runtime by matching the values in the authorId columns of both tables.
You would do this with an SQL statement. This is one available syntax for joining tables together. The SELECT clause indicates which columns from each table are going to be included in the recordset returned from the database. The FROM clause uses a JOIN operator to link the two tables together. The ON clause indicates which columns should match each other. The operator JOIN means what we call an inner join. That means that values will only be returned where there are matches between the two tables.
You can also use outer joins, indicated by the phrases LEFT JOIN or RIGHT JOIN. That means that you're including data from one table regardless of whether there is a match is in the other table. Again, for the sake of simplicity, in this video series, I'll only use inner joins indicated by the JOIN operator. So this SQL statement will return four columns, the title and isbncode from the Books table, and the firstName and lastName from the Authors table. These values will be returned in a single recordset, and only where there would be matches between the authorId values in both tables.
There is a lot more to know about relational databases, and normalizing your database structures, but if you understand these simple principles, you'll be able to follow all of the exercises in this video series, and you'll be able to accomplish about 80-90% of what we commonly do in dynamic web applications built with PHP and MySQL.