I'm working on a website which will be hosting a number of books written by myself. The book contents will be stored as HTML chunks in a database.
A book contains several chapters, and each chapter contains several sections. I decided to give each book, chapter and section unique ID:s with the aim that I can move and delete sections freely and not having to update records.
So the database looks like this:
books:
book_id int
book_name varchar
chapters:
book_id int references books.book_id
chapter_id int
chapter_no int
chapter_name varchar // will probably be factored out to a separate table
sections:
chapter_id int references chapters.chapter_id
section_no int
section_id int references section_texts.section_id
section_texts:
section_id int
section_text text
So for example section 5 in chapter 3 can point to the section_text
which has section_id
79, and contains the text "Hello world!". Section 6 could have section_id
83 and so on.
Now I want to create a PHP method which will, given a book name, chapter number and section number, retrieve for me the section_text
that is there. What I have now is done in 4 steps:
function getText($bookName, $chapter, $section)
{
// retrieve book id
$query = $this->db->query(
sprintf('SELECT book_id
FROM books`
WHERE book_name = %s;', $bookName));
$row = $query->row();
$bookId = $row->book_id;
// retrieve chapter id
$query = $this->db->query(
sprintf('SELECT chapter_id
FROM chapters`
WHERE book_id = %d AND chapter_no = %d;', $bookId, $chapter));
$row = $query->row();
$chapterId = $row->chapter_id;
// retrieve section id
$query = $this->db->query(
sprintf('SELECT section_id
FROM sections`
WHERE chapter_id = %d AND section_no = %d;', $chapterId, $section));
$row = $query->row();
$sectionId = $row->section_id;
// retrieve section text
$query = $this->db->query(
sprintf('SELECT section_text
FROM section_texts`
WHERE section_id = %d;', $sectionId));
$row = $query->row();
return $row->section_text;
}
So for our example above, calling getText("testBook", 3, 5)
should return the retrieved text, which was Hello World!
.
Although it works, it seems like the wrong way to go, creating 4 queries for fetching one section. This will be a small website so performance is not an issue, but I'm still interested in how this could be improved.