CODEWITHSUNDEEP
phpmysqlarraysrecursion
GarouDan
GarouDan

Reputation: 3833

Specif array with root and child categories

Using PHP and MySQL.

I have a table (category) with three fields only, id, name, parent.

Where parent is 0 on root category and a integer if it is derived from another.

But we can have several levels inside this, for example:

id, name, parent
01, Name 01, 00
02, Name 02, 00
03, Name 03, 00
04, Name 04, 02
05, Name 05, 01
06, Name 06, 01
07, Name 07, 05
08, Name 07, 05
09, Name 09, 04
10, Name 10, 07
11, Name 11, 10

How could we return an array containing the root and all of its subnodes? For example, for id=1, the array should be:

array (
    1 => array(
        id => 1,
        name => 'Name 1'
    ),
    5 => array(
        id => 5,
        name => 'Name 5'
    ),
    6 => array(
        id => 6,
        name => 'Name 6'
    ),
    7 => array(
        id => 7,
        name => 'Name 7'
    ),
    8 => array(
        id => 8,
        name => 'Name 8'
    ),
    10 => array(
        id => 10,
        name => 'Name 10'
    ),
    11 => array(
        id => 11,
        name => 'Name 11'
    )
);

I had take a look at this link and tried some variations but I haddnt success yet.

Upvotes: 0

Views: 116

Answers (1)

Michael Marr
Michael Marr

Reputation: 2099

This SQL query should get the data you want:

    SELECT DISTINCT c.id as id, c.name as name FROM category c, category s WHERE s.parent = $id OR c.id = $id;

Then you could use mysqli_fetch_assoc to get part of the array you need. If you needed the array indices to match up w/ the SQL indices, then you could just iterate over the result of mysqli_fetch_assoc and put into a new array:

    $target = array();
    while ( $row = mysqli_fetch_assoc( $result ) ) {
         $target[$row["id"]] = $row;
    }

Upvotes: 1

Related Questions