A very powerful feature of PHP is the ability to link tables. Using linked tables instead of one huge table makes databases much more flexible. In this example, I will use two tables: music and genre. The music table was used in the previous example. Using describe, I can see more about the tables:
mysql> describe music ;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| artist | varchar(30) | | | | |
| album | varchar(30) | | | | |
| price | decimal(6,0) | | | 0 | |
| qtty | int(5) | | | 0 | |
| label | varchar(30) | | | | |
| genre | int(8) | YES | | NULL | |
| media | int(8) | | | 1 | |
| media_count | smallint(3) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
mysql> describe genre ;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(15) | | | | |
| subgenre | varchar(15) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+I can query using both of these tables. I can link the music.genre field to the genre.id field. This will let me see the name of the genre, instead of a number. If I query the music table for a listing of all bands and their genre, I get something like:
mysql> SELECT artist , genre FROM music WHERE 1 ;
+------------------------+-------+
| artist | genre |
+------------------------+-------+
| string cheese incident | 1 |
| Stevie Ray Vaughan | 2 |
| Grateful Dead | 2 |
+------------------------+-------+But if I link the tables in the query, I can see the genre by name:
mysql> SELECT music.artist , genre.name AS 'genre' FROM music , genre WHERE genre.id = music.genre ;
+------------------------+---------+
| artist | genre |
+------------------------+---------+
| string cheese incident | jamband |
| Stevie Ray Vaughan | blues |
| Grateful Dead | blues |
+------------------------+---------+In this query, I am telling mySQL to select the artist from the music table, the genre name from the genre table, and to only select when the genre.id is the same as the music.genre. I also introduced the SQL statement as in this query. Using as allows me to "rename" genre.name to "genre". Without the as the query would have looked like:
mysql> SELECT music.artist , genre.name FROM music , genre WHERE genre.id = music.genre ;
+------------------------+---------+
| artist | name |
+------------------------+---------+
| string cheese incident | jamband |
| Stevie Ray Vaughan | blues |
| Grateful Dead | blues |
+------------------------+---------+This can be confusing, so it is a good idea to use as when it will make the query results easier to understand. Note that a query is creating a new temporary table.
When designing a database, it is important to think about how the data should be split into multiple tables. If this was a real database, I would have also used separate tables for label, artist, and possibly media. I would also add more fields for any other information that I have, such as year. Using a separate table for artist would allow a wider range of queries. For example, I would be able to query for other bands that a artist has been in.