在php中创建用户定义的mysql函数

Question

I have this problem whereby, i have created a user defined function within php. But it has some problems.Let me elaborate. suppose i've created this function

<?php

include 'db_connect.php';
$sql="DROP FUNCTION IF EXISTS testf";
$result=mysql_query($sql) or die (mysql_error());
$sql="CREATE  FUNCTION testf() RETURNS text
DETERMINISTIC
READS SQL DATA

BEGIN
DECLARE Output text;

set Output='zzz';

RETURN output ;
END";

$result=mysql_query($sql) or die (mysql_error());

$sql="SELECT testf()";
$result=mysql_query($sql) or die (mysql_error());
$row=mysql_fetch_array($result);
echo nl2br($row[0]);

?>

The above is working fine.

but the following has an error:

<?php
include 'db_connect.php';
$sql="DROP FUNCTION IF EXISTS testf";
$result=mysql_query($sql) or die (mysql_error());
$sql="CREATE  FUNCTION testf() RETURNS text
DETERMINISTIC
READS SQL DATA

BEGIN
DECLARE Output text;
DECLARE name text;
set Output='zzz';
set name='SELECT t_name for tbl_names where id=1';  
RETURN output ;
END";

$result=mysql_query($sql) or die (mysql_error());

$sql="SELECT testf()";
$result=mysql_query($sql) or die (mysql_error());
$row=mysql_fetch_array($result);
echo nl2br($row[0]);
?>

i'm getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT t_Name FROM tbl_name WHERE id=1; RETURN output ; END' at line 9 Can anyone suggest a solution?, i'm stuck. i know i could execute the query 'SELECT t_name for tbl_names where id=1' and pass the result as parameter. It would work yes, but i want to avoid it.

Answer 1

mysql_query doesn't allow but one query at a time. You're not going to be able to define any but the most trivial stored procedures with it, given that limitation.

You generally shouldn't be using the mysql extension anyway -- it's ancient, and was made for versions of MySQL that didn't even have such things as stored procedures. Why it still exists is beyond me.

Anyway, you could use the mysqli extension; there's even an example in the docs. It can be used quite similarly to the old procedural way, if you're more comfortable with that.