Solved

A MySQL Function conversion

Posted on 2004-08-13
4
317 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
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:ushastry
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Limit on number of values in mysql in clause 3 53
mysql sql statement - SQL INSERT INTO SELECT 11 60
MYSQL simple update statement 3 46
Have issues with Query MySQL 9 55
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now