How do I use a MySQL user-defined function from within PHP?

Spent several hours searching for an answer without success. I've written a user-defined function in MySQL which is passed an identifier which it uses to retrieve various pieces of data, concatenate it into one string and return it. I want to call this function from my PHP page and output the result.

Unsuccessful attempts 开发者_开发技巧include:

1. $result = mysql_query("select functionName($id)"); 

2. $sql = "select functionName($id)";
   $result = mysql_query($sql, $link);

3. functionName($id) 

Any ideas?

---

1 and 2 are close, but $result is not going to contain the result of the function call. Rather, it is going to contain the result cookie from the query. You can use that cookie to get the actual data, with mysql_fetch_row(). The function call just returns a value for the select statement, just the same as "SELECT 42" or "SELECT a FROM MyTable". So to get the result you would use the same mechanism as with any other SQL query that returns results; that is, use the cookie and call mysql_fetch_row(). So your final code will look like this:

$result = mysql_query("select functionName($id)");
$row = mysql_fetch_row($result, $link);
$returnValue = $row[0];

Note that you don't want to be interpolating variables directly into an SQL string (that can be a vector for attacks). I assume, however, that this code is just for example purposes.

---

I had the same question and found this very useful write up from devx, particulary the part at the bottom about calling MySQL functions:

http://www.devx.com/webdev/Article/42887/0/page/2

With regards to mysqli, my code is now as follows:

$result = mysqli_query($sqlconnection,"SELECT functionName($id)");
$row = mysqli_fetch_row($result);
return $row[0];

works perfectly.