Got a slight (or major, depending on how you like at it!) issue with the design of my database. My fault for not knowing what I’m doing with databases, although I have done quite a bit of reading on what I’ve got an issue with I can’t seem to get my head around it, so here goes…
Table 1 = books
Table 2 = pages
The books table has a primary key of bookID, some fields like bookName, bookPrice and so on as well as a few foreign keys like AuthorID (which is the primary key in the table authors) and genreSubjectID (again, primary key in the genreSubject table). This all works fine and dandy. Dandy and fine.
A book has multiple pages that you can navigate through “Previous” and “Next”, so there’s pagination in there which comes in later on (another thread perhaps in the PHP subforum).
The pages table has a primary key of pageID, a text field called pageText which stores the text content for the page formatted with Markdown and a foreign key of bookID (which is the primary key in the books table).
Now, right now this works, but only for one page. After that it kind of messes up - Probably because I’ve not designed the database in the correct way for what I’d like it to be able to do!
I want to be able to have multiple pages from the pages table be associated with / belong to a particular book in the books table and then of course it’s a case of making sure that works with the correct MySQL query in the PHP page.
So, my question is, where am I going wrong with the way I’ve designed this? How should I structure the books and pages tables so that the pages are associated with the correct books?
Many thanks for any help, pointers and especially solutions
If that’s how you have your tables, that would be right. If you’re not getting the values you’re expecting, it’s probably one of two things:
Your data is not what you think it is (i.e. your bookId values on the pages table is wrong
The join in your query is wrong (the ON portion is incorrect).
Perhaps if you give us a SHOW TABLE for the two tables, a short data dump example, and the query you’re trying to use? That way we can eliminate all the SQL/DBMS problems, and go from there…
Yea. Honestly I was pretty sure I had the database design right too, but because of the output on the web pages I thought maybe I had the DB design wrong…Maybe it is the PHP as you mention.
Here’s the code I’ve got for the PHP template page that displays the contents of the pageText field from the database:
<?php
include $_SERVER['DOCUMENT_ROOT'] . '/huffiertest.com/includes/dbconnection.inc.php';
if (isset($_GET['slug']))
{
$slug = $_GET['slug'];
}
else
{
header('Location: .');
exit();
}
include $_SERVER['DOCUMENT_ROOT'] . '/huffiertest.com/includes/dbconnection.inc.php';
try
{
$sql = "SELECT pageID, pageText, bookTitle
FROM books, pages
WHERE pages.pageID = books.bookID
AND bookSlugName='$slug'";
$result = $pdo->query($sql);
}
catch (PDOException $e)
{
$error = 'Error fetching title name: ' . $e->getMessage();
include 'error.html.php';
exit();
}
while ($row = $result->fetch())
{
$title = $row['bookTitle'];
$content = $row['pageText'];
$page = $row['pageID'];
}
include 'read.html.php';
?>
Here’s what the first page of the Dracula title is supposed to display:
And this is what the first page of the A Christmas Carol title displays:
But that’s actually supposed to be the 2nd page the Dracula title!
And here’s a few of screenshots from MyPhpAdmin:
Any ideas? If I haven’t given you what you asked for could you please give me more details? Any other information you need I’ll be happy to provide. At this point I’m now convinced it’s something to do with the JOIN I’ve wrong =/
Thanks Dave! This does fix the issue of the pages being muddled up and they now match the title associated with them in the DB tables
Except that for Dracula the content displayed for the page is the second page in the pages table, not the first page, but that will be a pagination issue no doubt so I’ll look into that and likely end up back in the PHP forum!
Since you’re not specifying a page there, the query will return ALL pages for that book, then loop through the result set, overlaying the previous record.
