MySqlQuery

Filepath: phork/php/database/MySql/MySqlQuery.class.php

The MySqlQuery class is used to generate queries for MySQL databases. Using the query generator isn't required, but it handles things like escaping strings automatically and helps make the application more portable.

There are 2 ways that a query can be generated. The longer way, which is slightly quicker but harder to read, or the shorter daisy-chained way, which is a bit slower but often times easier to read for simple queries.

Select Query Example

//get the query object from the database object

		$objQuery = AppRegistry::get('Database')->getQuery();

		

		//if there's a chance that the query object is in use, clone it

		$objQuery = clone $objQuery;

		

		//long format with a table join, count function, group by, having, and limit

		$objQuery->initSelectQuery();

		$objQuery->addTable('file');

		$objQuery->addTableJoin('folder', null, 'folderid', 'LEFT JOIN');

		$objQuery->addColumn('file.*');

		$objQuery->addColumn('folder.fullpath');

		$objQuery->addColumn('folder.hashlevel');

		$objQuery->addColumn($objQuery->buildFunction('COUNT', '*'), 'tally');

		$objQuery->addWhere('image', 1);

		$objQuery->addWhere('width', 400, '>');

		$objQuery->addGroupBy('type');

		$objQuery->addHaving('tally', 3, '>');

		$objQuery->addLimit(3);

		$strQuery = $objQuery->buildQuery();

		

		//short format

		$strQuery = $objQuery->select()->from('foo')->where('id', $intId)->buildQuery();

Insert Query Example

$objDb = AppRegistry::get('Database');

		$objQuery = $objDb->getQuery();

		

		//initialize a new insert query

		$objQuery->initInsertQuery();

		$objQuery->addTable('foo');

		$objQuery->addColumn('title', $strTitle);

		$objQuery->addColumn('body', $strBody);

		$objQuery->addColumn('created', date($objDb->getDatetimeFormat());

		$strQuery = $objQuery->buildQuery();

Update Query Example

$objDb = AppRegistry::get('Database');

		$objQuery = $objDb->getQuery();

		

		//initialize a new update query

		$objQuery->initInsertQuery();

		$objQuery->addTable('foo');

		$objQuery->addColumn('title', $strTitle);

		$objQuery->addColumn('body', $strBody);

		$objQuery->addColumn('updated', date($objDb->getTimestampFormat());

		$objQuery->addWhere('fooid', $intId);

		$strQuery = $objQuery->buildQuery();