busch — 2004-06-13T21:43:17-04:00 — #1
I am reading a book that has introduced the method of storing queries in a table in the database. I was wondering:
- Is this common practice?
- Do you recommend this?
- Why/Why not?
- When would you benefit from doing this?
- Why not just store the query in a function that's called by the include() function?
kaklz — 2004-06-14T03:31:43-04:00 — #2
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.
busch — 2004-06-14T10:55:57-04:00 — #3
The example i saw in this book was something like this:
ID int auto-increment
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?
dangermouse — 2004-06-14T12:32:23-04:00 — #4
It would make more sense using stored procedures seems as though there are functions to facilitate them.
kaklz — 2004-06-14T14:08:08-04:00 — #5
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.
modestitexpert — 2004-06-14T15:13:33-04:00 — #6
If you have a LARGE web system with 300-500 files/templates and 30-100 tables :eek2:, 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.
- put MySQL queries into database only in large projetcs/applications
busch — 2004-06-14T19:04:56-04:00 — #7
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.
kaklz — 2004-06-15T02:29:02-04:00 — #8
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)
busch — 2004-06-15T04:10:08-04:00 — #9
Thanks for your info!