Efficient Database Queries ( PHP + PDO SQL)

Question

At the moment I have a database structure like so:

| id | name            | parent_id
| 1  | Human Resources | 0
| 2  | Marketing       | 0
| 3  | Operations      | 0
| 4  | Design          | 0
| 5  | Marketing Design| 4
| 6  | Graphic Design  | 4
| 7  | Print Design    | 4
| 8  | Human Personal  | 1
| 9  | Food Ops        | 3

As you can see these are the departments within the business and also sub departments.

A sub-departments parent_id is the id of the department

do, for example:

id: 4, name: Design, parent_id: 0

id: 7, Print Design, parent_id: 4

Print Design is a sub department of design

I have called everything from the database in one query and now I need them in this structure:

$depts = array(
   "Human Resources" => array("Human Personal"),
   "Marketing" => array(),
   "Operations" => array("Food Ops"),
   "Design" => array("Marketing Design", "Graphic Design", "Print Design"),
   ...
);

so far I have:

 foreach ($results as $result) {

    if ($result['parent_id'] == 0) {
        $parentCategories_arr[array($result['id'] => $result['name'])];
    } else {
        $returnedResults_arr[$result['parent_id']] = array($result['name']);
    }
}

However I completely think that I have missed the point. so my question:

How do I loop through all the contents of that results and add the parent categories into an array with their sub categories as an array?

you can use method which take parent id and get sub categories you can do this recursive — Abanoub Makram
– Abanoub Makram, Commented Apr 5, 2017 at 12:40

Antonios Tsimourtos · Accepted Answer · 2017-04-05 13:55:08Z

Maybe there is an easier way, but it works : (hate to say that sentence) - try to make it better maybe

$mFinalArray = fixMyArray($results);

print_r($mFinalArray);

function fixMyArray($results){

    // Create categories - parent_id == 0
    foreach($results as $index => $result)                           // $index = 0|1|2|3|4|5|6|7|8|9
        if($result['parent_id'] == 0)                                // $result['parent_id'] = current item parent_id
            $mCategories[$result['name']] = $result['id'];           // $mCategories['Human Resources'] = 1|2|3|4

    // Insert each data to the right parent
    foreach($results as $index => $result)                           // $index = 0|1|2|3|4|5|6|7|8
        if($result['parent_id'] != 0)
            foreach($mCategories as $subindex => $category)          // $subindex = Human Resources | Marketing | Operations | Design
                if($result['parent_id'] == $category)                // $category = 0|1|2|3|4
                        $mFinalArray[$subindex][] = $result['name']; // ex. $mFinalArray['Human Resources'][] = Human Personal

    return $mFinalArray;    

}

*Last line has an extra [ ] $mFinalArray[$subindex][ ]= $result['name'] . That means append to array.

Output :

Array
(
    [Design] => Array
        (
            [0] => Marketing Design
            [1] => Graphic Design
            [2] => Print Design
        )

    [Human Resources] => Array
        (
            [0] => Human Personal
        )

    [Operations] => Array
        (
            [0] => Food Ops
        )

)

Collectives™ on Stack Overflow

Efficient Database Queries ( PHP + PDO SQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related