How to create a MySQL stored procedure from PHP?

Question

My attempts to query MySQL from PHP with a create statement of a store procedure (SP) have all failed. Is this not possible ?

If indeed possible, please give an example.

Solution 1

The MySQL manual has a clear overview about how to create stored procedures (13.1.15. CREATE PROCEDURE and CREATE FUNCTION Syntax).

The next question is: does the account you use to access the MySQL database the propper rights to actually create a procedure?

Details about this question can be found here: 19.2.2. Stored Routines and MySQL Privileges

Solution 2

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);

Solution 3

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.

Solution 4

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.

View more solutions