SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    norway
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The use of MySQL stored procedures

    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..?

  2. #2
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    norway
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes thats a good point..
    Also the with use of ORM's I guess make them a bit hard to implement

  4. #4
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mastodont View Post
    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/


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.oracle.com/customers/index.html

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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict webaddictz's Avatar
    Join Date
    Feb 2006
    Location
    Netherlands
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Yes, I blog, too.

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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)

  10. #10
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Germany
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    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.
    Quote Originally Posted by Cups View Post
    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.

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by FrlB View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •