Martian.titan Martian.titan - 4 years ago 76
MySQL Question

PHP MySql get data from a table and left join to the same table

I have this table names MyLevels.

id | level | ParentLevelId
---------------------
1 | basic | 1
2 | silver| 1
3 | gold | 2
4 | stone | 3
5 | wood | 2


on this table every level has a parent level. For example the
"gold"
level parent id is 2. It's mean the parent level is
"silver"
.

So I need a query to get the below in a html table:
Parent level comes from
ParentLevelId
.

level | parent level | distance From Basic
-------------------------------------------
basic | basic | 0
silver | basic | 1
gold | silver | 2


The distance from
basic
reference to the number that each level is far from
basic
level.

For example the
wood
level id is 5 and
5 - 1 = 4
. which means the it's 4 level far from
basic
level.

This is the only code I have:

$conn->prepare('SELECT id, level, ParentLevelId FROM MyLevels');


Any help appreciated.

Thanks

Answer Source

Please try this.

select c.level as level, p.level as parent_level, c.id-p.id as distance_from_parent
from MyLevels AS p
JOIN MyLevels AS c on p.id = c.ParentLevelId
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download