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.

Answer 1

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