Code critique: minimalist SQL query class

Hello all,

I have decided that I’m not a big fan of OOSQL classes that look like this:

$this->select()->from('pages')->innerJoin('images')->on('images.page_id','page.id','=')->where('pages.published = :published',array(':published'=>1))->orderBy('sort_id','asc');

Inevitably there end up being queries you can’t express using the built in methods, and every programmer seems to have a different perspective on what the method signatures should allow so it seems to gravitate towards a func_get_args() solution and loses any semblance of structure, bit by bit.

I decided I like writing straight SQL, for the most part. For simpler queries, it generally takes less code to write and for more complex ones you’re not limited by the structure of the class.

I wrote a very basic class that essentially creates a string object-- you can append, prepend, and replace anything you want. I also like the PDO-style :placeholders, so I added a __call() method that replaces :method with the first argument. For example:

$this->sql('SELECT * FROM pages INNER JOIN images ON images.page_id = page.id WHERE pages.published = :published ORDER BY sort_id ASC')->published(1);

I really like this. It’s simple and powerful, scales with large queries, and is currently only 56 lines. I can even take whole sections of a query and name them to give meaning to that section:

$sql = $this->sql('SELECT * FROM pages WHERE (:is_published) OR (:is_draft)');
$sql->is_published('pages.published = 1');
$sql->is_draft("status = 'draft'");
// SELECT * FROM pages WHERE (pages.published = 1) OR (status = 'draft')

I can also replace a :placeholder with an array, and it will automatically join the array using a comma by default, though another delimiter can be specified:

$sql = $this->sql('SELECT * FROM pages WHERE id IN (:selected_pages)');
$sql->replace(':selected_pages',array(1,2,3,4,5,6));
// SELECT * FROM pages WHERE id IN (1,2,3,4,5,6)

I would like to improve this class. Here are my questions:

  1. Are there any downsides to using :placeholders for string replacement? Any potential side effects I’m not aware of?
  2. How can I elegantly allow bound values for prepared statements? Maybe $sql->bind(‘:published’,1)? Or maybe it’s simpler to handle bound values outside of this class?
  3. Is a class like this better left as a string to be executed, ie $stmt = $db->prepare($sql); or is there value in abstracting the database and giving the class methods like execute() and fetchAll()? The latter means less code to write for the developer, but also increases the complexity of the class.
  4. Is there any merit to giving the class more structure (or maybe wrapping it in a class that has structure?) I have been envisioning a class that would allow appending columns, tables, joins, or where criteria to an existing string, like so:
$sql = $this->sql('SELECT id, title FROM pages');
$sql->select('body, sort_id');
// SELECT id, title, body, sort_id FROM pages

Something like that seems to require knowledge of the string’s contents, whereas the current class doesn’t care what the string contains (other than placeholders). I don’t know, maybe it’s better that way.

Comments and criticism are welcome.

Honestly and Truthfully? I think that classes which wrap existing good Database wrappers (MySQLi and PDO) are overkill. I only really see value there for programmers who don’t understand SQL and can’t be bothered to learn.

Data Access Objects make a ton of sense in compiled languages (if you do them right) because they allow changing the DB schema without recompiling your application. In PHP, code changes are trivial to update, so that’s not a concern. I really think that DAOs in interpreted languages are one of those things that are carried over from heavier languages (Java is a great example in PHP’s case) which don’t necessarily need to be there, and don’t provide a whole lot of value when they’re present.

I do find occasions where sending commands through an object - not as SQL - are useful.

For example:

$Database->Select()->From('SomeTable')->Where(array('id' => '24'))->limit(1)

Now, of course, if your database engine is MySQL that’d be translated into:

SELECT * FROM SomeTable WHERE id = 24 LIMIT 1

And this whole object is a waste of time. However, what if you want your application to be able to run off an XML file, or even a flat file? The objects using the same interface would be able to use your non-sql query and you can then utilise any kind of database system you can write an accessing object for.

I believe this abstraction should appear as a result of solving an existing problem,
not a possible problem. Otherwise one may end up with a pretty leaky abstraction.

I agree, completely. I suppose we’ve kind of gotten off topic, though, since the original question was only about SQL abstraction.

I find query builder classes useful in two situations :

  • when one wishes to build a rdbms-agnostic application,
  • when one wishes to build a query in an arbitrary order (first the from part, then the select part, then some where conditions, then a join, then…) when for example several objects contribute some bits and pieces to a query.

This class doesn’t seem to achieve either so I probably wouldn’t have a use for it. But if it’s aesthetically more pleasant to you to write SQL this way, why not ? Or is there more to it than that ?

Not really.
At least not if you’re using different database types.
They have similar - but not equal - SQL syntax - which e.g. PDO doesn’t take care of (AFAIK).

I agree, I think to many people take patterns that are really designed to solve specific problems and make them generic, which never works out well. Professionally I’ve just been using pure SQL for about a half of a year now and that seems to work best for my needs. I’ve found all attempts at automating the creation the SQL to be restrictive or just an overkill unless your solving a very specific problem that warrants and provides a large advantage by using abstraction or automation. The generic mapper or active record is a cool, but hardly practical in comparison to just writing the plain ol’ SQL.

I agree, it does nothing for the first situation. However, you can still build up queries in parts:

$query = $this->sql('SELECT * FROM pages');
if ($published) {
	$query->append('WHERE published = 1');
}

// OR

$query = $this->sql('SELECT * FROM pages WHERE :where LIMIT 10');
if ($published)	{
	$query->where('published = 1');
} else {
	$query->where('published = 0');
}

That was one of the primary goals for writing this class, after all, if it couldn’t build queries dynamically then there’d be little advantage over raw SQL. The whole idea is to take the weak points of raw SQL (string concatenation, dynamic queries) and make them as painless as possible without trying to rewrite an OOP version of SQL.

I’ve been doing something similar, though when I end up with duplicate queries or queries that are similar except for the WHERE criteria, I might encapsulate that in a gateway. For the most part though, SQL suits me well unless the query is dynamic.

I encapsulate all data access methods in a transient object. The one problem though as you point out is duplicating similar queries. You can’t really avoid it in some situations but in my opinion that is a small price to pay for the flexibility.

One thing have been thinking of is writing a code generator for SQL.

So given a SQL dialect (MySQL/Sqlite/Postgres etc), schema, and API (PDO/mysql/mysqli/sqlite etc), and an interface it could generate a concrete implementation.

The interface would have to rely on a decent naming convention, for the generator to determine what action should take place.


CREATE TABLE Page
(
  pageId INTEGER NOT NULL PRIMARY KEY,
...

);


interface MyData
{
   function deletePage($pageId);
}

Generating something like…


class ... implements MyData
{
   function deletePage($pageId)
   {
         $stmt = $this->pdo->prepare('DELETE FROM Page WHERE pageId = ?');
         $stmt->bindValue(1, $pageId, PDO::PARAM_INT);
         return $stmt->execute();
   }
}

That’s what libs like Zend_Db_* and others do, isn’t it?

They do it at run time, building SQL dynamically every time.

I agree, I’ve never been a fan of query building methods, they just take longer to write, longer to debug, longer to run and more difficult to optimize. I agree that if someone has the intention of supporting several different databases then some query building solution makes sense, but most of the time when I make a project for a client - why would I need to support more than one database or switch to xml or plain file data storage or whatever? The server has to support php and mysql, period, no problem with that at all.

  1. Are there any downsides to using : placeholders for string replacement? Any potential side effects I’m not aware of?

None that I can think of but if you are using preg_replace for substitution be aware of properly escaping the replacement string otherwise some strings (especially binary ones) will become corrupted. Initially I ran across this problem - I wrote about it here.

$sql = $this->sql('SELECT id, title FROM pages');
$sql->select('body, sort_id');
// SELECT id, title, body, sort_id FROM pages

Something like that seems to require knowledge of the string’s contents, whereas the current class doesn’t care what the string contains (other than placeholders).

To me that is overkill and I don’t see much benefit in it. I think parsing placeholders is enough for the sql class to do.

Some time ago I also came to the conclusion that I like plain sql better than building criteria in some convoluted ways. But I have gone a slightly different route because I also became a big fan of having table rows as objects like in Active Record pattern. That is what is used in Propel and the great thing is it allows you to extend each table class with your own methods which can parse and manipulate database results to your hearts content. Wanting to combine these 2 approaches I finally made a rather simple implementation of a Propel-like model that extends a simple database access class, which I can use as well. And for certain db manipulations I do things like this:


// simple row retrieval
$page = PagePeer::getByPK(10);

// simple data update
$page->title = 'Observer';
$page->save();

// delete
$page->delete();

// creating new row
$page = new Page;
$page->title = 'Observer';
$page->save();
$page_id = $page->page_id;

// getting a virtual table field
$title_length = $page->getTitleLength();

// using sql to get data as objects
$page = PagePeer::doSelectOne("SELECT *
    FROM page WHERE cat_id=5 ORDER BY date LIMIT 1");

// shorthand for the above and using placeholders
$db->prepare_value(":cat_id", 5);
$page = PagePeer::doSelectOne("WHERE cat_id=:cat_id
    ORDER BY date LIMIT 1");

// selecting many rows as objects
// $pages becomes an iterator object that I can traverse with foreach
$pages = PagePeer::doSelect("WHERE cat_id=:cat_id
    ORDER BY date");

// if I want just plain data from db I use my db object directly
// - this is a wrapper function with some convenience methods
$page = $db->fetch_one_row("SELECT title, date
    FROM page WHERE page_id=5");  // returns associative array

// fetch many rows in an array
$pages = $db->fetch_all_rows("SELECT title, date
    FROM page WHERE cat_id=1");

// or when I need only 1 value
$title = $db->fetch_one_value("SELECT title
    FROM page WHERE page_id=5");

// plain sql
$db->query("UPDATE page SET date=NOW()
    WHERE cat_id=5");

This is slightly different from your approach but has a similar objective of being able to use plain sql easily. I also enjoy the simplicity of working with objects for simple queries with no sql while also being able to use sql whenever convenient. I have used this approach in a few projects and I like it a lot. I ditched the whole idea of building sql code by criteria objects, etc. in favour of plain sql. I think accessing database should be as simple as possible for the developer and I want to stay away from too much complexity or abstraction if possible. It is enough for me to learn sql and I don’t want to have to learn and remember all the query building methods in a complex db pattern.

BTW, my db class uses and extends mysqli. I found mysqli a bit more convenient to use and simpler than PDO. At the end of the day I don’t think it makes a lot of difference.

I’ve spent the later of a year working on my own ActiveRecord and in the end I can simply say its just not worth it. its so much more efficient to solve the problems at hand using the simplest methods possible rather than creating some convoluted generic automation engine that will never handle all use cases. Than you end doing either hacking or finding workarounds for things that shouldn’t necessary need work-arounds to begin with. For simple “generic” operations automation seems fine until you have to deal worth real-world scenarios where logic is rarely so straight forward or atomic. I mean, if your using your own SQL for the later of the program than the use of an automated engine just becomes pointless and increases the complexity with no real advantage.

Well, I’ve spent the last year on my own active record and I find it to have been a very worthwhile endeavour. I’ve done a few projects with it and it’s been pleasure coding in this way, now the underlying mechanism is done I feel I can do things faster.

its so much more efficient to solve the problems at hand using the simplest methods possible rather than creating some convoluted generic automation engine that will never handle all use cases.

First, the automation engine is not so convoluted, at least not in my case. Second, no engine will ever handle all use cases, the point is it will handle many use cases and make many tasks easier.

Than you end doing either hacking or finding workarounds for things that shouldn’t necessary need work-arounds to begin with.

I don’t remember doing any hacking or trying to workaround the system I use. If it happens to be not good enough for a specific task I simply don’t use it for this task. What’s the problem? This doesn’t prevent me from using it for many other tasks.

For simple “generic” operations automation seems fine until you have to deal worth real-world scenarios where logic is rarely so straight forward or atomic.

Usually, there are many “generic” operations which can be automated. For real-world scenarios I simply extend the generic operations or use a different mechanism suitable for that purpose. Again, I don’t see any problem here. And yes, I have used this system in real-world scenarios.

I mean, if your using your own SQL for the later of the program than the use of an automated engine just becomes pointless and increases the complexity with no real advantage.

Why SQL would increase complexity? I use my own SQL to not have to use convoluted objects and methods for SQL creation, which is the point of this thread. I don’t use active record to abstract from SQL. I don’t use active record to create SQL using a bunch of dedicated objects and methods. I use active record in order to have db objects available to work with and extend, which I find very convenient. My own SQL doesn’t get in the way here at all. It just gives me more freedom to control what is going on and relieves me from having to build SQL in complicated steps.

Everyone has a preferrable way of solving things, what doesn’t work for you seems to work very well for me. But we are getting a bit off topic here.

Edit: To make things clear, I didn’t make a full-featured active record. My implementation handles mostly basic CRUD and no fancy stuff like relations, etc. So I’m not sure if it can be called active record. It mostly provides model objects to work with and that is all I need. I know making a full-featured active record is a very time-consuming task but I didn’t do it so I don’t feel like I spent a whole lot of time designing something that doesn’t bring many benefits. If I went that route I think I would feel the same, that it wasn’t worth it. But now I only use my lightweight active-record system which didn’t take much effort but helps a lot with many tasks and structuring my code.

@Lemon Juice: I’d love to see how your system works - could you post some example usage?

Unless Im working on a framework, I prefer straight MySQL syntax with PDO. In frameworks, my goal is always to be as flexible and plug-inable as possible - which means that it should be able to run from an XML file or MySQL, or flat file. All it takes is to write a class adhering to the correct interfaces and I’ve just expanded my market.

Of course, all of this would be great if I ever used my frameworks for anything :lol:

Sure, but what are you interested in - using the system or how it is implemented? Structure of classes, db object? As to the usage I’ve given most of examples in a post above, it’s quite simple. If you know Propel then this is a stripped down, lightweight and faster version of Propel. And of course a one-button mechanism for generating all model classes. There is also an automated mechanism for generating administration pages for forms and table editing (controllers, templates and validators) but that is not that much about the topic of sql or database access.

… Of course, all of this would be great if I ever used my frameworks for anything :lol:

Are you saying that grand ideas and nice theories remain only theories while in real world you get your hands dirty in quick and ugly php code just in order to get money for a finished project fast? :slight_smile: If everyone does the same then what are we doing here? :smiley:

I must confess I sometimes choose dirty ways deliberately, I just don’t feel comfortable with too many patterns… And so while I try to stick to OOP, I use things like Reg::$db, which pretends to be a registry (or singleton, or object factory) while it’s just a class acting like a global variable. Fast to type, fast to execute, non-problematic, maybe ugly, sorry… but works and I don’t need more :smiley:

The implementation, really. It IS your code, after all, I couldn’t possibly expect to see the system itself :cool:

[ot]

Almost :wink:

In fact, what I was saying is that all I ever do with PHP is write frameworks. I challenge myself regularly to come up with a framework that makes it easy to write all kinds of plugins and modify designs. Given no distractions, plenty of Coffee and Lucozade and food now and then, I come up with such a framework in about a day. I then spend some time adding plugins etc… then I lose motivation. It goes nowhere. I used to get work all the time, but in the last 12 months I’ve received exactly nothing in terms of freelance work, so I’m just left messing around with theory.

I think if you want to finish a project fast, you should still adhere to certain principles etc. I’d say the overall time difference between using a register and dependency injection is about 5 minutes, but the DI brings structural benefits, so it’s worth it. In fact, I find a few diagrams on paper speeds up my project work about 25% - because I have direct instructions; I don’t spend time thinking ‘hmm, what if I do this THAT way?’.

So certain not-so-good practices, such as the singleton registry, can be avoided with minimal effect on performance.

All you need is motivation and a plan. I suppose the same goes with anything in life :lol:[/ot]

Ok, so I’ll try to explain briefly how it works:

  1. Front controller (index.php with some helper classes) gets the name of the action from url and fires specific method in a proper controller. List of actions assigned to controllers is kept in a db table. Generally, I have one controller per db table, that’s what my automated system for admin generates, of course I can add more controllers when I need.

  2. An example of a controller with basic methods for adding, editing, listing, deleting, archiving and moving records in the payment_type table. I know they probably are too big as most of this logic should go to the model but they are generated automatically and I’m too lazy to restructure all of that. I have skipped the content of the last methods since that’s a lot of code. $portlet is an object for passing data to the templates. As you can see all basic CRUD is done without SQL:


<?php
// FlyControleer - I named my system 'Fly', hence the name :)
class PaymentTypeController extends FlyController {

	/* form for adding record */
	public function addPaymentTypeForm($request, $portlet = null) {
		
		if (!$portlet) {
			$portlet = new FlyPortlet;
			$paymentType = new PaymentType;
		} else {
			$paymentType = $portlet->data['formData'];
		}
		
		$portlet->data['formAction'] = "index.php?a=addPaymentType";
		$portlet->data['actionType'] = 'add';
		$portlet->data['formData'] = $paymentType;

		$portlet->setTemplate("admin/payment_type-form.tpl");
		
		return $portlet;
	}
	
	/* adding record action */
	public function addPaymentType($request, $portlet = null) {
		
		// this gets all data from posted from form into a new paymentType object.
		// true means that all fields should go through trim() function
		$paymentType = new PaymentType(@$request['post']['payment_type'], true);
		
		$validator = new Validator;
		$validator->validatePaymentType($paymentType);
		$validationErrors = $validator->getValidationErrors();
		
		if ($validationErrors) {
			// errors in form
			$portlet = new FlyPortlet;
			$portlet->data['formData'] = $paymentType;
			$portlet->data['validationErrors'] = $validationErrors;
			$this->setGlobalMessage("There are errors in the form!", "error");
			
			return $this->addPaymentTypeForm($request, $portlet);
		}
		
		// validation ok, save form data
		$paymentType->save();

		$this->setGlobalMessage("Data have been added");

		return new FlyAdminRedirect('listPaymentTypes');
	}

	
	/* form for editing record */
	public function editPaymentTypeForm($request, $portlet = null) {
		if (!$portlet) {
			$portlet = new FlyPortlet;

			if (!$paymentType = PaymentTypePeer::getByPK(@$request['get']['payment_type_id'])) {
				throw new Exception("Requested record does not exist in database");
			}
			$portlet->data['formData'] = $paymentType;
		} else {
			$paymentType = $portlet->data['formData'];
		}
		
		$portlet->data['formAction'] = "index.php?a=editPaymentType";
		$portlet->data['actionType'] = 'edit';

		$portlet->setTemplate("admin/payment_type-form.tpl");
		
		return $portlet;
	}
	
	
	/* editing record action */
	public function editPaymentType($request, $portlet = null) {
		$db = Reg::$db;
				
		$paymentType = PaymentTypePeer::getByPK(@$request['post']['PK']);
		if (!$paymentType) {
			throw new Exception("Form error");
		}
		$paymentType->loadData($request['post']['payment_type'], true, array('for_poland', 'abroad'));
		
		$validator = new Validator;
		$validator->validatePaymentType($paymentType);
		$validationErrors = $validator->getValidationErrors();
		
		if ($validationErrors) {
			// errors in form
			$portlet = new FlyPortlet;
			$portlet->data['formData'] = $paymentType;
			$portlet->data['validationErrors'] = $validationErrors;
			$this->setGlobalMessage("There are errors in the form!", "error");
			
			return $this->editPaymentTypeForm($request, $portlet);
		}
		
		// validation ok, save form data
		$paymentType->save();
		
		if ($paymentType->hasChanged()) {
			$this->setGlobalMessage("Data have been saved");
		} else {
			$this->setGlobalMessage("Data have not been saved, probably you have made no changes", "alert");
		}
		return new FlyAdminRedirect('listPaymentTypes');
	}


	/* record listing */
	public function listPaymentTypes($request, $portlet = null) {
	}
	
	/* delete record */
	public function deletePaymentType($request, $portlet = null) {
	}
	
	/* move to archive */
	public function movePaymentTypeToArchive($request, $portlet = null) {
	}
	
	/* restore from archive */
	public function restorePaymentTypeFromArchive($request, $portlet = null) {
	}

	/* move selected items */
	function movePaymentType($request) {
	}
}
?>
  1. Model objects. Similar structure to Propel - there are base classes generated automatically with basic methods for accessing row objects and static base peer classes with methods not concerned with any specific data object. Both of these are extended by classes where I add my own stuff.

Example of BasePaymentType class:


<?php
/* Do not edit this class! Use extended class for your stuff. */
class BasePaymentType extends DbRecord {

	protected $_primaryKeyName = 'payment_type_id';
	protected $_tableName = 'payment_type';
	protected $_className = 'PaymentType';
	protected $_peerName = 'PaymentTypePeer';
	protected $_PKQuoteStyle = 's';
	protected $_hasChanged = false;
	public $_originalData;

	public $payment_type_id = '';
	public $name_pl = '';
	public $name_en = '';
	public $name2_pl = '';
	public $name2_en = '';
	public $desc_pl = null;
	public $desc_en = null;
	public $for_poland = '';
	public $abroad = '';
	public $commission = '0.00';
	public $constant_commission = '0.00';
	public $payment_fee_usd = '0.00';
	public $payment_fee_info_pl = null;
	public $payment_fee_info_en = null;
	public $payment_fee_name_pl = null;
	public $payment_fee_name_en = null;
	public $item_status = '1';
	public $item_pos = '';

	public function __construct($data = null, $doTrim = false) {
		parent::__construct($data, $doTrim);
	}

	public function getPrimaryKey() {
		return $this->payment_type_id;
	}

	public function getPrimaryKeyName() {
		return 'payment_type_id';
	}

	public function getDbTableName() {
		return 'payment_type';
	}


	public function copy() {
		$copy = clone $this;
		$pkName = $this->_primaryKeyName;
		$copy->$pkName = null;
		$copy->item_pos = null;
		$copy->_originalData = null;
		
		return $copy;
	}

	public function getPaymentTypeId() {
		return $this->payment_type_id;
	}

	public function getNamePl() {
		return $this->name_pl;
	}

	public function getNameEn() {
		return $this->name_en;
	}

	public function getName2Pl() {
		return $this->name2_pl;
	}

	public function getName2En() {
		return $this->name2_en;
	}

	public function getDescPl() {
		return $this->desc_pl;
	}

	public function getDescEn() {
		return $this->desc_en;
	}

	public function getForPoland() {
		return $this->for_poland;
	}

	public function getAbroad() {
		return $this->abroad;
	}

	public function getCommission() {
		return $this->commission;
	}

	public function getConstantCommission() {
		return $this->constant_commission;
	}

	public function getPaymentFeeUsd() {
		return $this->payment_fee_usd;
	}

	public function getPaymentFeeInfoPl() {
		return $this->payment_fee_info_pl;
	}

	public function getPaymentFeeInfoEn() {
		return $this->payment_fee_info_en;
	}

	public function getPaymentFeeNamePl() {
		return $this->payment_fee_name_pl;
	}

	public function getPaymentFeeNameEn() {
		return $this->payment_fee_name_en;
	}

	public function getItemStatus() {
		return $this->item_status;
	}

	public function getItemPos() {
		return $this->item_pos;
	}


}
?>

And the BasePaymentTypePeer class. The SHORT_FIELDS contstant contains all table fields which are not TEXT or BLOB, occasionally I use this for performance reasons where fetching long column data is unnecessary, but that is rare and somewhat ugly. ALL_FIELDS_FOR_JOIN is a list of fields that I can use for join queries when I want data from related tables to be put in appropriate objects as well.


&lt;?php
/* Do not edit this class! Use extended class for your stuff. */

class BasePaymentTypePeer {
	
	public static $cache = array();
	public static $cacheFast = array();
	
	const SHORT_FIELDS = 'payment_type.`payment_type_id`, payment_type.`name_pl`, payment_type.`name_en`, payment_type.`name2_pl`, payment_type.`name2_en`, payment_type.`for_poland`, payment_type.`abroad`, payment_type.`commission`, payment_type.`constant_commission`, payment_type.`payment_fee_usd`, payment_type.`payment_fee_name_pl`, payment_type.`payment_fee_name_en`, payment_type.`item_status`, payment_type.`item_pos`';
	
	const ALL_FIELDS_FOR_JOIN = 'payment_type.`payment_type_id` AS `payment_type.payment_type_id`, payment_type.`name_pl` AS `payment_type.name_pl`, payment_type.`name_en` AS `payment_type.name_en`, payment_type.`name2_pl` AS `payment_type.name2_pl`, payment_type.`name2_en` AS `payment_type.name2_en`, payment_type.`desc_pl` AS `payment_type.desc_pl`, payment_type.`desc_en` AS `payment_type.desc_en`, payment_type.`for_poland` AS `payment_type.for_poland`, payment_type.`abroad` AS `payment_type.abroad`, payment_type.`commission` AS `payment_type.commission`, payment_type.`constant_commission` AS `payment_type.constant_commission`, payment_type.`payment_fee_usd` AS `payment_type.payment_fee_usd`, payment_type.`payment_fee_info_pl` AS `payment_type.payment_fee_info_pl`, payment_type.`payment_fee_info_en` AS `payment_type.payment_fee_info_en`, payment_type.`payment_fee_name_pl` AS `payment_type.payment_fee_name_pl`, payment_type.`payment_fee_name_en` AS `payment_type.payment_fee_name_en`, payment_type.`item_status` AS `payment_type.item_status`, payment_type.`item_pos` AS `payment_type.item_pos`';
	
	const SHORT_FIELDS_FOR_JOIN = 'payment_type.`payment_type_id` AS `payment_type.payment_type_id`, payment_type.`name_pl` AS `payment_type.name_pl`, payment_type.`name_en` AS `payment_type.name_en`, payment_type.`name2_pl` AS `payment_type.name2_pl`, payment_type.`name2_en` AS `payment_type.name2_en`, payment_type.`for_poland` AS `payment_type.for_poland`, payment_type.`abroad` AS `payment_type.abroad`, payment_type.`commission` AS `payment_type.commission`, payment_type.`constant_commission` AS `payment_type.constant_commission`, payment_type.`payment_fee_usd` AS `payment_type.payment_fee_usd`, payment_type.`payment_fee_name_pl` AS `payment_type.payment_fee_name_pl`, payment_type.`payment_fee_name_en` AS `payment_type.payment_fee_name_en`, payment_type.`item_status` AS `payment_type.item_status`, payment_type.`item_pos` AS `payment_type.item_pos`';
	
	/* Mapping for each field: array($quoteStyle, $isNull) */
	public function getFieldMap() {
		return array(
			'payment_type_id' =&gt; array('s', false),
			'name_pl' =&gt; array('s', false),
			'name_en' =&gt; array('s', false),
			'name2_pl' =&gt; array('s', false),
			'name2_en' =&gt; array('s', false),
			'desc_pl' =&gt; array('s', true),
			'desc_en' =&gt; array('s', true),
			'for_poland' =&gt; array('i', false),
			'abroad' =&gt; array('i', false),
			'commission' =&gt; array('d', false),
			'constant_commission' =&gt; array('d', false),
			'payment_fee_usd' =&gt; array('d', false),
			'payment_fee_info_pl' =&gt; array('s', true),
			'payment_fee_info_en' =&gt; array('s', true),
			'payment_fee_name_pl' =&gt; array('s', true),
			'payment_fee_name_en' =&gt; array('s', true),
			'item_status' =&gt; array('i', false),
			'item_pos' =&gt; array('i', false),
		);
	}
	
	public function getByPK($PK, $use_cache = true) {
		if (array_key_exists($PK, PaymentTypePeer::$cache) && $use_cache) {
			return PaymentTypePeer::$cache[$PK];
		}
		return PaymentTypePeer::$cache[$PK] = Reg::$db-&gt;fetch_one_row("SELECT * FROM `payment_type` WHERE payment_type_id=" .Reg::$db-&gt;quote($PK, 's'), 'PaymentType');
	}

	public function getByPKFast($PK, $use_cache = true) {
		if (array_key_exists($PK, PaymentTypePeer::$cacheFast) && $use_cache) {
			return PaymentTypePeer::$cacheFast[$PK];
		}
		return PaymentTypePeer::$cacheFast[$PK] = Reg::$db-&gt;fetch_one_row("SELECT " .PaymentTypePeer::SHORT_FIELDS. " FROM `payment_type` WHERE payment_type_id=" .Reg::$db-&gt;quote($PK, 's'), 'PaymentType');
	}

	public function doSelect($sql, $joinedTables = null) {
		if (strcasecmp(substr(ltrim($sql), 0, 6), "SELECT") == 0) {
			return Reg::$db-&gt;fetch_all_rows_iterator($sql, "PaymentType", $joinedTables);
		}
		return Reg::$db-&gt;fetch_all_rows_iterator("SELECT * FROM `payment_type` ".$sql, "PaymentType");
	}
	
	public function doSelectFast($sql) {
		return Reg::$db-&gt;fetch_all_rows_iterator("SELECT " .PaymentTypePeer::SHORT_FIELDS. " FROM `payment_type` ".$sql, "PaymentType");
	}

	public function doSelectOne($sql, $joinedTables = null) {
		if (strcasecmp(substr(ltrim($sql), 0, 6), "SELECT") == 0) {
			return Reg::$db-&gt;fetch_one_row($sql, "PaymentType", $joinedTables);
		}
		return Reg::$db-&gt;fetch_one_row("SELECT * FROM `payment_type` ".$sql, "PaymentType");
	}
	
	public function doSelectOneFast($sql) {
		return Reg::$db-&gt;fetch_one_row("SELECT " .PaymentTypePeer::SHORT_FIELDS. " FROM `payment_type` ".$sql, "PaymentType");
	}

	public function reorderSort($whereSql) {
		Reg::$db-&gt;query("SET @a = 0");
		Reg::$db-&gt;query("UPDATE payment_type, (SELECT payment_type_id, @a:=@a+2 AS s FROM payment_type WHERE $whereSql ORDER BY item_pos ASC) dt
			SET payment_type.item_pos = dt.s WHERE payment_type.payment_type_id = dt.payment_type_id;");

	}
}
?&gt;

And there are also db access classes like Db (extending mysqli), DbResult (extending MySQLi_ResultDbRecord), DbRecord and DbRecordIterator - they are used by the classes posted above. If you want I can post them as well, my code is not secret, it can just take too much space in case this is not something you are interested in.

I don’t consider this system an engineering marvel, it just works well and the automatic generation of classes, controllers and templates is something of much help. And it’s quite fast performance-wise, which results in some half-ugly solutions sometimes. But I like it a lot because it gets the job done. If there is anything more you want to know, I can post more code, no problem.