Wednesday May 15th, 2013

There is no right or wrong way[1], but no matter what there is no *pretty* way to do SQL inside of a PHP application. I have been having a personal debate with myself all week about how to make SQL statements nicer in an application without going to a huge DBAL package like Doctrine.

[1] – There are actually plenty of wrong ways to write SQL. For example, “SELECT * FROM table WHERE id={$_GET[‘id’]};”

Separation of Church and SQL.

My second idea (after formatting SQL inline of PHP) was to completely remove the SQL from the app all together and load it separately (with application caching) from separate query files. These files would literally just contain the SQL that would be dumped straight into database object.


  • No SQL in PHP. Fairly nice to read.
  • People have told me they’ve done this before.


  • Now I have to open a 2nd file to update this application. The first being the programming which tells me which SQL file is used, second being the actual SQL file.
  • My query parameters are disconnected from the code so even if I remembered which file had the query I needed, I might not remember what all the arguments in the query are named without opening the query file.
  • It is yet more disk IO first time the query is used.
  • You can’t procedurally generate that shit.

We Can Build Anything.

My third idea then was for simpler queries to provide a query building object which would generate the SQL as soon as we tried to read it out.


  • All the methods can take multiple arguments [e.g. From(‘table1′,’table2’)] and because the SQL is not generated until we try and read it, we can actually do stuff later and then From(‘table3’) and the end query will be FROM table1,table2,table3 allowing us to procedurally generate queries.
  • It looks nicer than trying to format multi line SQL strings in the code.


  • It is basically no easier or harder to read than the actual SQL.
  • Saves no keystrokes in the end.
  • It is more work (memory use, cpu use, (object creation, method lookups, jumps, things that don’t really matter 95% of the time)) to do such a simple thing.

In the end I really have not decided what to do about it. I am interested in opinions and comments.

Comment by Jake Smith on Thursday May 16th at 10:24am
#1 What you are doing seems no different/better than using stored procedures on the DB server. Only plus side is it is less of a pain to update your queries by having them in your repo. * I am NOT advocating using stored procedures *

#2 I think this is the best idea, but there are already so many libraries for Query Builders. I think you're re-inventing the wheel if you build it yourself. If you want to do it more so for research and enjoyment, then go for it.
    Comment by bob on Thursday May 16th at 10:51am
    @Jake Smith:

    The only reason I would continue my query builder (I actually have a working class from that code example above) would be to keep dependencies down. ATM like Aura I have the whole "0 dependency" thing going on. Not so much the ability to snap the system apart though.
Comment by Jeff Madsen on Thursday May 16th at 10:42am
Agree with Jake completely - there are so many good libraries for this, don't understand what else you would really want. Look at http://auraphp.com/, Laravel's Eloquent, or dozens of other projects of different shapes & sizes
    Comment by bob on Thursday May 16th at 10:49am
    @Jeff Madsen:

    Aura is on my todo list to checkout today for sure, Daniel had told me about it last night too.
Comment by xaxo on Thursday May 16th at 10:51am
I like the background picture. Is that from some game or your houseplace? Use framework with performative build-in RAR layer, e.g. Yii or 3rd party ORM libs like propel/doctrine/adodb.
    Comment by bob on Thursday May 16th at 10:53am

    It is a photo I took off my balcony here in downtown Dallas. ;)
Comment by Rasmus Schultz on Thursday May 16th at 10:59am
In my previous job, I was happy for years, simply encapsulating ugly query construction in factory-classes - this approach lets you manually optimize the SQL queries to the extreme, and while these factory-classes are definitely ugly on the inside, they can be made superbly elegant on the outside, and this leads to more domain-specific language in controllers and service classes, as compared to the typical (lazy) use of object/relational-mappers and accompanying query-builders.

What I mean by that is, once you're using an object/relational-mapper that lets you do things like User::query()->with('profile')->select() and encapsulates pre-defined relations etc., it quickly becomes natural and automatic practice to simply construct queries using an Active Record API, directly in your controllers and services, instead of encapsulating this stuff in a real data-access layer.

Because your generic AR/ODM library does the encapsulation for you, right? Wrong. It encapsulates general facilities for query construction and transactions etc. - but it does not generally expose any of this to your controllers/services using APIs with domain-specific language. You still see this code littered with things like join() and where() method-calls that take SQL snippets as arguments.

It's a matter of personal preference of course. But I'm a minimalist, and having used both approaches for many years, I have to say, I prefer the control, flexibility and performance of simple factory classes, and I was perfectly happy encapsulating the ugly parts in those.

That said, of course you can build data-access APIs that use domain-specific language, with an AR/ODM library inside those - there just isn't much of a tendency to do so, and people who subscribe to AR as a "lifestyle" tend to think a data-access layer on top of AR is superfluous.

I tend to think that domain language is more important than almost anything else - join statements and where clauses most likely have no direct relevance to your business domain, and I prefer to see it neatly packed away in domain-specific APIs.

It's all a matter of preference though :-)
    Comment by bob on Thursday May 16th at 11:38am
    @Rasmus Schultz:

    Aye preference is what it always seems to boil down to. I shy away from some of the larger abstraction packages because I feel like some of the database interactions may be more complex than they might be able to optimize for. Nature of the beast and all that. I also like you prefer having tight control over things behind-the-scenes.
Comment by mailo on Friday May 17th at 3:41am
Since in MVC apps the model is responsible for example, for the database communication, you can store your SQL in there (cons: model won't be database agnostic).

Then you just do something similar to your first idea: Model::getVehicleListByOwner().

Even if you don't use any framework, storing SQL in this kind of method should improve code maintainability.

Tip: you can use query builder inside that method, so as a result your model would be database agnostic.

And yeah, _do not_ reinvent the wheel :)
スーパーコピーブランド格安販売店はこちらへ!品々の激安価格に持ったスーパーコピーブランド 代引きの新作はお客様に提供されます。安心、迅速、確実、お客様の手元にお届け致します。★弊社は9年の豊富な経験と実績を持っております。★一流の素材を選択し、精巧な作り方でまるで本物のようなな製品を造ります。★品質を重視、納期も厳守、お客様第一主義を貫きは当社の方針です。★驚きの低価格で商品をお客様に提供致します!★早速に購入へようこそ! http://www.okakaku.com/brand-9-copy-0.html
2015年の新素材-新作!高品質 腕時計高品質の追求 超N品を良心価格で提供詳しくは以下のようなブランドがあります。HERMES(バッグ、財布、時計) CHANEL(バッグ、財布、時計)LOUIS VUITTON(バッグ、小物、財布、時計) BVLGARI(財布、時計)Christian Dior(バッグ、財布) COACH(バッグ、財布)GUCCI(バッグ、財布) ROLEX(時計)OMEGA(時計) IWC(時計)FRANCK MULLER(時計) HUBLOT(時計)クロエ CHLOE バッグなどです。ご不明点が ございましたらお気軽にお問い合わせください http://www.ooowatch.com/tokei/rolex/index.html
最も美しいスーパーコピー,超格安ブランド時計コピー激安通販専門店!ブランド品に憧れて、予算オーバーし、諦めなければならないことがありましたか。スーパーコピー品が一番いいチョイスだ思います。少しのお金でも、世界中一番人気を持つブランド品、貴族しか買えない贅沢品がゲットできます。素敵な外観と実用性に優れたブランド コピー バッグ,,スーパーコピー財布,スーパーコピー 代引き,エルメス コピー,ルイヴィトン 財布、ルイヴィトン バッグ、ベルトなどの逸品はお客様に提供します。誰でもブランド品を手に入れられるのは弊社の経営理念です。当店の最大目標はお客様が安心してお買い物が出来き、お客様の頼りになるお店を目指す事ことです。お洒落な貴方はきっとこのチャンスを見逃しなくよ! http://www.brandiwc.com/brand-25-copy-0.html
エルバーキンコピーエルメスバーキン30コピーエルメス ボリード47,エルメス バッグ 名前,エルメス ネクタイ ピンク エルメス クラッチバッグ,エルメス バッグ コピー,エルメス バーキン コピー エルメス 財布 ダミエ オークション,エルメス ヨーロッパ,エルメス エールライン エルメス クラッチ激安通販、高い品質、送料無料。バーキン25コピー、バーキン30コピー、バーキン35コピー、バーキン40コピーなど世界中有名なブランドレプリカを格安で通販しております。N級品スーパーコピーブランドは ブランドスーパーコピー超N品エルメスバッグ,エルメス バーキン25 , バーキン30.バーキン35.バーキン40. エルメス(HERMES) ケリー http://www.gginza.com/wallet/louisvuitton/index_5.html
プラダ スーパーコピー,プラダ 財布 コピー,プラダ 新作 財布ブランド財布コピー,ブランド スーパーコピー 財布,プラダ スーパーコピー 財布,シャネル財布コピー,グッチ スーパーコピー 財布,エルメス スーパーコピー 財布,ルイヴィトン長財布コピー,スーパーコピー財布,エルメスコピー財布,各種のブランドはかばんをコピーします偽物ブランド,激安偽物,ブランド財布コピー,エルメス財布コピー,ブランドのコピーブランド財布,ルイ?ヴィトンブランド財布コピー,偽ブランドグッチ財布,D&G,コピー財布偽物,偽物時計コピー,時計,ボッテガベルト,,靴,その他のバッグコピー,ブランド財布激安,ブランド激安販売,偽ブランド激安市場,通販送料無料專門店 ルイヴィトンコピー 児玉店,当店ルイヴィトン コピー 財布、ルイヴィトン コピー バッグ 全MAX80%OFF!期間限定SALE。最短即日発送。送料無料ルイヴィトン コピー,ルイヴィトン コピー 財布,ルイヴィトン コピー バッグ http://www.ooobrand.com/bags/chanel/index.html
高品質2015シャネル スーパーコピー激安專門店弊社は海外大好評を博くシャネル コピー激安老舗です,2015高品質シャネル バッグ コピー,シャネル 靴 コピー,シャネル 財布 コピー品の品質はよくて、激安の大特価でご提供します。 http://www.bestevance.com/louisvuitton/index_6.html
ブランドコピー時計大人気を海外激安通販専門店ロレックス、ブルガリ、フランク ミュラー、シャネル、カルティエ、オメガ、IWC、ルイヴィトン、オーデマ ピゲ、ブライトリング、グッチ、エルメス、パネライ、パテックフィリップ偽物(コピー商品)のブランドの腕時計の販売、通販。 http://www.msnbrand.com/brand-copy-IP-2.html
16 Responses to “On SQL in PHP”
Write a Comment