Timothy J. Warren 172eb3f6eb Merge branch 'develop' 2 months ago
apiDocumentation Add updated documentation 3 months ago
build Remove method separators, update documentation building configuration 3 months ago
src Remove some old code coupling and boilerplate 3 months ago
tests Remove method separators, update documentation building configuration 3 months ago
.editorconfig Flatten source structure a bit 3 months ago
.gitignore Update file headers 3 months ago
.gitlab-ci.yml Update builds to remove PHP 7 support 3 months ago
.scrutinizer.yml Moved protected query builder methods to abstract class 4 years ago
.travis.yml Update builds to remove PHP 7 support 3 months ago
CONTRIBUTING.md Move development from fork to main repo on develop branch, test more PHP versions 1 year ago
LICENSE.md Code Style fixes 1 year ago
README.md Various refactoring 3 months ago
RoboFile.php Remove method separators, update documentation building configuration 3 months ago
composer.json Remove method separators, update documentation building configuration 3 months ago
phpdox.xml Remove some old code coupling and boilerplate 3 months ago
phpstan.neon Remove PDOInterface to prevent conflicts in method parameters with native PDO object 3 months ago
sonar-project.properties Move development from fork to main repo on develop branch, test more PHP versions 1 year ago

README.md

Query

A query builder/database abstraction layer, using prepared statements 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)
  • Supported version of PHP (Older versions may work, but are not supported)

Databases Supported

  • MySQL
  • PostgreSQL
  • SQLite

Including Query in your application

  • Install via composer and include vendor/autoload.php

Connecting

Create a connection array or object similar to this:

<?php

$params = array(
	'type' => 'mysql', // mysql, pgsql, firebird, sqlite
	'host' => 'localhost', // address or socket
	'user' => 'root',
	'pass' => '',
	'port' => '3306',
	'database' => 'test_db',

	// Only required for
	// SQLite 
	'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.

<?php
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

<?php

// 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 Query Builder class. However, it does not implement the update_batch or caching methods. For specific query builder methods, see the class documentation. Underscored methods are also aliased to camel case methods.

You can also run queries manually.

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

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

Retrieving Results:

An example of a moderately complex query:

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

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

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

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

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

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

Inserting / Updating

An example of an insert query:

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

An example of an update query:

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

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