Well, I’ve got a few options that I see, so I open it up to see what others do. For this discussion, we shall be referring to a mySQL table with the following columns:
FirstName | LastName | Address | Address2 | City | State | Zip | Email | Comments | Sex | Food | Age | HotOrNot
Now we access this table several dozen times for various reasons. Each time, we need different things. Sometimes, only the first name of one individual, sometimes several things, sometimes all data for an individual, or a list of individuals.
Now… we want to be fast and efficient. But want clean and manageable code.
Say for examples sake, we need to get all fields for all users for one particular page of the site. For another, we only need a certain 3. For another, only a certain 2.
What is the best method to do this? Create a separate DB query for each option? Create 1 function that dynamically creates the query based on what fields you want? Just select all data that matches the query, and use what you want?
The reason this comes up is I was working on something similar the other night, and discussing this recently. Here were the basic points:
- Many DB calls is tough to maintain. Say you want to change DB’s, or update the code for whatever reason. That’s lots of changes.
- You can just select all data for the query. But then you have lots of data being selected. Not good for a giant database.
- Generating a DB call can be messy, complex, and of course slow, depending on how complex they can get.
I”ve seen it done all ways. Do people to generate the SQL ‘on demand’? Hard code all of it? Or just select every column in the query, and use what you want?
I’m curious what people choose, and most importantly, why? What makes that option outweigh the others? Alternative methods welcome of course 😉
I’m mainly referring to PHP and mySQL, but if you want to bring in Perl, JSP, or other DB’s like Oracle, feel free.
Yes, this is an uber-geeky, perfectionist, strongly geek opinionated conversation. I’m sure some will feel there is one, and only one way to do this. And all others are extremely wrong. But debate is a good thing. Just keep it under control, I hate moderating.
