SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing SQL queries in database

    I am reading a book that has introduced the method of storing queries in a table in the database. I was wondering:
    1. Is this common practice?
    2. Do you recommend this?
    3. Why/Why not?
    4. When would you benefit from doing this?
    5. Why not just store the query in a function that's called by the include() function?


    Thanks
    busch

  2. #2
    SitePoint Enthusiast kaklz's Avatar
    Join Date
    Mar 2004
    Location
    Latvia, Riga
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, that depends on what you mean by storing queries in a database. If you mean using stored procedures - then it's great and widely used on serious and large systems. However, if you mean it having a table 'queries', where you have some ID's and queries in plain text, then I actually don't think it's a good idea.

  3. #3
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The example i saw in this book was something like this:
    storedQuery Table

    ID int auto-increment
    NAME tinytext
    QUERY varchar 255

    Example: ID=1, NAME=getName, QUERY="SELECT username FROM members"

    This is just an example of what was in the book.
    Does that answer your question, kaklz?
    busch

  4. #4
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would make more sense using stored procedures seems as though there are functions to facilitate them.

  5. #5
    SitePoint Enthusiast kaklz's Avatar
    Join Date
    Mar 2004
    Location
    Latvia, Riga
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Busch
    The example i saw in this book was something like this:
    storedQuery Table

    ID int auto-increment
    NAME tinytext
    QUERY varchar 255

    Example: ID=1, NAME=getName, QUERY="SELECT username FROM members"

    This is just an example of what was in the book.
    Does that answer your question, kaklz?
    busch
    Well, it does answer my question, and I must say I don't think this is a good aproach. The thing is - you have to make additional SQL query each time you need any of those queries. So let's say some php file uses 5 different queries. That makes a total of 10 queries. I must say on bigger sites that might turn to unneeded load on database server.

  6. #6
    SitePoint Enthusiast ModestITExpert's Avatar
    Join Date
    Jun 2004
    Location
    Planet Earth
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kaklz
    Well, it does answer my question, and I must say I don't think this is a good aproach. The thing is - you have to make additional SQL query each time you need any of those queries. So let's say some php file uses 5 different queries. That makes a total of 10 queries. I must say on bigger sites that might turn to unneeded load on database server.
    Hmmm.....

    If you have a LARGE web system with 300-500 files/templates and 30-100 tables , sometimes you need to execute a chains of MySQL queries in order to mantain data integrity. You need to execute these chains from several times from the several scripts. If you put each chain in a separate file and then will include each time when needed the system performance will decrease.

    But if you put each chain in the database and will retrieve each time you need it, the whole system will work faster (especially with MySQL caching).

    The problem of large applications - great amount of files to include and memeory usage. Moreover file open/read functions quite slow.

    Conclusion:
    - put MySQL queries into database only in large projetcs/applications

  7. #7
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for everyone's input. One question. Kaklz, what did you mean when you said this:
    If you mean using stored procedures - then it's great and widely used on serious and large systems.
    busch

  8. #8
    SitePoint Enthusiast kaklz's Avatar
    Join Date
    Mar 2004
    Location
    Latvia, Riga
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Busch
    Thanks for everyone's input. One question. Kaklz, what did you mean when you said this:

    busch
    Stored procedures are a feature of SQL that allows you to group several SQL statements (just like PHP functions) and get back a result. When projects are developed in big programmer groups, this is a common aproach in order to separate database programming from logic and presentation programming.

    Unfortunately stored procedures are not yet available in MySQL (they say it will be available starting version 5.0)

  9. #9
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your info!
    busch


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
  •