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.

---

you can write your queries (select, insert, update, delete,...) in stored procedures and only call those stored procedures from within your php code instead of writing your queries in your php files.

a good resource for learning stored procedures in mysql is this book:

mysql stored procedure programming

---

I don't know about PHP, but you could use an ORM. Wikipedia has a nice list of ORM software.

---

You can look into using Doctrine ORM. Not sure if that is what you were asking for or not.

---

A good way is to use an ORM such as Doctrine.

---

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/

---

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.