Tuesday, July 08, 2008

MySQL: Making the move from one table to two related tables

I had to learn this sooner or later and now that it's learned it's great. It was a bit painful. For my site Reading Pen Pals I had to separate records for books and records for authors into two different database tables so that information pertaining only to authors, such as their oral history letters, didn't have to be entered repeatedly for each new book added. The author info would already be in a record but would have to be added again because it was pinned to that record. So the MySQL query has changed from this:
SELECT * FROM books WHERE alphachar='h' ORDER BY title ASC
to this:
SELECT books.id,books.authorlname,books.the,books.title, books.level_gif,authors.authorfname,authors.authorlname, authors.authorlink,books.quote FROM books,authors WHERE alphachar='h' AND authors.authorlname = books.authorlname ORDER BY title ASC
so what I had to learn about was joined tables. It's a great system because you can specify which tables all the data is coming from after FROM, and which table each field belongs to with a period separating the table and field. Cool.

No comments :