SQL Query Builder

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

SQL Query Builder

Post by Ambush Commander »

Not sure if this belongs in Databases, but it's mostly PHP code related.

I've been doing some reading of Domas Mituzas (the inhouse MySQL dev. for MediaWiki) and came across this interesting blog post: http://dammit.lt/2006/03/05/db-abstract ... mediawiki/ essentially describing the differences between Zend and MediaWiki's db abstraction layers.

However, what struck me was the idea that SQL was structured rather than just text, so you ought to be able to generate it with a query builder, like...

Code: Select all

$select->where('field = ?',$value);
rather than manually typing the SQL.

It seems to be a quite interesting approach (I've never heard of it before, but it makes sense). Do you use it? And also, do you think it would be a good idea to augment the database wrapper in a toolkit to support query building (is it a feature or a core item)?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: SQL Query Builder

Post by Chris Corbyn »

Ambush Commander wrote:However, what struck me was the idea that SQL was structured rather than just text, so you ought to be able to generate it with a query builder, like...

Code: Select all

$select->where('field = ?',$value);
rather than manually typing the SQL.
This is not new to me. I've seen it other code before and I've used it in form classes myself :)

SQL is structured so yes, it's easy to do this nicely if you building a query. SQL is easier to parse than other languages too although analysing a query is not as simple is building the damn thing (I spent some time writing an SQL beautifier).
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

But would you consider it's benefits great enough to mandate by a toolkit? That is, the Toolkit requires a DB wrapper class that gives this functionality.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I am of two minds about building SQL that way. If certainly seems interesting at first glance, but once you get into it -- as the article whined about ;)) you get into programmer preference about how to do things and also into some real gymnasitcs to do advanced things (and even simple ones).

The root of the problem is that SQL is very expressive and very mature so you can do almost anything coding in straight. So you end up with a ton of code to replace something that is already very good. However, there are things in SQL -- mainly building lists -- that are very tedious and building them in code makes it much easier to make error free changes with ease.

You might want to look into Hibernate and OQL t o see some of the cools stuff that has been done in this area -- it is certainly not new.
(#10850)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Hmm... if that's the case, then I really can't use SQL building. Hrmm....

Makes for interesting research. I'll look into those two apps you mentioned. PHP? (Doesn't seem like it. Hibernate's Java)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Ambush Commander wrote:Hmm... if that's the case, then I really can't use SQL building. Hrmm....
Actually I think they are very useful. The problem is creating a one-size-fits-all that does everything that SQL does. It is very difficult and the code ends up huge. But if you limit yourself to a specific solution then I think it can work very well. I use these types of classes as the basis for or within Model classes all the time because it makes writing many types of code easy and handles all the escaping nonsense that is so tedious. But I always also have plenty of hand coded SQL because other stuff is dead simple done that way.
Ambush Commander wrote:Makes for interesting research. I'll look into those two apps you mentioned. PHP? (Doesn't seem like it. Hibernate's Java)
Yes, it's pretty much the standard Java persistence library.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Pear::DB allows the structured creation of query strings just like this.

Though from what I gather, there are other reasons why Pear::DB is not so widely used. Never used it myself.

I've used structured SQL in the past with forms for custom search criteria.. form has input for fields, operator and value.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Actually PEAR :: DB is no longer supported I believe -- it's MDB2 now. It has a "OO Query Tool" or some such thing here:

http://pear.php.net/manual/en/package.d ... rytool.php

But I don't think that is exactly an elegant interface.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

I just remembered glancing over someone's code that had "$db->whereAdd('col =', $val);" or some such :)
Ward
Forum Commoner
Posts: 74
Joined: Thu Jul 13, 2006 10:01 am

Post by Ward »

It's quite handy for displaying result tables. For example, if you have a table of DB records, and the user is allowed to do things like re-sort, filter by certain fields, etc, then a query builder is very useful.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

I imagine so. In my inexperience, I've never actually had to allow such control over querying, but that definitely would be the case once I finish incrementally refactoring my primary application.

Specialized builder it is.
Post Reply