• 12
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

Best practice for organizing SQL queries in code?

I have a bunch of beautiful OOP code but the functions have these monstrously huge SQL queries. In 2010, should we really still be writing our queries in-line or is there an abstraction, like MVC, for SQL queries in PHP?

Thank you for your insights.

Edit: I probably should have mentioned this here. But as a professional PHP engineer, you wouldn't believe how often I've come across ORMs done horribly and (even worse!) ORMs programmed correctly that perform absolutely terribly (esp. on many-to-many and many join queries). I'm very negatively biased towards ORMs, with the exception of a few simple ORMs, like Kohana.

I guess what I was looking for was a generic Dynamic SQL design pattern.

I mostly work in Joomla and developed my own OO wrapper for sql generation. But when I work on non-joomla project I always use db2php, this is Netbeans IDE plug-in. It makes it easy to execute even complex queries.

Generation of the models takes couple of clicks and everything is done.

It is terrific opens source project. Check it out.

http://code.google.com/p/db2php/

  • 1
Reply Report

I enjoy using PHPs OOP approaches with a static DB class and the use of stored procedures (SP).

That way all the Mysql code is stored in the server and you can implement the permission checks along with complex queries - using prepared statements inside the SP. With Postgres one would use functions.

This allows a clean database class with generalized code for building SP calls. At best the methods of the broker do accept objects as parameters, so that the PHP developer can concentrate on his code and enjoys all the benefits of the phpdoc in his IDE (autocompletion!).

e.g.

$x = new SEARCH_ITEM();
$x->name = $blab;
$resObj = $this->db->search_item($x);

This also makes it easy to implement things as an API to your project.

Writing SPs is quite a lot if you're not used to it.

Is there good software around? I guess there are good abstraction packages around which I don't know... I don't see things like PDO or Adodb as an abstraction layer in that sense. Creole or Propel is more like what I'm thinking of.. Something where the queries are not part of the code and are being build using abstraction layers like PDO. I didn't try creole and propel.

  • 1
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions