>>$sql="UPDATE mytable SET myfield=myuserfunction(myfield)"
What I want is my function to evaluate each field and put the new
value in the field.
For example, this works:
$sql= "UPDATE mytable SET myfield=myfield+3";
Stores myfield+3 in each myfield, but with a function it does not
work.
My office SQL guru tells me that MS SQL Server has such a thing as
User Defined Functions, which *may* be evaluated per row. He has never
had a need to used UDFs.
MySQL also permits User Defined Functions. But you need to compile
them into a plugin and register them with MySQL, something that
seems annoying enough that you shouldn't do it lightly. If you've
got some generally useful functions that you could use a lot, they
might be worth doing. You also have to deal with the issue that
the functions won't be on every MySQL server, and chances are hosting
companies won't let you add your own plugins to MySQL unless you
lease the entire server.
One good candidate for a UDF that I've seen discussed here is one
that calculates a distance between two pairs of lat/long coordinates,
so that you can, for example, select the nearest 5 stores (with
known coordinates) to the center of the Zip code (yes, this is
USA-centric) the user supplied (tables of zip code to lat/long are
apparently available somewhere. The US Census TIGER data could
give you lat/long coordinates of a street block but you'd need a
complicated matching algorithm to match user input against the
addresses in that data. I think you could also use that data to
compute a center-of-gravity for a Zip code (5 or 9 digits, but it
likely doesn't include Zip codes that exist only inside a Post
Office for the PO Boxes.) You'd definitely want to pre-crunch this
data rather than doing it on each query.).
Your best bet is to translate the PHP function into a SQL function,
if possible.
Gordon L. Burditt