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.
4 replies on “How do you code your DB calls?”
I semi-hardcode it:
$tbl_people = ‘my_people’;
$fld_firstname = ‘first_name’;
and for the queries:
mysql_query(“SELECT $fld_firstname FROM $tbl_people WHERE “)
That way I can type and edit the query where it’s used in the code, and still have some more central control over the table and field names.
PS: Could you add some padding on your comments pages’ body? It’s a bit hard to read when the page is in a fullscreen tab or window.
If you use a database that supports views (eg. Postgresql), you can save yourself the pain and trauma caused by underlying database structure changes by creating a view that replicates the old structure using your new tables, thus saving the headache of having to update your code.
As an example, say you had a table called ‘users’ that has the fields you described. Now imagine for whatever obscure reason you broke this into two separate tables — users_brief and users_details. You import your old data into these two tables, then delete your old users table. Now you define the ‘users’ VIEW, which for all intents and purposes you can use as another table in your query. A view basically allows you to define a “virtual table” that is defined as an SQL query — so you could have a query that does a SELECT * FROM users_brief LEFT JOIN user_details USING (user_id), or something to that effect, and present that as the ‘users’ table. Rename fields, whatever is necessary, and your underlying application will never know the difference.
You can then update your application to the new code (if you even want to) at your leisure – there’s no critical rush to get all the queries changed over while users are clammouring at you about not being able to access the system…..
Because the view is defined within the actual DBMS, the query planner should have the smarts to optimise it and ensure the performance difference between the view and the regular table is minimal.
If you want any more information/help drop me an email 🙂
Nothing stops you from updating your code to the new
i just hardcode it all
this may be harder to change when you have a modification in the db structure but in fact i like to see clearly which query i’m sending to the db. i wouldn’t use function because i would always ask myself what syntax i must use to pass my arguments (quotes or not quotes, etc.).
semi-hardcoding like Jan! seems interesting but i would feel a bit lost if a variable called $name contained “surname” just because i renamed a col from name to surname.
and of course views are a very good solution but i’m pretty sure MySQL don’t have them
From their site: “Support for stored procedures was added in version 5.0, and support for triggers will be added in version 5.1.” Given that 4.0 is the latest stable it could be a while…
I cut my teeth on MySQL many years ago, but after running into some of its limitations around the same time I was studying database theory and design made me search around for other options. I decided to give Postgresql a try and have never looked back.
Answering the original question, I do a couple of things:
I have a home grown PHP database wrapper class that handles all my required functionality.. if I ever switch DBMSes, I have a single place to go and change the database specific functions (things in queries is a different story — for that you probably want to look at ADODB or something that attempts to isolate differences in SQL implementations). If I want to debug an application, I can set a flag to output the queries and/or results to follow whats going on. Really handy.
In my queries, I make sure I’m only SELECTing the fields I need. Performance aspects aside, think of security. If you’re pulled every bit of data from the database and the user finds a way to display it, you’re screwed. If you only pull out information you were planning on using, the impact is dramatically reduced. If I ever have some reason to change the underlying database structure, I know I can always emulate the old table using a view.
It all depends on just how many queries you’re going to have. If you’re going to be reusing a lot of the same queries, you might even be worth looking at a 3 tier design:
DBase Business Logic/Rules UI
That way, you can isolate your queries in the database tier, your checks and manipulation in the business logic tier, and your main PHP does the UI. All you need to do is create an instance of your business logic class and use the data provided by it in whatever way you desire in the UI.
On the downside, you add overheads – it takes time to design in a tiered approach rather than an ad-hoc amalgamated design, and in any form of abstraction you add processing overheads. In the end its a tradeoff between maintainability and separation between your functional areas and getting things done in a hurry 🙂
On the issue of logic/UI separation, I’ve found Smarty (http://smarty.php.net/) to be absolutely superb. Having all my HTML and other display tucked away in a separate file and leaving the PHP code to do all the logic and input handling really makes things cleaner to work with.