Solved

A MySQL Function conversion

Posted on 2004-08-13
4
320 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Creating and Managing Databases with phpMyAdmin in cPanel.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

759 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