Posted by Tim Mickelson on 10/14/88 11:37

Hello

I have this problem, making a script file to use the "mysql"
application, I can create the file my_sp.sql, and launch as:
mysql -u pippo -ppluto test < my_sp.sql
This works fine, the problem is doing this from php, with the
mysql_query, how should I change the delimiter, this is not
a standard SQL query, so I get an error, I have really tried all
different possiblities to fix this, please help me someone.

Tim

my_sp.sql:

DELIMITER $$
CREATE PROCEDURE `test`.`users`(name VARCHAR(30))
BEGIN
DECLARE comment VARCHAR(100);
SET comment = CONCAT(name,' is added');
INSERT INTO users (id,name,comment) VALUES (NULL,name,comment);
END
$$
DELIMITER ;

This works fine, my problem is, how can I create this from PHP, using
the mysql libraries?

<?
$con = mysql_connect('localhost','pippo','pluto');
mysql_select_db('test');

// HOW SHOULD THIS QUERY BE??
$query = "DELIMITER $$ ";
$query.= "CREATE PROCEDURE test.users(name VARCHAR(30)) ";
$query.= "BEGIN";
$query.= "DECLARE comment VARCHAR(100);";
$query.= "SET comment = CONCAT(name,' is added');";
$query.= "INSERT INTO users (id,name,comment) VALUES
(NULL,name,comment);";
$query.= "END";
$query.= "$$";
$query.= "DELIMITER ;";

mysql_query($query);
mysql_close($con);
?>

[Back to original message]