Recursive selects are your friend here;
with t as
(
select * from testtbl as t1
union all
select t2.* from testtbl as t2
join t on t.cid=t2.category_parent_id
)
select cname, count(1) as depth from t
group by cname
I haven't got access to a MySql instance here so I've only tested that on SQLServer.
Hope this helps,
Fredrik
Actually, ignore of all that. MySQL doens't support it I think :(