Luvz Luvz - 3 months ago 8
MySQL Question

how to make function in PHP with repeatedly querying a database

My Code is working but i'm not satisfied with this, it looks very dirty.
My Problem is, How can I make a function for my code to be not repeating.
Here's my code,

<?php
// PARENT MENU
$q2 = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = (SELECT MIN(parent) from userpage) ORDER BY t.sort ASC";
$r2 = $db->query($q2);
$a2 = $db->fetch_all_array($q2);
foreach($a2 as $k => $v)
{
$indent = "&#10149;";
$tab = "&emsp;";
$menu_id = $v['m_id'];
echo $v['pagename'] . "(" . $v['page'] . ")";
// 1ST SUB MENU
if ($menu_id != '')
{
$q3 = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id . " ORDER BY t.sort ASC";
$r3 = $db->query($q3);
$a3 = $db->fetch_all_array($q3);
foreach($a3 as $k3 => $v3)
{
$menu_id2 = $v3['m_id'];
echo $v3['pagename'] . "(" . $v3['page'] . ")";
// 2ND SUB MENU
if ($menu_id2 != '')
{
$q4 = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id2 . " ORDER BY t.sort ASC";
$r4 = $db->query($q4);
$a4 = $db->fetch_all_array($q4);
foreach($a4 as $k4 => $v4)
{
$menu_id3 = $v4['m_id'];
echo $v4['pagename'] . "(" . $v4['page'] . ")";
// 3RD SUB MENU
if ($menu_id3 != '')
{
$q5 = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id3 . " ORDER BY t.sort ASC";
$r5 = $db->query($q5);
$a5 = $db->fetch_all_array($q5);
foreach($a5 as $k5 => $v5)
{
$menu_id4 = $v5['m_id'];
echo $v5['pagename'] . "(" . $v5['page'] . ")";
// 4TH SUB MENU
if ($menu_id4 != '')
{
$q6 = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id4 . " ORDER BY t.sort ASC";
$r6 = $db->query($q6);
$a6 = $db->fetch_all_array($q6);
foreach($a6 as $k6 => $v6)
{
$menu_id5 = $v6['m_id'];
echo $v6['pagename'] . "(" . $v6['page'] . ")";
// 5TH SUB MENU
if ($menu_id5 != '')
{
$q7 = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id5 . " ORDER BY t.sort ASC";
$r7 = $db->query($q7);
$a7 = $db->fetch_all_array($q7);
foreach($a7 as $k7 => $v7)
{
$menu_id6 = $v7['m_id'];
echo $v['pagename'] . "(" . $v['page'] . ")";
} //5th submenu
} //closing if for 5th submenu
} //4th submenu
} //closing if for 4th submenu
} //3rd submenu
} //closing if for 3rd submenu
} //2nd submenu
} //closing if for 2nd submenu
} //1st submenu
} //closing if for 1st submenu
} //parent menu
?>


Thank You.
Any answer is highly appreciated.

Answer

You can change your code to use recursion. The simplest way to do this will still call the database multiple times but you won't need them all in code.

Calling the function below will write out the submenu and all its children / grandchildren etc...

It works using recursion which in this context means the function calls itself to complete its task.

function subMenu($menu_id) {
    $q = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id . " ORDER BY t.sort ASC";
    $r = $db->query($q);
    $a = $db->fetch_all_array($q);
    foreach($a2 as $k => $v)
    {
        $menu_id2 = $v['m_id'];
        echo $v['pagename'] . "(" . $v['page'] . ")";
        if ($menu_id2 != '') {
            // write out the submenu of this submenu
            subMenu($menu_id2);
        }
    }
}

With an indent counter. Pass in 0 to indent_count

function subMenu($menu_id, $indent_count) {
    $q = "SELECT t.*, t2.id as m_id FROM userpage t left join menu t2 on (t2.parent=t.parent and t2.sort=t.sort) WHERE t.parent = " . $menu_id . " ORDER BY t.sort ASC";
    $r = $db->query($q);
    $a = $db->fetch_all_array($q);
    foreach($a2 as $k => $v)
    {
        $menu_id2 = $v['m_id'];
        // You need to add in an indent based on $indent_count
        echo $v['pagename'] . "(" . $v['page'] . ")";
        if ($menu_id2 != '') {
            // write out the submenu of this submenu
            subMenu($menu_id2, $indent_count + 1);
        }
    }
}
Comments