Grow your CSS skills. Land your dream job.

SQL – list of pages with one-to-many relationships

  • # November 20, 2012 at 9:50 am

    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?

    __
    # November 20, 2012 at 10:56 am

    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.

    # November 20, 2012 at 11:08 am

    I thought of doing the structure like that, but I thought it’d be easier to keep track of if I could keep the pages in separate tables. It’s just how I figured it’d make sense to do things.

    I definitely thought of doing things all in one table, but decided it’d be easier to keep track of in the future as things grow a bit.

    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). I have a similar query where I return basically the same thing (including permalinks which are stored in another table).

    What would be super-awesome is returning something such as…


    id | title
    +
    +
    1 | landing page A
    2 | page A subpage 1
    3 | landing page B
    4 | page B subpage 1
    5 | page B subpage 2

    I realize I'd need to return more data, but 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.

    # November 20, 2012 at 11:09 am

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

    __
    # November 20, 2012 at 11:42 am

    > 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.

    __
    # November 20, 2012 at 11:57 am

    **********
    [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.

    # November 20, 2012 at 2:19 pm

    jesus murphy that seems like a lot of reading for what we’re trying to accomplish.
    I’ve actually taken your advice about using one table.

    I’ve found that without doing a thesis on database structure, I use PDO statements to iterate over the results and order things that way.

    So I do the following:
    1. Grab all pages that have a menu_id (only landing pages have this).
    2. Iterate over the results. Store them in a nice lil array.
    3. For each, grab the sub-pages that have a landing_id that matches the landing page’s id. Store them in the array.
    4. List out the array. Everything will be in order as expected.

    It’s more queries… but I honestly have no idea how to do this more efficiently. And I don’t see a noticeable gain in performance being worth the time :(

    __
    # November 20, 2012 at 4:03 pm

    “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:

    < ?php
    # 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
    # November 21, 2012 at 10:04 am

    you, sir… are a saviour. I didn’t even look at how simple this could be by using PHP moreso than MySQL :)

    Thanks for the help!

    __
    # November 21, 2012 at 9:41 pm

    no prob

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".