Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Other SQL – list of pages with one-to-many relationships

  • This topic is empty.
Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #40890
    fooman
    Participant

    I have two tables, one for landing pages and one for sub-pages. They have a one-to-many relationship.

    **Structure for Landing Pages:**

    - id
    - title
    - content

    **Structure for Sub-pages:**

    - id
    - landing_id
    - title
    - content

    This is all well and good.
    How do I select everything in one query so that all pages are in order starting from the first landing page, it’s sub pages, next landing, sub-pages. etc etc.

    Do I have a really bad structure or is this a strange query to need?

    #114921
    __
    Participant

    SELECT p.id, p.title, s.id, s.title
    FROM pages p
    JOIN subpages s
    WHERE s.landing_id = p.id

    However, since all of your records represent pages, why not put them in the same table?

    CREATE TABLE page(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT
    ,parent INT UNSIGNED NOT NULL DEFAULT 0
    ,title VARCHAR(255) NOT NULL
    ,content TEXT NOT NULL
    )

    This uses a self-referential foreign key to indicate which page.id a sub-page belongs to. The default value “0” (which has no corresponding id) would indicate a top-level page.

    Then , your query is much simpler:

    SELECT id,title
    FROM page
    ORDER BY parent

    Which would give you a result like

    id | parent | title


    +


    +


    1 | 0 | landing page A
    2 | 0 | landing page B
    3 | 1 | page A subpage 1
    4 | 2 | page B subpage 1
    5 | 2 | page B subpage 2

    You can sort them into an array [map] from there.

    #114929
    __
    Participant

    > PS….. how did you do such an awesome code table haha. Mine was an epic fail :(

    just remember to count characters : )

    > As for your initial query, this will return all results that have subpages. And not those with only a landing page (no sub nav, for example).

    Sorry, I meant to write

    SELECT p.id, p.title, s.id, s.title
    FROM pages p
    LEFT JOIN subpages s
    ON s.landing_id = p.id

    > that’s the kind of list that’d be cool to return directly from a query. Right now the only thing I can think of doing is returning the data, and then dumping things into an array to sort it as needed.

    I think you’ll be stuck sorting it out in an array. There may be a way to do this in MySQL, but I don’t know that. It seems you’d need to make a preliminary query and then iterate over each result. You might be able to create a VIEW to accomplish this as well… I’ll let you know if I figure it out.

    #114931
    __
    Participant

    **********
    [this is a little complex](http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html), and requires stored procedures, but it seems it could be a solution.

    #114966
    __
    Participant

    “Round trips” to the database are very slow – connecting, querying, and retrieving a result can take as long as the whole rest of your script.

    Say you have five landing pages – that’s at least six queries, where you could have gotten the same info with only one. I’d recommend getting all of the records at once, if at all possible.

    IIUC, you might do something like this:

    SELECT menu_id,landing_id,title
    FROM page
    WHERE IS NOT NULL menu_id
    OR IS NOT NULL landing_id

    Then, in PHP, you can assemble your list:

    # organize resultset into two arrays: menu pages and submenu pages
    while( $r = $PDOresult->fetch( PDO::FETCH_NUM ) ){
    list( $menuID,$landingID,$title ) = $r;
    if( !empty( $menuID ) ){
    $menu[ $menuID ] = $title;
    }
    if( !empty( $landingID ) ){
    $submenu[ $landingID ][] = $title;
    }
    }
    # combine the two arrays into a flat list
    foreach( $menu as $key => $landingPage ){
    $list[] = $landingPage
    if( !empty( $submenu[ $key ] ) ){
    foreach( $submenu[ $key ] as $subPage ){
    $list[] = $subPage;
    }
    }
    }
    print ‘

    '.implode( "n",$list );

    If you have a table like this:

    menu_id | landing_id | title
    +
    +
    1 | NULL | landing page one
    2 | NULL | landing page two
    NULL | 1 | page one subpage A
    NULL | 2 | page two subpage A
    NULL | 2 | page two subpage B
    NULL | NULL | unassigned page (just for fun)

    You'll get a result like this:

    landing page one
    page one subpage A
    landing page two
    page two subpage A
    page two subpage B
    #115115
    __
    Participant

    no prob

Viewing 6 posts - 1 through 6 (of 6 total)
  • The forum ‘Other’ is closed to new topics and replies.