Create MySQL User-Defined Function in PHP


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.


DROP FUNCTION CalculateAmount//

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


DECLARE totalCredits FLOAT;

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

RETURN totalAmount;




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


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.