How to create a MySQL stored procedure from PHP?

Question

My attempts to query MySQL from PHP using the CREATE statement of a stored procedure (SP) have failed. Is this impossible?

If it is possible, please give an example.

Answer 1

Besides privileges, what is most likely causing your problems is that mysql_query($query) can actually only execute one command per call!

So all you have to do is split the command into several mysql_query($query) -calls.

What I mean is 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);

Answer 2

The MySQL manual has a clear overview of 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 have the appropriate permissions for the actual creation process?

Details about this issue can be found here: 19.2.2. Stored routines and MySQL permissions