using SQL include files

[chrisg101]

I'm after some advice regarding the use of including ".sql" files.

I'm thinking of seperating my sql queries into seperate files and doing php incudes when the query is needed. That way if the query changes then I'm only updating the file instead of opening the php file. Is there any disadvantage in doing this? ie. security concerns, etc. Seems like a nice way to organise the code.

I could rename the files in '.php' so that would get parsed as php and no one could just grab the contents, but this is just me guessing at this stage.

Any suggestions would be appreciated.

Chris.

[[deleted]]

Including files is never good for performance.

Besides which; just how more managable does extracting the queries make your code?

If you have to edit a query then yes you can just edit the sql file, but editing a query usually also means that the output changes and that therefore you also have to edit the PHP that handles the output.

A function lubrary or OOP may be a better solution. Then you can create self-contained functions to both execute the query and process the results.
Yes you'll have to edit PHP again, but everything that deals with the SQL is gathered inside one function which makes is very easy to manage.

[csn]

Put all the complicated SQL in the database as functions, views, ...(you ARE using PostgreSQL aren't you 😉 ).

[chrisg101]

No, I'm actually using mysql. Is there a way you can save queries in mysql? I heard that using stored queries actually slows up the database quite a bit.

If you can, and you think it is worth it - how do I create a stored query/procedure etc.

Appreciate your help.
Regards, Chris.

[[deleted]]

MYSQL is rumoured to start supporting stored procedures in 4.1+. But MySQL has been rumoured to do a lot the past years...

Stored procedures can greatly increase the speed of your database because th queries are stored in a 'prepared' state. The database already creates a query-plan when you create the procedure, so it can simply begin executing the stored procedure when you call it, instead of having to check the query and build a plan every time you do the query.

A general rule in databases is that if you can make the database do it, it's probably faster than doing it yourself.

[csn]

I think there's a PERL module or something that allows you some sort of stored procs with MySQL.

[csn]

Here it is - still 0.1

http://software.tangent.org/article.pl?sid=01/08/23/0817244&mode=thread&threshold=

http://slashdot.org/articles/01/08/24/1253228.shtml