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?
 
ThGConnect With a Mentor Commented:
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
 
bljakConnect With a Mentor Commented:
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
 
spearman123Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.