Nesting Categories for MySQL

Question

The API returns the following categories:

[1] => Array ( [category_id] => 14308 [parent_id] => 14308 [level] => 0 ) [2] => Array ( [category_id] => 179836 [parent_id] => 14308 [level] => 1 ) [3] => Array ( [category_id] => 230022 [parent_id] => 179836 [level] => 2 ) 

And I need to insert them into the database like this:

 ╔═════════════╦═══════════╗ ║ category_id ║ parent_id ║ ╠═════════════╬═══════════╣ ║ 14308 ║ 0 ║ ║ 179836 ║ 14308 ║ ║ 230022 ║ 179836 ║ ╚═════════════╩═══════════╝ 

... which is easy, but the category_to_path table is complicated, and I cannot understand. It should be like this:

 ╔═════════════╦═════════╦═══════╗ ║ category_id ║ path_id ║ level ║ ╠═════════════╬═════════╬═══════╣ ║ 14308 ║ 14308 ║ 0 ║ ║ 179836 ║ 14308 ║ 0 ║ ║ 179836 ║ 179836 ║ 1 ║ ║ 230022 ║ 14308 ║ 0 ║ ║ 230022 ║ 179836 ║ 1 ║ ║ 230022 ║ 230022 ║ 2 ║ ╚═════════════╩═════════╩═══════╝ 

This is what I still have:

 $path_ids; for ($i=0; $i <= ($category->CategoryLevel-1); $i++) { // -1 cause the API returns a non zero-based level $path_ids[$i]['category_id'] = $category->CategoryID; if ($category->CategoryLevel-1 == $i) { $path_ids[$i]['path_id'] = $category->CategoryParentID[0]; } elseif ($category->CategoryLevel-1) { // ? } $path_ids[$i]['level'] = $i; } 

Answer 1

The following query should provide you with categories and paths. It does not give levels, since there is no line number in your source data. You may need to use dynamic SQL if you want to get levels.

The strategy I used is cross-joining categories, which generates all possible start and end points. It also generates some paths not supported by the table. To remove these unwanted paths, I check each path for a subquery to make sure that it can be created by moving from category to path in 3 steps or less.

 SELECT t1.category_id AS category_id, t2.category_id AS path_id FROM yourTable t1 CROSS JOIN yourTable t2 INNER JOIN ( SELECT t1.category_id AS category_id_1, t2.category_id AS category_id_2, t3.category_id AS category_id_3 FROM yourTable t1 LEFT JOIN yourTable t2 ON t1.parent_id = t2.category_id LEFT JOIN yourTable t3 ON t2.parent_id = t3.category_id ) t2 ON t1.category_id = t2.category_id_1 AND t2.category_id IN (t2.category_id_1, t2.category_id_2, t2.category_id_3) ORDER BY category_id, path_id 

Answer 2

I would try to rewrite the array after getting it from the API so that it looks like this:

 [14308] => Array ( [parent_id] => 14308 [level] => 0 ) [179836] => Array ( [parent_id] => 14308 [level] => 1 ) [230022] => Array ( [parent_id] => 179836 [level] => 2 ) 

When you get your array from the API, you can process it with a simple foreach loop.

 $arrayFromApi = apiCallToObtainData(); $newArray = []; foreach($arrayFromApi as $value){ $newArray[$value['category_id']] = [ $value['parent_id'], $value=['level'], ]; } 

Now you can easily access your parents, so it’s easy for you to achieve what you need.