phpmyadmin Question posted in
The official documentation can be found here.

How can I combine my recursive function with another argument – Phpmyadmin

I have a script that works recursively and is able to show a sub menu multiple levels deep. The problem is this: at the moment I work with two parts in my CMS.

Categories and articles.

Articles can be under categories and categories can be under other categories (subcategories), but articles can’t be under other articles.

Currently my script only works with categories but if for example in my CMS article 1 and category1 are both under category0 I want to show them both, not just only category1.

In my database the structure is like this:

`snm_categories` - contains category data
`id` - the id of a category
parent_id - the id of it's parent category, this is `1` if it has no parent


`snm_content` - contains the article data
`catid` - the id of the category it falls under, in my above example this will be the same id as the parent_id of `category1`

This is my script:

<?PHP
// Get all categories and articles
$menu = "
SELECT cat.id as cat_id, cat.level, cat.parent_id, cat.title as cat_title, cat.alias as cat_alias, cat.published, cat.rgt, cnt.state, cnt.id as content_id, cnt.catid, cnt.title as content_title, cnt.alias as content_alias
FROM snm_categories cat
LEFT JOIN snm_content cnt
ON cnt.catid = cat.id
WHERE cat.id NOT IN (1, 2, 3, 4, 5, 7, 8, 22)
AND cat.published = 1
GROUP BY cat.id
ORDER BY cat.rgt ASC";
$menuconn = $conn->query($menu);
// Create new array
$menuData = array(
    'items' => array(),
    'parents' => array()
);
// Create new array with `items` and `parents`, which contain cat_id and parent_id
while($menu = $menuconn->fetch_assoc())
{
    $menuData['items'][$menu['cat_id']] = $menu;
    $menuData['parents'][$menu['parent_id']][] = $menu['cat_id'];
}

// Function to create menu, $parentId is 1 (when categories have no parent and are topcategories)
function buildMenu($parentId, $menuData)
{
    $html = '';
    if (isset($menuData['parents'][$parentId]))
    {
        // If parent_id is 1 put a <li> around it (because it's not a subcat) if not put an <ul> around
        if($parentId == '1'){
          $html = '<li>';
        }else{
          $html = '<ul class="sub-menu">';
        }
        foreach ($menuData['parents'][$parentId] as $itemId)
        {
            $html .= '<li class="menu-item"><a href="info/'.$menuData['items'][$itemId]['cat_alias'].'">'.$menuData['items'][$itemId]['cat_title'].'</a>';
            // Use this function recursively
            $html .= buildMenu($itemId, $menuData);
            $html .= '</li>';
        }
        if($parentId == '1'){
          $html .= '</li>';
        }else{
          $html .= '</ul>';
        }
    }
    return $html;
}
// Echo menu
echo buildMenu(1, $menuData);
?>

It makes sense only categories are shown because that’s all there is inside the function, so I tried changing it to this:

// Create new array with `items` and `parents`, which contain cat_id and parent_id
while($menu = $menuconn->fetch_assoc())
{
    $menuData['items'][$menu['cat_id']] = $menu;
    $menuData['parents'][$menu['parent_id']][] = $menu['cat_id'];

    // Get the articles under the correct category
    $submenu = "SELECT * FROM snm_content WHERE catid = '".$conn->real_escape_string($menu['cat_id'])."' AND catid NOT IN (8,22) AND state = 1 ORDER BY ordering";
    $submenuconn = $conn->query($submenu);
    while($submenu = $submenuconn->fetch_assoc()){
      $artikelsubs = '<li class="menu-item"><a href="info/'.$submenu['alias'].'">'.$submenu['title'].'</a>';
    }
}

// Function to create menu, $parentId is 1 (when categories have no parent and are topcategories)
function buildMenu($parentId, $menuData)
{
    $html = '';
    if (isset($menuData['parents'][$parentId]))
    {
        // If parent_id is 1 put a <li> around it (because it's not a subcat) if not put an <ul> around
        if($parentId == '1'){
          $html = '<li>';
        }else{
          $html = '<ul class="sub-menu">';
        }
        foreach ($menuData['parents'][$parentId] as $itemId)
        {
            $html .= '<li class="menu-item"><a href="info/'.$menuData['items'][$itemId]['cat_alias'].'">'.$menuData['items'][$itemId]['cat_title'].'</a>';
            // Use this function recursively
            $html .= buildMenu($itemId, $menuData);
            $html .= '</li>';
        }
        if($parentId == '1'){
          $html .= '</li>';
          $html .= $artikelsubs;
        }else{
          $html .= '</ul>';
        }
    }
    return $html;
}
// Echo menu
echo buildMenu(1, $menuData);

But still I don’t see any articles which is strange because when I try the query of $submenu and use a hardcoded catid in phpmyadmin it shows me the correct data.

How can I also show articles in my menu?

Tags:

2

Answers


  1. 0

    First problem i see is in your code at below

    while($submenu = $submenuconn->fetch_assoc()){
      $artikelsubs = '<li class="menu-item"><a href="info/'.$submenu['alias'].'">'.$submenu['title'].'</a>';
    }

    If there are 10 articles, you are storing the last one? Also you are not ending the li tag for some reason.

    Since I don’t have the DB to check this, but as per my understanding the loop should be something like below

    while($submenu = $submenuconn->fetch_assoc()){
      $artikelsubs .= '<li class="menu-item"><a href="info/'.$submenu['alias'].'">'.$submenu['title'].'</a></li>';
    }

    Also if the articles list is based on the cat_id then you should be storing this data also

    $menuData['articles'][$menu['cat_id']] = $artikelsubs;

    And make sure to blank the artikelsubs before the inner while loop. Then you need
    replace your $html .= $artikelsubs; to fetch it from the menuData object

    Login or Signup to reply.
  2. 0

    I know this is very different from your code, but I wrote this a couple weeks ago, for a similar situation, so I adapted it a little bit to your situation:

    <?php
echo get_items(1);

function get_items($id){
    global $conn; //db connection object
    $html = '';
    $sql = mysqli_query($conn, "SELECT * FROM snm_content WHERE catid = $id");
    while($row = mysqli_fetch_assoc($sql)){
        $html .= '<li>'.$row['title'].'</li>';
    }
    $sql = mysqli_query($conn, "SELECT * FROM snm_categories WHERE parent_id = $id");
    while($row = mysqli_fetch_assoc($sql)){
        $html .= '<li>'.$row['title'].'
                      <ul>'.get_items($row['id']).'</ul>
                  </li>';
    }
    return $html;
}
?>

    I think you can easily modify it to get the exact html markup you need, but the essence is there. Don’t hesitate if you have any questions about it !

    By the way, i used the same DB structure as you, and I entered some rows in snm_content and snm_categories, and this is the output I get:

    <li>cat 0
    <ul>
        <li>article 0</li>
        <li>cat 1
            <ul>
                <li>article 1</li>
                <li>article 2</li>
            </ul>
        </li>
        <li>cat 2
            <ul>
                <li>article 3</li>
            </ul>
        </li>
    </ul>
</li>
<li>cat 3
    <ul>
        <li>article 4</li>
    </ul>
</li>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search