The use of MySQL stored procedures

[danman]

Hi
Coming from a MS/.NET world I am curios as to why the use of stored procedures seems not that debated or used in php applications.
If not used in my regular work with ASP.NET applications then it is almost percieved as you are doing a "rookie" error.
One thing is that you have to recompile your entire project with one tiny change in sql code, which is not the case in php obviously.
But still this would not explain why SP's it is often considered a "must" in .net and is rarely used at all in php..?

[Czaries]

I think the reason stored procedures are not often used in PHP stems from the fact that they are relatively new to MySQL, which is what most PHP application use for a database. MySQL isn't the enterprise-level database that MS-SQL is.

That coupled with the fact that most MySQL libraries don't have really good built-in ways to call stored procedures or pass values to them, and you don't get much use. I would even bet that over half of the PHP developers out there don't even know what stored procedures are, let alone have any clue on how to use them. It's just not something that is very common in the PHP community.

[danman]

Yes thats a good point..
Also the with use of ORM's I guess make them a bit hard to implement

[Mastodont]

Hello danman,
I believe that one on the greatest PHP buzzword is now "database abstraction". Each blog, forum or any other application "must" be able to communicate with any database otherwise you are doomed and nobody cares about your work
But stored procedures are for the most part not so easy portable. ORMs are simpler to learn and the logic is preserved in application. Many programmers believe that database is only a store, not decision-making entity.
And Czaries is right about MySQL, it is rather a toy.

You are not the first asking this question. Please try to read e.g. this thread:
http://stackoverflow.com/questions/8...hp-mysql-world

[r937]

And Czaries is right about MySQL, it is rather a toy.

oh, puhleeeeease, this is the 21st century -- try to keep up

see http://mysql.com/customers/

[Mastodont]

http://www.oracle.com/customers/index.html

( I must admit that I am not uncommitted - they give me money ... )

[r937]

do they also pay you to denigrate other database systems? especially now that mysql is also yours?

really, i thought we had grown up beyond the "mine's bigger than yours" name-calling

[webaddictz]

do they also pay you to denigrate other database systems? especially now that mysql is also yours? really, i thought we had grown up beyond the "mine's bigger than yours" name-calling

I can still pee further. I don't want to get into the whole debate on which database is superior to another, but I do think Czaries is spot on: most php developers started with PHP and MySQL and most articles on mysql (or at least the ones I've seen) don't go further than using MySQL as a stupid datastore where you can store a value and retrieve it later.

It's a darn shame to see that most php developers don't see the huge benifits a database has to offer. I myself use PostgreSQL most of the time because I like it and it follows SQL a bit stricter than MySQL does, and I put a lot of the applications' logic into the database, rather than the application itself. A lot of code I see being written could be handled by a rdbms, and easier and faster too, but people just don't know this.

[Cups]

I only read this link because I thought I might find some discussion on how I would and when I should use SPs when using PHP

By nature don't Stored Procedures cause a tendency to hardwire of some domain logic into your database?

Maybe this is a mad thought, brought on by my imminent plan to learn Python, but how about if you wanted your rdbms to stay the same, but you decided to switch languages for your app?

So in a way turning "database abstraction" to "language abstraction".

In that case having more domain logic in your database would make things easier, no?

(on the occasions I use an MVC framework I have fleetingly pondered if I could retain my Model classes but switch the rest of my framework, so asking implausible questions of myself comes naturally to me)

[FrlB]

By nature don't Stored Procedures cause a tendency to hardwire of some domain logic into your database?

Yes, and that's what makes them so useful.

Maybe this is a mad thought, brought on by my imminent plan to learn Python, but how about if you wanted your rdbms to stay the same, but you decided to switch languages for your app?

Or one could have different applications using the same data (base) - in that case SPs reduce redundancy.

My problem with SPs (and why I don't like to use them so much at the moment) is they tend to fragment the application, i.e. it's logic, and you will have additional places to look for in case of unwanted behaviour. Another problem: MySQL's stored procedures are hard to code and debug (for me). Are there good tools for debugging MySQL's SQL?

I use them sometimes to enforce application behaviour additionally, e.g. I want some entries changed in case some other data is being deleted - while my app takes care of this I additionally use a trigger and SP to be sure - normal script termination is not guaranteed.

[Cups]

Or one could have different applications using the same data (base)

I find that a stimulating idea.

I maintain a CMS for a niche market and slowly realised that managing the data from the Admin (CRUDdy) side of things shared very little with how I put my public pages together (mainly read-only into templates).

I have used trad MVC frameworks, but at my last rewrite I decided to develop a simple PresentationModel (PM) styled framework for the Admin area of a CMS I maintain. (put simply, Models<->Views using Ajax).

More and more of the CMS "content" is coming from outside, eg. Flickr for photos for example.

Once the simple CRUD scaffolding is in place, the main work involved in the Admin area using the PM is the creation and management of Ajax calls, and sychronising View States.

I have been using a rigid PHP/Ajax library but it would be nice to think I could swop and use something else for certain tasks.

Certainly this thread puts a question in my mind as to what I should learn first, Triggers and SPs (and maybe PostGres) or another language, such as Python.