Create MySQL User-Defined Function in PHP

Introduction

We are describing User-Defined Functions. User-Defined Functions is code that extends the functionality of MySQL Server. A User-Defined Function can work the same as built-in functions like concat(), find_in_set() in MySQL and concat(), and find_in_set() in string functions. User-Defined functions are compiled as object files that can be added by a CREATE FUNCTION statement and can be removed dynamically from the server with a DROP FUNCTION statement.

This function is very helpfull when you want to extend the functionality in your MySQL Server; the features are:

User-defined functions can have zero or more input parameters
Can have aggregate functions that handle groups of rows
You can indicate that a function returns NULL or that an error occurred

The syntax is similar to Stored Procedures in MySQL. Here I am creating a simple User-Defined function to calculate the available credits in a user account.

Example

DROP FUNCTION CalculateAmount//

CREATE FUNCTION CalculateAmount(userid INT) RETURNS float(10,2)

BEGIN

DECLARE totalCredits FLOAT;

SELECT SUM(amount) INTO totalAmount FROM credit_user WHERE id =userid;

RETURN totalAmount;

END

Note:

You are not use parameter var in function same types column field of database if you use with database query otherwise its conflict.

Syntax

DECLARE variablename datatype

Use that variable in a function, like here I have used it in a query to get the total amount.

Syntax Of User-Defined Function

SELECT function_name(parameters);

You can create the functions as simple or as complex as you need.

Some Important Features

The function returns the values of string, integer, or real.
You will define a single row in this function, or aggregate functions can operate on groups of rows.
To provide MySQL arguments and pass them to a function.