How to create MySQL stored procedures from PHP?

Question

I wrote a database migration PHP script, that loops a list of SQL files and executes their content. It should help to automate the project setupd and updates. Now I'm getting errors like this:

mapCoursesToSport.sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER |
DROP PROCEDURE IF EXISTS mapCoursesToSport|
CREATE PROCEDURE mapCo' at line 1

I was getting the same error when I was passing to the script a file with a view definition, that also was using DELIMITERs. Then I found the workaround just to remove the delimiters from the SQL file. And it worked. But now it's not an option, since stored procedures really need delimiter definitions.

So, how can I define MySQL stored procedures from PHP? (Or maybe morre generally: How should this SDELIMITER be handeled?)

http://stackoverflow.com/a/5025730/2153758 You've commented there, the answer below states that you have to pass every variable one by one to `mysql(i)_query` instead of using commas. — bwoebi, Apr 22 '13 at 18:02
Thank you for the hint, but it doesn't work for me. I do need delimiters in my stored procedures. So I cannot just remove them from the code. If I do that, the procedures are not created. — automatix, Apr 22 '13 at 20:15
You don't need delimiters? Semicolons should pass as is ... and don't end your query by one delimiter — bwoebi, Apr 22 '13 at 20:20
No, I wrote, that I _do_ need delimiters... But anyway, now the workaroud with disabling of `DELIMITER`s is working for me. My mistake was, that I did not only commented the specific delimiter lines (`DELIMITER $$`) out, but also the line with `END$$` and furthermore forgot to replace the delimiter `$$` in ffirst the line (`DROP PROCEDURE IF EXISTS mapCoursesToSport$$`) by a semicolon. The working version looks like this: `DROP PROCEDURE IF EXISTS mapCoursesToSport; ... END`, so pretty similar to the code in the comment you linked... :) Thank you! — automatix, Apr 22 '13 at 21:05
possible duplicate of [How to insert/create stored procedures in mySQL from PHP?](http://stackoverflow.com/questions/4575780/how-to-insert-create-stored-procedures-in-mysql-from-php) — p.s.w.g, Apr 23 '13 at 03:04

Th. Ma. · Answer 1 · 2013-04-22T19:50:53.087

3

You can use native PHP function mysqli::query and mysqli::prepare alternatively to create stored procedure:

http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

Using mysqli::multi_query also works but is a bit more tricky to handle since you might need to count the number of query to execute upfront before executing them one by one (provided the final query delimiter is optional, counting the queries can be tedious)

http://php.net/manual/en/mysqli.multi-query.php

    <?php
    // Using mysqli extension below in object-oriented mode 
    // after having executing queries 
    // with mysqli::multi_query

    do {
        $queryResult = $mysqli->use_result();
        unset($results);
        while ($result = $queryResult->fetch_array(MYSQLI_ASSOC)) {
            $results[] = $result;
        }
        $queryResult->close();

        if ($mysqli->more_results()) {
            $mysqli->next_result();
        }

        $queryCount--;
    } while ($queryCount > 0);

P.S.: The reason I'm not using mysqli:more_results nor mysqli:next_result as loop statement is that some query might be properly executed without returning any result. In such case, we don't want to break the loop before all queries have been executed.

edited Apr 22 '13 at 19:50

answered Apr 22 '13 at 19:03

Th. Ma.

9,432
5
31
46

Thank you for your answer! I'm not really understanding, how it should work... What are you passing to `multi_query`? How are you defining the `queryCount`? Where/how are you using the `$results` array? – automatix Apr 22 '13 at 19:53
1

All queries used to create the procedure should be passed to multi_query. The count of queries could be defined using `implode` on ";" (if defined as the query delimiter) of your queries string stored in a variable passed to mysql_multi_query. I've introduced the results array to cover the general case (where would not like to create a procedure but execute multiple queries returned results). Please consider following the documentation pointed at by the first link as it is of simpler use. – Th. Ma. Apr 22 '13 at 19:57
1

I gave +1 for your answer, because it really detailed and contains an example, but I've not accepted it, since I've already found an answer bwoebi's [comment](http://stackoverflow.com/questions/16153410/how-to-create-mysql-stored-procedures-from-php#comment23081757_16153410).Please see [this comment](http://stackoverflow.com/questions/16153410/how-to-create-mysql-stored-procedures-from-php#comment23087575_16153410). – automatix Apr 23 '13 at 10:37
Thank you! Would you be so kind to edit your question and to make the correct answer (or link pointing at it) under an additional * EDIT * paragraph? – Th. Ma. Apr 23 '13 at 12:00

How to create MySQL stored procedures from PHP?

1 Answers1