Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    May 2008
    Posts
    119
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Show different MySQL rows within the same results

    Hi,

    My problem is a little complex, so I will try to set it out as simply as possible for you!

    I have a MySQL database that has two types of page entries:

    type = TOP_PAGE or SUB_PAGE
    page_order = 1-9
    parent_page = list of top pages
    content = page content

    Therefore, the idea is that all top-level pages have a page order and all the sub-pages have a page order, and all the pages have what I have termed a 'parent page', which is their 'group' of pages. What I can't work out how to do is set these pages out in the correct menu structure, which has to be in the following order:

    1. Top-level pages by page_order (1-9)
    2. Sub-pages shown underneath each corresponding top-level page also shown by their respective page order (i.e. each group of sub-pages will have it's own 1-9 page order).

    Any advice on whether this is possible would be greatly received.

    Neil

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Is it only ever two levels of depth, or can it have infinite depth level?

  • #3
    Regular Coder
    Join Date
    May 2008
    Posts
    119
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I'm currently only planning two levels, but may add another level in the future if it is viable.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by howard-moore View Post
    I'm currently only planning two levels, but may add another level in the future if it is viable.
    This indicates that you'll need to use recursion (or a hellalot of querying within the recursion, which I don't recommend); alternatively in the OO world you could write a tree. I don't recommend writing it to match just the two levels if there is a possibility the depth will change (otherwise you could actually query it in a way that it builds the lists into a logical array with minimal PHP work, but it is tied down whatever level you query). Too bad RDBMS aren't recursive.

    I'll try to write something up between work here today. The type column isn't necessary btw, I'll assume that a parent_page of either 0 or null would dictate whether it is located on top or not (I'll assume 0). Either case I'll typically create a root parent anyway since it makes the recursion easier.

    Edit:
    BTW, what is this page content here? Is it actually a text type or something more along a title? You want to try and minimize the data per fetch if you can.
    Last edited by Fou-Lu; 12-28-2012 at 03:44 PM.

  • #5
    Regular Coder
    Join Date
    May 2008
    Posts
    119
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    This indicates that you'll need to use recursion (or a hellalot of querying within the recursion, which I don't recommend); alternatively in the OO world you could write a tree. I don't recommend writing it to match just the two levels if there is a possibility the depth will change (otherwise you could actually query it in a way that it builds the lists into a logical array with minimal PHP work, but it is tied down whatever level you query). Too bad RDBMS aren't recursive.

    I'll try to write something up between work here today. The type column isn't necessary btw, I'll assume that a parent_page of either 0 or null would dictate whether it is located on top or not (I'll assume 0). Either case I'll typically create a root parent anyway since it makes the recursion easier.

    Edit:
    BTW, what is this page content here? Is it actually a text type or something more along a title? You want to try and minimize the data per fetch if you can.
    Wow - I hadn't realised it would be quite so complex! I had no idea there would be OO or recursion required, but any pointers you could give me would be wonderful!

    To answer your question, the page content will simply be html code (edited via a text-box / ckeditor tool). Also, the TYPE field is required, as the database is also used for a number of other record types, of which TOP_PAGE or SUB_PAGE are just a couple of types.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Okay, I'm not 100% sure I follow what you are needing to do here.
    I'm thinking like an infinite depth link tree, but it sounds more like these are entire pages. You definitely don't want to pull up the entire pages on each load. Unless these are simply short chunks of code?

  • #7
    Regular Coder
    Join Date
    May 2008
    Posts
    119
    Thanks
    13
    Thanked 0 Times in 0 Posts
    You are absolutely right. Essentially I'm looking at building a dynamic menu/sitemap, with the entries being pages and sub-pages. I will not need to pull up the entire pages.

  • #8
    Regular Coder
    Join Date
    May 2008
    Posts
    119
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I've been doing a bit more reading, and basically what I am looking to do is a sort of dynamic menu using PHP and MySQL, but utilising the above fields. The issue I have is that I want the results to sort as follows:

    1. Sort TOP_PAGE's by page_order
    2. Show corresponding SUB_PAGE's below each 'parent' TOP_PAGE
    3. Sort the SUB_PAGE's at '2' above by page_order

    Thanks,
    Neil

  • #9
    Regular Coder
    Join Date
    May 2008
    Posts
    119
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Hi All,

    Just an update to let you know that I have managed to solve this. The code is a little messy and complex because of the stylesheets that I use for it, but it works like this:

    Code:
    <?
    $result=mysql_query("SELECT id, title, link, parent_code, page_order, menu_title, parent_title, page_type, live, editable, installed FROM $filename WHERE type='PAGE' AND installed='checked' ORDER BY parent_code, page_order, title");
    $menu = array(
        'items' => array(),
        'parents' => array()
    );
    while ($items = mysql_fetch_assoc($result))
    {
        $menu['items'][$items['id']] = $items;
        $menu['parents'][$items['parent_code']][] = $items['id'];
    }
    function buildMenu($parent_code, $menu)
    {
       $html = "";
       if (isset($menu['parents'][$parent_code]))
       {
          $html .= "\n";
           foreach ($menu['parents'][$parent_code] as $itemId)
           {
              if(!isset($menu['parents'][$itemId]))
              {
                 $html .= 	"<tr><td class=\"".$menu['items'][$itemId]['page_type']."_EDIT\"><ul class=\"edit clear\"><li><a href=\"admin_pages_".$menu['items'][$itemId]['page_type'].".php?eid=".$menu['items'][$itemId]['id']."\">Edit / Delete<span></span></a></li></ul></td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_l\">".$menu['items'][$itemId]['title']."</td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_l\">".$menu['items'][$itemId]['parent_title']."</td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_c\">".$menu['items'][$itemId]['page_order']."</td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_c\"><img src=\"../data/admin_docs_".$menu['items'][$itemId]['live'].".gif\" title=\"Showing Page\"></td></tr>";
              }
           $html .= "</tr> \n";
              if(isset($menu['parents'][$itemId]))
              {
                 $html .= 	"<tr><td class=\"".$menu['items'][$itemId]['page_type']."_EDIT\"><ul class=\"edit clear\"><li><a href=\"admin_pages_".$menu['items'][$itemId]['page_type'].".php?eid=".$menu['items'][$itemId]['id']."\">Edit / Delete<span></span></a></li></ul></td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_l\">".$menu['items'][$itemId]['title']."</td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_l\">".$menu['items'][$itemId]['parent_title']."</td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_c\">".$menu['items'][$itemId]['page_order']."</td>
    				<td class=\"".$menu['items'][$itemId]['page_type']."_b_c\"><img src=\"../data/admin_docs_".$menu['items'][$itemId]['live'].".gif\" title=\"Showing Page\"></td></tr>";
                 $html .= buildMenu($itemId, $menu);
              }
           }
           $html .= "\n";
       }
       return $html;
    }
    echo buildMenu(0, $menu);
    ?>
    I hope that this helps someone somewhere, and thanks for the help all have given me.

    Neil

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Oh yay I hate writing recursive arrays as trees.
    You *can* also let the function completely flatten the dataset of the $menu array if desired. Ultimately if I do a tree with arrays in PHP, I'd have two functions anyway, the first one would build the correct nesting level for the items, and the second to display it (both accept the same dataset). That is similarish to what you've done here with the two items in the $menu pulled from the fetch.


  •  

    Posting Permissions

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