Building a cool website using HTML, PHP and MySQL

Joins

now to the point of the last several pages. What is the point of having all these tables? You want the information you want, and you don't want to go shuffling from table to table looking for it. If you want to know who has what book out and when is it due, you want information that looks like this:

First, Our library is organized into several tables. We have an authors table, a topics table, a books table, a users table, a books checked out table, and a books-author relation table. The point of all this is to guarantee internal integrity within the data. For instance, we have this author:

  mysql> select books.title, concat(authors.efname,' ',authors.elname)
          -> as author,
          -> topics.topic from books inner join topics using(topic_id)
          -> inner join bookswritten using(book_id)
          -> inner join authors using(writer_id) where authors.writer_id=12
          -> order by topics.topic;
+----------------+-----------------+--------------+
| title          | author          | topic        |
+----------------+-----------------+--------------+
| Humans         | Donald Westlake | Fantasy      | 
| Why me?        | Donald Westlake | Mystery      | 
| High Adventure | Donald Westlake | Romance      | 
| Kahawa         | Donald Westlake | Spy Thriller | 
+----------------+-----------------+--------------+
4 rows in set (0.76 sec)

Same guy, lots of different styles. He wrote lot of books. Whenever we added a new one, all we did is added the one volume to the books table, links in the relation tables and we are done. He just died this year, so if we had biographical information on him, all that would need to change is that, there is no need to change the rest of the data base, with all its opportunities for error. We just change the one bit of information in the one place that has universal application with all his data.

Since all that data is in different tables, we need a way to bring it together. That is the function of the join.

There are two flavors of join. One is the plain join It can be called inner join. (I usually use "inner Join" in my work. ) It reports on data that meets all the criteria. For example, if Steven King were on my list of Authors, and since I don't have any books by Steven King, he would not show up on my inner join.

The other form of join is the left or right join. These joins show data that shows up on one table, even if there is no related data in the second table.

  mysql> select * from authors where writer_id=34;
+-----------+--------+--------+
| writer_id | efname | elname |
+-----------+--------+--------+
|        34 | Steven | King   | 
+-----------+--------+--------+
1 row in set (0.07 sec)

  mysql> select books.title, concat(authors.efname,' ',authors.elname)
          -> as author,
          -> topics.topic from books inner join topics using(topic_id)
          -> inner join bookswritten using(book_id)
          -> inner join authors using(writer_id) where authors.writer_id=34;
Empty set (0.14 sec)

  mysql> select books.title, concat(authors.efname,' ',authors.elname)
          -> as author from books left join bookswritten using(book_id)
          -> right join authors using(writer_id) limit 55,10;
+------------------------------------+------------------+
| title                              | author           |
+------------------------------------+------------------+
| Flashman and the Mountain of Light | George Frazier   | 
| Memoirs                            | Ullysses Grant   | 
| Memoirs                            | Tecumsah Sherman | 
| Skeleton in the Closet             | MC Beaton        | 
| NULL                               | Steven King      | 
+------------------------------------+------------------+
5 rows in set (0.04 sec)

Left and right joins can be useful in places where you need to know where there is empty data. For example, if you have rental property, the apartment doesn't go away when the tenant leaves. Most of the time.

I have been talking about data integrity on several occasions. Data integrety is the point of all this. The goals of data integrety are:

Data is not duplicated. If Sheldon checks out 20 books, we only have one record for Sheldon.
Data that is important does not go away. When Sheldon returns all 20 books, we don't want to loose his information
Mistakes are easy to catch and easy to fix. Multiple records have multiple errors. One record means there is only one place to change.
Searches are faster and more accurate. One patron record can keep track of fines, overdues and restrictions. There is no need to look through all records to look for one data instance

Most of the really important work of web pages is in the design and organization of the data you wish to present. It is also the hardest part of any web project, the part you need to spend the most time thinking over. But now you have the foundation in, you can start thinking of the next part: The HTML frame.

Resources

Building a cool website using HTML, PHP and MySQL

Joins