Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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...
2 Solutions
what version of mysql are you using?

THe stored procedures are not allowed until mysql 5.0
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!
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 ;)
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.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now