PHP - MySQL best practices

[technossomy]

Dear all

I have two questions regarding using PHP with MySQL:

1- I have a number of queries that I currently keep in a file named queries.sql. However, is there a best practice for keeping queries somewhere? In a table, in a file, in the PHP code as an array, in the PHP code as separate variables?

2- What is the best way of implementing nested queries in PHP using MySQL v4.0 (ie no views) in the case where using INNER/LEFT JOINs would make the query horrendously complex? I really prefer to be able to use intermediate results and execute the subsequent queries on that result. With "result" I mean something like $result = mysql_query($sql);

Thanks in advance

Tech

[arran]

For 2., i'd encourage you to use joins. One query using a join will be much faster than executing a simpler query then iterating through the results and executing a further query for each one.

Joins aren't complicated - do some reading and you'll be up-to-speed in no time!

[technossomy]

Thanks arran

I am well versed in joins, my only concern is the length of the resulting query. Thanks nonetheless.

Tech

[SeanW]

Big joins are fine, I've got some horribly complex ones that do 5 inner joins on the same table that execute very quickly due to proper use of indices. Just use EXPLAIN on the query to figure out if it's running well.

You can also use user variables which will eliminate some overhead:

[dev.mysql.com...]

Sean

[technossomy]

Thanks SeanW

The queries I am talking about are a good paragraph in length, which from a maintenance standpoint will probably require a fair bit of editing. Hence my latent need for views.

User comments on the webpage you kindly pointed to, indicate that no aggregates can be used in combination with variables. My queries are teeming with AVGs and MAXs however, so I am not sure whether that would be the way to go.

Thanks again

Tech