PHP Hierarchical MYSQL查询

Question

I am trying to make a query on the database example below where if a particular ID has a parent greater than zero, echo that parent’s NAME.

For example when the query would reach ID #5, under the PARENT I would like to get “Adidas” and not “2”. Right now I get the parent’s ID, but I’d like to go a step further and retrieve the parent’s NAME.

DATABASE EXAMPLE:

ID  |  PARENT  |  NAME
———————————————————————
1   |     0    |  Nike
2   |     0    |  Adidas
3   |     1    |  Jordan
4   |     1    |  Bryant
5   |     2    |  Rose
6   |     2    |  Howard

Here is the query I am performing as it stands (it's a bit convoluted since I am calling from 2 tables):

$q = "SELECT `categories`.`ID`, `categories`.`Parent`, `categories`.`NAME`, 
             `products`.`ID` as `ID2`, `products`.`NAME` 
      FROM `categories`, `products`";

Thank you all in advance.

Answer 1

To get the desired output from the query you need to use SELF JOIN in this case as main ID and Parent ID both are in same table.

The query will be

SELECT `cat1`.`ID` , `cat1`.`Parent` , `cat2`.`NAME`
FROM `categories` AS cat1
LEFT JOIN `categories` AS cat2 ON `cat2`.`ID` = `cat1`.`PARENT` 

Categories table has been joined to itself by creating aliases cat1 and cat2.

In this case it is important that from which alias you will get name of the Parent. As ID of second alias cat2 is matched with first alias cat1, you will get the parent category name with second alias.

Hope so it will help you.