I was complaining to my husband that I couldn’t get the MySQL GUI to create procedures. It threw wobblies about the syntax. So my first stored procedure after installing MySQL 5 was typed line by line into the prompt – not ideal especially if you are prone to typos!

So he said, unsympathetically, you could always write a script and run it from the file on the server. Which is true, I can. Better still I wrote myself a little PHP page that allows me to run the scripts from the comfort of my own PC (or someone else’s for that matter). Just make sure it is in a password protected directory on the server!

<?php
    include("connectioni.php");
    reset ($HTTP_POST_VARS);
    while (list ($key, $val) = each ($HTTP_POST_VARS))
    {
        $$key = stripslashes($val);
    }
    if (isset ($sql)) {
        if (!mysqli_multi_query($link,$sql)) {
            echo $sql . " " . mysqli_error($link);
        }
        else {
            do {
                if ($result = mysqli_store_result($link)) {
                    echo "<table>";
                    $finfo = mysqli_fetch_fields($result);
                    echo "<tr>";
                    foreach ($finfo as $val) {
                        echo "<td>". $val->name ."</td>";
                    }
                    echo "</tr>";
                    $numrows=mysqli_num_rows($result);
                    $numcols=mysqli_num_fields($result);
                    while ($row = mysqli_fetch_row($result)) {
                        echo "<tr>";
                        for ($counter = 0; $counter < $numcols; $counter++) {
                            echo "<td>". $row[$counter] ."</td>";
                        }
                        echo "</tr>";
                    }
                    echo "</table>";
                    mysqli_free_result($result);
                }
                else {
                    echo mysqli_info($link);
                }
            } while (mysqli_next_result($link));
        }
        mysqli_close($link);
    }
    else {
        echo "Enter sql query";
    }
?>
<form action="<?=$HTTP_SERVER_VARS['PHP_SELF']?>" method="POST">
<textarea name="sql" cols="60" rows="30"><?=$sql?></textarea>
<input type="submit"/>
</form>

7 stored procedures later and counting.

This is very useful afterwards:

insert into mysql.procs_priv (host,db,user,routine_name,routine_type,grantor,proc_priv)
values('host','database','user','proc_name','PROCEDURE','dba@%','Execute');
flush privileges;
select * from mysql.procs_priv;