Is it possible to use php user function in a single global SQL query?

This string does not work in php:

$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.

Obviously, multiple queries in a loop do work but what I need is a
single call.

Thanks

re: Is it possible to use php user function in a single global SQL query?

*** markus wrote/escribió (1 Jul 2004 01:38:45 -0700):[color=blue]
> This string does not work in php:
>
> $sql="UPDATE mytable SET myfield=myuserfunction(myfield)"[/color]

You can't put PHP code inside a string, it that's what you mean.
[color=blue]
> 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.
>
> Obviously, multiple queries in a loop do work but what I need is a
> single call.[/color]

So you want to get a value from a database, pass it to a PHP function and
then use the returned value in a database query, all in one single call; is
that correct? Sorry: you can't.



--
--
-- Álvaro G. Vicario - Burgos, Spain
--

re: Is it possible to use php user function in a single global SQL query?

On 1 Jul 2004 01:38:45 -0700, m7484@hotpop.com (markus) wrote:
[color=blue]
>This string does not work in php:
>
>$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.[/color]

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.

However, if you're using MySQL, you'd need to execute multiple UPDATE
statements. That said, depending on what your function does, it may be
possible to write it in SQL.

I don't know about any other RDBMSs.

--
David ( @priz.co.uk ). <http://www.priz.co.uk/ipdb/>
Build the Village: <http://www.priz.co.uk/build/>
Why which is what or whoever.

re: Is it possible to use php user function in a single global SQL query?

$sql= "UPDATE mytable SET myfield=myfield+3";

My function simply evaluates a field against numeric values and and returns a value

function myfunction($value1){

if (value1>20 && value1 <=200) {
new=100;

}else if(value1>200 && value1 <300)){
new=523;

}else{
new=2;
}

return new;
} //end function

re: Is it possible to use php user function in a single global SQL query?

On 1 Jul 2004 12:19:39 -0700, m7484@hotpop.com (markus) wrote:
[color=blue]
>$sql= "UPDATE mytable SET myfield=myfield+3";
>
>My function simply evaluates a field against numeric values and and returns a value
>
>function myfunction($value1){
>
>if (value1>20 && value1 <=200) {
> new=100;
>
>}else if(value1>200 && value1 <300)){
> new=523;
>
>}else{
> new=2;
>}
>
>return new;
>} //end function[/color]

You can do that with a CASE expression in SQL.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

re: Is it possible to use php user function in a single global SQL query?

>>$sql="UPDATE mytable SET myfield=myuserfunction(myfield)"[color=blue][color=green]
>>
>>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.[/color]
>
>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.[/color]

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

re: Is it possible to use php user function in a single global SQL query?

This is just a casual function. The fact that I have to register MySQL
user functions with the server makes them useless in practice for the
average developer.

My best bet is therefore perform multiple queries in a loop which was
my initial code. I wonder if this is too much stress on the database
with 20000 records, though.

re: Is it possible to use php user function in a single global SQL query?

m7484@hotpop.com (markus) wrote in message
news:<b226eb71.0407010038.35683663@posting.google. com>...[color=blue]
>
> What I want is my function to evaluate each field and put the new
> value in the field.[/color]

A user-defined function (UDF) is a way to extend MySQL with
a new function that works like native (built in) MySQL function
such as ABS() and CONCAT().

...

For instructions on writing user-defined functions, see section
23.2 Adding New Functions to MySQL. For the UDF mechanism to work,
functions must be written in C or C++, your operating system must
support dynamic loading and you must have compiled mysqld
dynamically (not statically).

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

In other words, if you want write user-defined functions in SQL,
consider switching to PostgreSQL or a commercial SQL engine.
[color=blue]
> 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.
>
> Obviously, multiple queries in a loop do work but what I need is a
> single call.[/color]

Consider writing a query that will work along the lines of

UPDATE mytable SET myfield=myfield+3

Cheers,
NC

re: Is it possible to use php user function in a single global SQL query?

On 2 Jul 2004 09:33:56 -0700, m7484@hotpop.com (markus) wrote:
[color=blue]
>This is just a casual function. The fact that I have to register MySQL
>user functions with the server makes them useless in practice for the
>average developer.
>
>My best bet is therefore perform multiple queries in a loop which was
>my initial code. I wonder if this is too much stress on the database
>with 20000 records, though.[/color]

If your function performs what you posted previously in this thread, your best
bet is just writing a CASE statement that does the same thing.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space