How to create a MySQL stored procedure from PHP?

Besides of the privileges, what quite probably might cause your problem is, that actually mysql_query($query) can only work one command per call !

So what you have to do is to split up the commands into sevaral mysql_query($query) -calls.

What i mean is something like this:

$query = "DROP FUNCTION IF EXISTS fnElfProef (accountNr INT)";

mysql_query($query);

$query = "CREATE FUNCTION fnElfProef (accountNr INT)
    RETURNS BOOLEAN
       BEGIN
          DECLARE i, totaal INT DEFAULT 0;
          WHILE i < 9 DO
             SET i = i+1;
             SET totaal = totaal+(i*(accountNr%10));
             SET accountNr = FLOOR(accountNr/10);
          END WHILE;
       RETURN (totaal%11) = 0;
    END";
mysql_query($query);


$query = "SELECT * FROM mytable";
mysql_query($query);

As long as:

• You're using MySQL 5.0+
• Your account has been authorized for creating stored procedure
• You're using the valid CREATE PROCEDURE syntax

Then it should be working. Check these and get back to us.

You can't use the DELIMITER statement from within PHP. Therefore,

1) remove the DELIMITER statements
2)and change all your delimiters to ;

It is annoying because now you have to have a version with DELIMITERS for development and testing in MySQL command line or a MySQL tool like sqlyog or MySQL workbench, and a version without DELIMITERS to use via your PHP program.

If someone knows a better way, I'd like to hear it - (but not by calling a OS shell script - that has too many residual issues.)

And you need SUPER permissions.

Have you got sufficient privileges on the account you are connecting from PHP with? From the manual

As of MySQL 5.0.3, to execute the CREATE PROCEDURE or CREATE FUNCTION statement, it is necessary to have the CREATE ROUTINE privilege

See also Stored Routines and MySQL Privileges in the manual.

Replace mysql_query($query);

To mysql_unbuffered_query($query,$link_connection);