What’s The Difference Between Annual & Monthly Billing

I’m quoting part of an answer which I received for another question of mine:

In the PHP/MySQL world I would say stored procedures are no-go
I would like to know: Is that so? Why? Why not?

[edit]I mean this as a general question without a specific need in mind[/edit]

2 ANSWERS

February 16, 2017 at 9:37 am admin

When using stored procedures with MySQL, you will often need to use the mysqli interface in PHP and not the regular mysql interface.

The reason for this is due to the fact that the stored procedures often will return more than 1 result set. If it does, the mysql API can not handle it and will you get errors.

The mysqli interface has functions to handling these multiple result sets, functions such as mysqli_more_results and mysqli_next_result.

Keep in mind that if you return any result set at all from the stored procedure, then you need to use these APIs, as the stored procedure generates 1 result set for the actual execution, and then 1 additional one for each result set intentionally returned from the stored procedure.

February 16, 2017 at 9:41 am admin

I develop and maintain a large PHP/MySQL application. Here is my experience with stored procedures.

Over time our application has grown very complex. And with all the logic on the php side, some operations would query the database with over 100 short queries.

MySQL is so quick that the performance was still acceptable, but not great.

We made the decision in our latest version of the software to move some of the logic to stored procedures for complex operations.

We did achieve a significant performance gain due to the fact that we did not have to send data back and forth between PHP and MySQL.

I do agree with the other posters here that PL/SQL is not a modern language and is difficult to debug.

Bottom Line: Stored Procedures are a great tool for certain situations. But I would not recommend using them unless you have a good reason. For simple applications, stored procedures are not worth the hassle.

Viewing 2 replies - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.