A Query Builder and Database abstraction layer
Go to file
Timothy Warren 232761398e Version 2.0.2, Adds checks for constants and functions in the common file 2015-06-04 15:27:55 -04:00
Query Update docs 2014-11-07 12:21:49 -05:00
docs Update Docs 2015-06-04 15:24:44 -04:00
drivers Scrutinizer Auto-Fixes 2014-08-08 20:02:14 +02:00
tests Version 2.0.2, Adds checks for constants and functions in the common file 2015-06-04 15:27:55 -04:00
.gitignore Fix some issues with multiple array items in where statements 2014-11-07 12:14:46 -05:00
.scrutinizer.yml Moved protected query builder methods to abstract class 2014-03-27 15:46:28 -04:00
.travis.yml Update Docs 2015-06-04 15:24:44 -04:00
CONTRIBUTING.md Contributing file 2012-09-25 19:23:34 +00:00
README.md Update docs, composer and README 2014-03-31 16:01:58 -04:00
autoload.php Version 2.0.2, Adds checks for constants and functions in the common file 2015-06-04 15:27:55 -04:00
common.php Version 2.0.2, Adds checks for constants and functions in the common file 2015-06-04 15:27:55 -04:00
composer.json Update docs, composer and README 2014-03-31 16:01:58 -04:00
phpci.yml Fix some issues with multiple array items in where statements 2014-11-07 12:14:46 -05:00
phpdoc.dist.xml Docblock updates 2014-04-15 16:16:15 -04:00

README.md

Query

A query builder/database abstraction layer, using prepared queries for security.

Build Status Code Coverage Scrutinizer Code Quality Latest Stable Version Total Downloads Latest Unstable Version License

Requirements

  • Pdo extensions for the databases you wish to use (unless it's Firebird, in which case, the interbase extension is required)
  • PHP 5.3+

Databases Supported

  • Firebird (via interbase extension)
  • MySQL
  • PostgreSQL
  • SQLite

Including Query in your application

To include Query in your PHP project, just include the autoload.php file. This will automatically load the classes that are supported by the current PHP installation.

Connecting

Create a connection array or object similar to this:

<?php

$params = array(
	'type' => 'mysql',
	'host' => 'localhost',
	'user' => 'root',
	'pass' => '',
	'port' => '3306',
	'database' => 'test_db',

	// Only required
	// SQLite or Firebird
	'file' => '/path/to/db/file',

	// Optional paramaters
	'prefix' => 'tbl_', 	// Database table prefix
	'alias' => 'old' 		// Connection name for the Query function
);

$db = Query($params);

The parameters required depend on the database.

Query function

You can use the Query() function as a reference to the last connected database. E.g.

Query()->get('table_name');

or

$result = Query()->query($sql);

If the alias key is set in the parameters, you can refer to a specific database connection

// Set the alias in the connection parameters
$params['alias'] = 'old';

// Connect to the legacy database
Query('old')->query($sql);

Running Queries

Query uses the same interface as CodeIgniter's Active Record class. However, it does not implement the update_batch or caching methods.

####You can also run queries manually.

To run a prepared statement, call $db->prepare_execute($sql, $params).

To run a plain query, $db->query($sql)

Retrieving Results:

An example of a moderately complex query:

$query = $db->select('id, key as k, val')
	->from('table t')
	->where('k >', 3)
	->or_where('id !=' 5)
	->order_by('val', 'DESC')
	->limit(3, 1)
	->get();

This will generate a query similar to (with this being the output for a Postgres database):

SELECT "id", "key" AS "k", "val"
FROM "table" "t"
WHERE "k" > ?
OR "id" != ?
ORDER BY "val" DESC
LIMIT 3 OFFSET 1

To retreive the results of a query, use the PDO method fetch and/or fetchAll.

$query = $db->get('table_name');

$results = $query->fetchAll(PDO::FETCH_ASSOC);

Inserting / Updating

An example of an insert query:

$query = $db->set('foo', 'bar')
	->set('foobar', 'baz')
	->where('foo !=', 'bar')
	->insert('table');

An example of an update query:

$query = $db->set('foo', 'bar')
	->set('foobar', 'baz')
	->where('foo !=', 'bar')
	->update('table');

The set method can also take an array as a paramater, instead of setting individual values.