Solved

proper programming technique for large site with many db queries?

Posted on 2003-11-20
3
276 Views
Last Modified: 2006-11-17
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.
0
Comment
Question by:NGDESL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 4

Assisted Solution

by:bljak
bljak earned 100 total points
ID: 9791262
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
 
LVL 1

Assisted Solution

by:spearman123
spearman123 earned 50 total points
ID: 9791538
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
 
LVL 14

Accepted Solution

by:
ThG earned 100 total points
ID: 9801263
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question