proper programming technique for large site with many db queries?

hi,
This is a general question having to do with proper programming technique.  Specifically it has to do with how to handle a large number of DB queries in a large PHP site.

The site I am designing is composed with many different php pages and each page have mostly unique DB queries (inserts, updates, deletes).  Up to this point I have been writing each query in the page that needs the query.  Now that the site has become much bigger, this is becomming inefficient and hard to maintain.  If a DB table field changes name, for example, I have to seach through all the php pages to find the pages that have queries on that table and update them.

I've seen a couple ways to remedy this and was wonder which was the best way.  One way i saw was to put all the queries into an array and put the array in a seperate PHP page.  Then you have all your php pages require the page with the array.  This way there's only one page to look at in order to change any query.  Is there any limit to the PHP array size?  Is it inefficient/slow to import this array into all my PHP pages even though each maybe may only use 2 or 3 of the queries in the array?  Are there any better ways to accomplish this?

Thanks for the advise.
NGDESLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bljakCommented:
Hard to say, one would really need to see and know the wbtree and such. But generally its good administer idea to put all _defines_ into one file and include it on each page, so you can use its variables or arrays or whatever you put into it. About size of arrays in php, there is no real limtiation, but i think i have read somewhere something about size of 50000, like in c/c++.

//bljak
0
spearman123Commented:
depends on size of server, speed, and programming experience.
Better servers can handle more requests. so just connect to the server... send in data,,, send in data,, ckise server connectioon
definitely only want 1 server connection though..
programming experience: dont try something your not sure about.. alot of people are trusting that everything will work without fail.... dont use something you cant fix if a problem arises..
0
Giovanni GCTOCommented:
NGDESL, there are so many ways to handle a situation like that. I experienced a problem similar to yours on a medium size web site, about 60 different dynamic pages, all of them using the db. I solved my problem by following simple SQL rules:

* Whenever you INSERT, always declare the field names you are inserting (I assume that you know the INSERT syntax, otherwise you shouldn't be asking this). Use the default value carefully and you'll solve many maintaining issues when changing the structure of a table.
* When working with the data, always use mysql_fetch_assoc() or mysql_fetch_array(), referencing to field names rather then field column number. This way inserting/adding columns to your tables won't break the existing queries/data handling.

Another way to handle this is an abstraction layer or object, maybe grouped by "target areas". This means that if you need dataX from your database, instead of directly querying your database, create a function like fetch_dataX() and store it in a separated file, or create an object that does the same thing. If you get what I mean, this is the right solution for structuring even major websites.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.