SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Issue with Query

    Hi guys,

    I am constructing a database in mysql to display some lists but cant seem to get the relational bit working.

    I have a 2 tables in a database, chunks + pages, I want them to be linked under chunks.id (unique primary key) and pages.chunkid (this can occur multiple times) in order to display either chunks.title or chunks.content.

    A secondary constraint is pages.optionid field = option variable and pages.pageid field = page variable - but i dont think I have a problem with this as i can get that working when i dont integrate chunks table.

    Not sure if I need to configure a foreign or secondary key in the database?

    This is my current query:

    public function BuildPagesList()
    {
    $myList = '';
    $option = 2;
    $page = 2;

    $result = $this->mysqlConnection>query('SELECT * FROM chunks WHERE chunks.id = pages.chunkid AND pages.optionid="' . $option . '" AND pages.pageid="' . $page . '" ' . 'ORDER BY order_no ASC');

    while ($row = $result->fetch_assoc())
    {
    $myList .= '<li id="' . ($row['id']) . '">' . ($row['chunks.chunkTitle']) . '</li>';
    }
    return $myList;
    }


    Currently this is displaying an error "Fatal error: Call to a member function fetch_assoc() on a non-object" in relation to this row "while ($row = $result->fetch_assoc())"

    Any help would be gratefully received. Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    you have to join chunks to pages.
    You're getting that error because the query finished with an error.

  3. #3
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you fixed now

    Solution:

    $result = $this->mysqlConnection->query('SELECT chunks.* FROM chunks LEFT JOIN pages ON chunks.id = pages.chunkid WHERE chunks.id = pages.chunkid AND pages.optionid="' . $option . '" AND pages.pageid="' . $page . '" ' . 'ORDER BY order_no ASC');
    while ($row = $result->fetch_assoc())
    {
    $myList .= '<li id="' . ($row['id']) . '">' . ($row['title']) . '</li>';
    }
    return $myList;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    SELECT chunks.*
    FROM chunks LEFT JOIN pages
    ON chunks.id = pages.chunkid
    WHERE chunks.id = pages.chunkid
    please remove that redundant WHERE condition, as it's redundant

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Not only do you have a redundant where condition like r937 says, but you also put the pages table in the WHERE clause, and that means the LEFT JOIN has become an INNER JOIN. So if you need a LEFT JOIN, put them in the ON clause instead:
    Code:
    SELECT chunks.* 
    FROM chunks 
    LEFT JOIN pages 
    ON chunks.id = pages.chunkid 
    AND pages.optionid='$option' 
    AND pages.pageid='$page' 
    ORDER BY order_no ASC');
    If you don't need an LEFT JOIN, change it in an INNER JOIN.

  6. #6
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Not only do you have a redundant where condition like r937 says, but you also put the pages table in the WHERE clause, and that means the LEFT JOIN has become an INNER JOIN. So if you need a LEFT JOIN, put them in the ON clause instead:
    Code:
    SELECT chunks.* 
    FROM chunks 
    LEFT JOIN pages 
    ON chunks.id = pages.chunkid 
    AND pages.optionid='$option' 
    AND pages.pageid='$page' 
    ORDER BY order_no ASC');
    If you don't need an LEFT JOIN, change it in an INNER JOIN.

    Thanks for the additional help from both of you. Solution now is:

    $result = $this->mysqlConnection->query('SELECT chunks.* FROM chunks INNER JOIN pages ON chunks.id = pages.chunkid WHERE pages.optionid="' . $option . '" AND pages.pageid="' . $page . '" ' . 'ORDER BY pages.order_no ASC');

    while ($row = $result->fetch_assoc())
    {
    $myList .= '<li id="' . ($row['id']) . '">' . ($row['title']) . '</li>';
    }
    return $myList;

    However, I have discovered a problem... I need title (or could be content) from chunks to be displayed, but the id for the row needs to be
    pages.id NOT chunks.id as it currently is for the delete and re-order of the list to work. Any idea's how to put it into practice? I have tried doing:

    $myList .= '<li id="' . ($row['pages.id']) . '">' . ($row['chunk.title']) . '</li>';

    and

    $myList .= '<li pages.id="' . ($row['id']) . '">' . ($row['title']) . '</li>';

    with no success.

    Thanks for the help again

  7. #7
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If that isnt clear then i'll try and explain it again

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you answer a couple questions about it?

    first, you provide a value for pages.pageid, so, if my assumption is right and this value uniquely identifies a single page, then why are you also suppling values for that page's optionid? how many optionids can a page have? and if more than one, why is the table called pages instead of someting like pageoptions? is there an options table?

    secondly, which columns do you want back? (please don't say "all") i would've asked this anyway, but you seem to have stumbled on the problem yourself:
    I need title (or could be content) from chunks to be displayed, but the id for the row needs to be
    pages.id NOT chunks.id as it currently is
    if you can clarify those things, we can fix the query easily
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you need the pages_id, you'll have to select it in your query. And you can't do this:
    $row['chunk.title']

    Instead, select each individual field in the query, and if two fields from the two tables have the same name (like 'id') give them an alias:
    Code:
    SELECT pages.id AS page_id
    You can then use that alias in your PHP code.

  10. #10
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you answer a couple questions about it?

    first, you provide a value for pages.pageid, so, if my assumption is right and this value uniquely identifies a single page, then why are you also suppling values for that page's optionid? how many optionids can a page have? and if more than one, why is the table called pages instead of someting like pageoptions? is there an options table?

    secondly, which columns do you want back? (please don't say "all") i would've asked this anyway, but you seem to have stumbled on the problem yourself:if you can clarify those things, we can fix the query easily
    Ok, let me try and clarify

    A user creates a chunk - which is simply a title and content.

    Chunks are stored in chunks table which have the following table:

    ID (unique PK)
    order_no (to control the order of the list - is changed when list changes with new values stored)
    username (got from session and allows user to only see the chunks he creates)
    title (chunk title)
    content (chunk content)

    ------

    A user then can add as many chunks into a page as they like - including repation if they like. These links are stored in pages table (might be renamed later) but are only links as the chunk could be modified and I want the data integrity to be maintained.

    pages table:

    ID (primary key, unique)
    order_no (to control order of pages list - can be updated)
    chunkid (this is the chunk id to provide link to chunks)
    optionid (will explain below)
    pageid (will explain below)

    --------

    Options - a user will have the ability to create various options - this could be for example beginner, intermediate, advanced. an option will have an ID.

    For each option there will be a set of pages. Each page will contain a number of chunks. Each page has an ID in order to be able to identify which page of which option is being displayed.

    -------

    so basically:

    option choice -> page choice -> set of chunks

    ------

    in order for the control of delete and re-order the ID in the query needs to be pages.id, though i also need to create a query to delete all links in the pages table when a chunk is deleted from the chunk table but i figure once I have a working model to work from then it shouldnt be too hard to adapt that.

    Thanks again for the help

  11. #11
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There will of course be tables for the options and pages but I have just used default values for now

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your initial post was exactly the opposite, many pages for each chunk:
    I have a 2 tables in a database, chunks + pages, I want them to be linked under chunks.id (unique primary key) and pages.chunkid (this can occur multiple times)
    the solution you are seeking is to avoid the dreaded, evil "select star" and use column aliases

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Vanq69 View Post
    ... though i also need to create a query to delete all links in the pages table when a chunk is deleted from the chunk table
    the problem here is that the pages table should ~not~ be linking to the chunks table if each page has multiple chunks

    the link (called a foreign key) resides in the "many" table of a one-to-many relationship

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your initial post was exactly the opposite, many pages for each chunk:
    the solution you are seeking is to avoid the dreaded, evil "select star" and use column aliases

    The idea is that there is 1 chunk - which can be used/linked to on multiple pages or more than once on the same page.

    Yes it appears that is the better move - will have a try.

  15. #15
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the problem here is that the pages table should ~not~ be linking to the chunks table if each page has multiple chunks

    the link (called a foreign key) resides in the "many" table of a one-to-many relationship

    Yeah foreign key was what I was looking for - my previous DB experience is with access.

    [tried to post link but need 10 posts!]

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't think posting a password on a public forum which gets hundreds of thousands of views (not necessarily by registered members) is a good idea

    there must be other ways of describing whatever you need to tell us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good point!

    Here is a screenshot instead...



    Hope this helps explain the concept. Formatting is a bit rough atm but its just a working model.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you'll need 3 tables in all

    1 table for the pages, 1 table for the chunks, and a third "many-to-many" or relationship table which keeps track of which chunks are on which page

    sorry i did not realize this sooner
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you'll need 3 tables in all

    1 table for the pages, 1 table for the chunks, and a third "many-to-many" or relationship table which keeps track of which chunks are on which page

    sorry i did not realize this sooner
    Definitely no need to apologise!

    I think I will need 4 in all? (including the options - may as well put it in now)

    Chunks table - as is?

    pages table - containing details of the pages per option?
    ID, optionID, chunkID?

    options table
    ID, pageID?

    relationship table:
    id, chunk id, page's appear on?

    how would order for the page list be managed?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Chunks table - as is?
    no, remove order_no

    pages table - containing details of the pages per option?
    yes

    options table
    no, remove pageID

    relationship table:
    should ~not~ have id of its own
    use chunkid,pageid as a composite primary key
    add order_no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •