[thelist] MySQL stored function looping problem

Jeremy Weiss eccentric.one at gmail.com
Mon Dec 8 18:31:27 CST 2008


Okay, a while back I posted a question involving a very non-normalized
db table that I'm dealing with.
(http://lists.evolt.org/archive/Week-of-Mon-20081117/196289.html)
I've made a bit of progress but I'm in new territory now. I've tried to
fully explain the problem, so this email is a bit long.

The advice I received last time was to break this troublesome field out
into its own table. So, with the help of others, I've managed to write
a couple of stored functions to assist with this task.

The field in question is pipe delimited so I have one stored function
that counts the number of | in the field and adds 1 to give me the
count. You can view this function at http://tinyurl.com/6dn7y5

Another function takes the property mls # and a position number as
input, and parses the pipe delimited field and returns the value in
that position. You can view this function at http://tinyurl.com/5cezeo

The final function takes the property mls number as input, uses it to
call the first function to get a count, then uses the mls # and the
count to loop through, calling the second function once for every value
in this field, then takes each value and inserts it and the mls number
into the new table. 

So far, so good. But, at this point we've only processed 1 of the 
15K+ listings. And I didn't really want to make that many calls from
a PHP script if I could avoid it. So I've gone back and modified the
final function to use a cursor to pull a list of all the mls numbers 
and then loop through them, doing all the above each time. 

The problem is, when I call the final function from Query Browser, 
it only inserts 103 records into the new table (from 9 
properties). So I threw a simple counter in the mix and it returns 
over 15K on the cursor loop. So it seems that it is looping like
it's suppose to, but something else isn't working. Trouble is, I'm
not sure how to debug it. You can view this function at 
http://tinyurl.com/6xpfub

Google wasn't much help on this one (or I'm not using
the right search phrase). So any and all guidance,
advice, suggestions, etc. are welcome.

-jeremy




More information about the thelist mailing list