mysql select recursive query

Question

I have this table:

table with records example

I am trying show a hierarchical combo something like this:

combo

It is posible to do this using a select statement or how I can implement this via PHP? I was googling and I found that it is posible using recursive functions but it is new for me and I can not implement this. May be somebody give me an idea?

Update:- I finally solved my problem. I create a recursive function based on @user2433317 code, first I create and initialized a variable to control the amount of blank spaces that I need in each level

$n=-3;

next I write the recursive function

function ShowSubCats($id){
global $cn, $n;
$sql = "SELECT * FROM menus WHERE idPadre = '$id';";
$r = mysqli_query($cn, $sql);       
if (mysqli_num_rows($r) > 0) {
    $n+=3;
    while ($row = mysqli_fetch_object($r)) {
        echo "<option value='$row->id'>".space($n).$row->titulo."</option>" ;
        ShowSubCats($row->id);                  
    }
        $n-=3;
    }
}

and call it with 0 argument that mean idPadre=0 don't have parent. Finally this is the space function

function space($n){
$str = "";
for ($i=0; $i<$n; $i++) { 
    $str .= "&nbsp;";
}
return $str;

}

and the result is showed here

enter image description here

thank you for your answers and sorry for my english is not very good

ajhanna88 · Accepted Answer · 2018-04-13 04:52:11Z

4

Something like this should do the trick, get parent categories, then call a rescursive function to get all childs.

$sql = "SELECT * FROM table WHERE idParent = 0 ";
$results = mysqli_query($sql);
while ($row = mysqli_fetch_array($results)) {
    echo '<li>' . $row['title']) . '</li>';
    ShowSubCats($row['id']);
}


function ShowSubCats($categoryid) {

    $sql = "SELECT * FROM table WHERE idparent='$categoryid';";
    $results = mysqli_query($sql);

    if (mysqli_num_rows($results) > 0) {
        while ($row = mysqli_fetch_array($results)) {
            echo '<li>' . $row['title'] . '</a>';
            ShowSubCats($row['idparent']);
            echo '</li>';
        }
    }

}

edited Apr 13, 2018 at 4:52

answered Jul 28, 2014 at 3:04

ajhanna88

886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:01:28Z

First make a function called has_parent():

function has_parent( $id )
{
    global $connection;
    $sql = "SELECT parent FROM table WHERE id = {$id} LIMIT 1";
    $results = mysqli_query($sql);
    $row = mysqli_fetch_assoc($results);

    if ( $row == 0 )
    {
        return false;
    } else {
        return true;
    }
}

Make it perfect yourself name the fields etc. and warning I didn't test it. It'll basically return true if the given id has a parent and false elsewise.
Next Loop through all the entries in the database:
echo ''; while( $row = mysqli_fetch_assoc($results_of_mysqli_for_all_results) ){

   ?>

        <?php if (!has_parent( $row['id'] )): ?>
            <li><?php echo $row['yourField']; ?></li>
        <?php else: ?>
            <li><?php echo $row['yourField'] ?>
                <?php
                    echo '<ul>';
                    // loop through all the entries with the parent = $row['id'] ( I think you can make this yourself )
                    echo '</ul>';
                ?>
            </li>
        <?php endif ?>
   <?php  

}
echo '</ul>';

EDIT:
If you want select menu see this question.
EDIT:
Ask me if you have any questions or if it didn't work ( I didn't test it myself though )

Collectives™ on Stack Overflow

mysql select recursive query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related