Show different MySQL rows within the same results

[howard-moore]

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

[Fou-Lu]

Is it only ever two levels of depth, or can it have infinite depth level?

[howard-moore]

I'm currently only planning two levels, but may add another level in the future if it is viable.

[Fou-Lu]

Originally Posted by howard-moore

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.

[howard-moore]

Originally Posted by Fou-Lu

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.

[Fou-Lu]

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?

[howard-moore]

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.

[howard-moore]

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

[howard-moore]

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:

<?
$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

[Fou-Lu]

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.