Hi. I would always recommend stored procedures, as long as there are a very few rules obeyed: - keep them simple - they should mostly implement just CRUD operations plus application-specific searches, and should not encapsulate any other logic - use just portable SQL (well, as long as this is possible)
My reasoning for using stored procedures and sticking to these rules is the following: - no matter what you do, especially with PHP, you can't achieve the same performance if you generate your SQL on the fly as when you just call a precompiled stored procedure - by keeping stored procedures very simple, and sticking to the convention of packing just CRUD + specialized searches into them, plus using just portable SQL, inasmuch as possible, you can easily switch databases - in most cases, just copying over the stored procedures does the trick - for the same reasons listed for the previous point, the readability of your application is much improved - reading "users_getByLogin(:login)" is IMO easier to comprehend than "SELECT * FROM USERS WHERE loginName = :login", without sacrificing any performance or portability, compared to using inline SQL statements as strings The consequences of not sticking to the above listed two criteria can be very bad: - packing more than reasonable logic into the database makes your application incomprehensible - for instance "company_doMonthEndCalculations()" is likely to include such a huge quantity of logic, that the PHP code calling it is mostly irrelevant, and you must actually comprehend both the details of the SQL in the database (in the stored procedures) and the way PHP is connecting them to understand an application - obviously harder if you have all your logic in just one place - using non-portable SQL may be quite a deterrent from porting to a new database, or may cause a lot more effort than needed, and isn't in fact justified in most cases - whereas if packing only very specific and simple operations into stored procedures allows you to keep the design of the PHP application very object-oriented, packing very much logic into stored procedures may cause your PHP code to be just an adapter to an application written in SQL, instead of being the application itself; SQL being procedural, your application will have all the flexibility, extensibility and maintainability problems that a non-OO design causes br, flj -- Fine counsel is confusing, but example is always clear. (Edgar A. Guest, The Light of Faith) PS: I'm not trying to be a smart-ass, but IMO stored procedures are underrated (not just by PHP programmers), and it's a pity (and it leads to sub-optimal applications, and to the development of cures for the symptoms instead of the cause, at least on other platforms than PHP), and not letting a database do what it does best is simply stupid. That's why I try advertising their use whenever I have an opportunity. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
