Solved

A MySQL Function conversion

Posted on 2004-08-13
4
321 Views
Last Modified: 2008-02-20
I have this function from a SQL Server:

create function top_parent
( @id int)
returns int
as
begin

     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
     begin
          set @ch = @par
          select @par = parent_id from menu where id = @par
          set @i = @i + 1
     end

     return @ch

end


I would like to format it and use it for MYSQL, but I don't know how.... I can access my database with phpmyadmin...
Thanks!
0
Comment
Question by:chambers777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 17

Expert Comment

by:akshah123
ID: 11798455
what version of mysql are you using?

THe stored procedures are not allowed until mysql 5.0
0
 
LVL 26

Expert Comment

by:Umesh
ID: 11798849
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..

http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html


Hope this helps!
0
 
LVL 1

Accepted Solution

by:
AdamMorton earned 65 total points
ID: 11838138
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 ;)
0
 
LVL 7

Assisted Solution

by:petoskey-001
petoskey-001 earned 60 total points
ID: 11848828
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question