A MySQL Function conversion

I have this function from a SQL Server:

create function top_parent
( @id int)
returns int

     declare @i integer
     set @i = 0
     declare @ch integer
     declare @par integer
     set @ch = @id
     select @par = parent_id from menu where id = @ch

     while @par <> 0 AND @i < 1000
          set @ch = @par
          select @par = parent_id from menu where id = @par
          set @i = @i + 1

     return @ch


I would like to format it and use it for MYSQL, but I don't know how.... I can access my database with phpmyadmin...
Who is Participating?
AdamMortonConnect With a Mentor Commented:
It is true that stored procedures are not supported until MySQL 5, but if you just want to select the top_parent you can do it with multiple joins.  

I don't know how deep your menu structures actually are, but you probably wouldn't want to do this up to depth 1000 like your stored procedure supports.

Here is a query that will get the top parent for a depth of 9 or less:

select case
when m1.id is null then null
when m2.id is null then m1.id
when m3.id is null then m2.id
when m4.id is null then m3.id
when m5.id is null then m4.id
when m6.id is null then m5.id
when m7.id is null then m6.id
when m8.id is null then m7.id
else m9.id end as top_parent
from menu m1
left join menu m2 on (m1.parent_id=m2.id)
left join menu m3 on (m2.parent_id=m3.id)
left join menu m4 on (m3.parent_id=m4.id)
left join menu m5 on (m4.parent_id=m5.id)
left join menu m6 on (m5.parent_id=m6.id)
left join menu m7 on (m6.parent_id=m7.id)
left join menu m8 on (m7.parent_id=m8.id)
left join menu m9 on (m8.parent_id=m9.id)
where m1.id=1500;

returns the top parent for menu row with id=1500

If your tables are indexed correctly, this shouldn't be as bad to run as it looks... depending on how bad you think it looks ;)
what version of mysql are you using?

THe stored procedures are not allowed until mysql 5.0
UmeshMySQL Principle Technical Support EngineerCommented:
Hi  chambers777,

akshah is abs right.. Only mysql 5.0 have this kind of privilege..

Please check this mysql manual link.. It has given howto stuff..


Hope this helps!
petoskey-001Connect With a Mentor Commented:
Nice try AdamMorton, but what happens when  you want to find something nested 10 deep?  Huh, huh?  lol.  

Seriously though nice solution.  To extend it any depth, just re-run the command using the parent_id you received last time until the the parent_id returned is the same as the id in the where clause.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.