Reputation: 5330

Can I write my own mySQL functions to use in mySQL queries?

Can I write a custom function that I can call when making mySQL queries eg.

My database table has fields 'filetype' and 'filename'. I want to perform a function on filename before returning the result.

$query = "SELECT filetype, MY_FUNCTION(filename) FROM table..";
$result = mysql_query($query);
return $result

SO $result now has the new values created by MY_FUNCTION. The original database data will be unchanged.

OK - so it looks like User Defined Functions are the way to go... how do I write my own user defined function? Can I use PHP to write it? Where do I save the function or do I include it in my globals file?

Thanks!

Upvotes: 6

Answers (5)

tstenner

Reputation: 10281

You can't write a user defined function in PHP (or at least not without writing a wrapper that will call your script), as UDFs are loaded as native code.

Upvotes: 0

Quassnoi

Reputation: 425251

DELIMITER $$

CREATE FUNCTION MY_FUNCTION (FILENAME VARCHAR(255)) RETURNS VARCHAR(255)
DETERMINISTIC
NO SQL
BEGIN
       RETURN SUBSTR(filename, 1, 20);
END
$$


DELIMITER ;

To create the function, you just paste its code (sililar to provided above) in your favorite query tool, like mysql.exe, MySQL Query Browser, phpmysqladmin etc.

You can write UDF's both in C and in SQL. In the latter case, you don't need to have a compiler.

The function I provided as an example is an SQL UDF. It just returns first 20 characters of the filename.

You can use any MySQL functions in an SQL UDF.

Upvotes: 12