MySQL stored procedures / their use on the web from PHP
Archive - Originally posted on "The Horse's Mouth" - 2009-10-10 01:04:44 - Graham EllisAre there times when your PHP will pull a large result set back from MySQL, or have to make a large number of queries, just to further process the data into a relatively small dynamic section of a report? If so, why not have the MySQL do more of the work and return a much shorter, more processes results to the PHP? You can do this with an appropriate stored procedure.
On yesterday's MySQL course I wrote a series of stored procedure examples ... the "abc" of stored procedures, if you like - there are complete books on the topic! Let's see the results - follow the links if you want to see the code.
This is a very simple stored procedure ... a 'macro' that lets a simple query - call otc() embedded in your mysqli query method - run a more complex piece of SQL within the MySQL daemon.See MySQL stored procedure definition and PHP source code
Rather than return just a single value, this example returns a complete table of results - in our PHP, we've loped through those results and displayed them in a browser.See MySQL stored procedure definition and PHP source code
You'll want to pass a parameter into your stored procedures ... this example shows how ...See MySQL stored procedure definition and PHP source code
Our final example in this set shows variables, conditionals, loops, a cursor and more ... we've passed back just a single value - kicked back to us by a rather different use of select to what you're used to.See MySQL stored procedure definition and PHP source code
The data that we seeded the tables with is here, and the same data as a mysqldump file is here