SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru bronze trophy AndrewCooper's Avatar
    Join Date
    Sep 2008
    Location
    Manchester, UK
    Posts
    631
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Database Design Issue

    Hey All,

    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

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Your design sounds right. Just to be sure I've got it right...

    book: bookId (PK), bookName, bookPrice,.....
    pages: pageId (PK), bookId (FK to book), pageText

    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...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Guru bronze trophy AndrewCooper's Avatar
    Join Date
    Sep 2008
    Location
    Manchester, UK
    Posts
    631
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Your design sounds right. Just to be sure I've got it right...

    book: bookId (PK), bookName, bookPrice,.....
    pages: pageId (PK), bookId (FK to book), pageText
    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.

    Quote Originally Posted by DaveMaxwell View Post
    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...
    Here's the code I've got for the PHP template page that displays the contents of the pageText field from the database:

    PHP Code:
    <?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:

    dracula-output.png

    And this is what the first page of the A Christmas Carol title displays:

    a-christmas-carol-output.png

    But that's actually supposed to be the 2nd page the Dracula title!

    And here's a few of screenshots from MyPhpAdmin:

    pages-table-1.png

    books-table-1.png

    pages-table-2.png

    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 =/

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    It's this part: WHERE pages.pageID = books.bookID

    it should be: WHERE pages.bookID = books.bookID
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Guru bronze trophy AndrewCooper's Avatar
    Join Date
    Sep 2008
    Location
    Manchester, UK
    Posts
    631
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    It's this part: WHERE pages.pageID = books.bookID

    it should be: WHERE pages.bookID = books.bookID
    How newbie can a guy get...

    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!

    Cheers! Problem solved!

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by AndrewCooper View Post

    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!
    That's because of this piece
    PHP Code:
    while ($row $result->fetch()) 

        
    $title $row['bookTitle']; 
        
    $content $row['pageText']; 
        
    $page $row['pageID']; 

    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Guru bronze trophy AndrewCooper's Avatar
    Join Date
    Sep 2008
    Location
    Manchester, UK
    Posts
    631
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    That's because of this piece
    PHP Code:
    while ($row $result->fetch()) 

        
    $title $row['bookTitle']; 
        
    $content $row['pageText']; 
        
    $page $row['pageID']; 

    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.
    Thanks for the pointer Dave - Managed to get the pagination working correctly myself!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •