Building a cool website using HTML, PHP and MySQL

Joins

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.

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.