<?php

$hostname = '127.0.0.1';
$username = 'root';
$password = '';
$database = 'my_db';
$backup_server = '127.0.0.2';

// Connect to a single MySQL server
$db = new DBAL\Database($hostname, $username, $password, $database);

// Connect to MySQL server and is primary server is down connect to secondary server
$db = new DBAL\Database($hostname, $username, $password, $database, $backup_server);

$db->select('test_table');
// Query Run = "SELECT * FROM `test_table` LIMIT 1;"

$db->select('test_table', array('id' => 3));
// Query Run = "SELECT * FROM `test_table` WHERE `id` = 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)));
// Query Run = "SELECT * FROM `test_table` WHERE `id` >= 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)), array('name', 'email'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)), array('name', 'email'), array('id' => 'DESC'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 ORDER BY `id` DESC LIMIT 1;"

// Usage of IN or NOT IN operator
$db->select('test_table', array('id' => array('IN' => array(1, 2, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` IN (1,2,3) LIMIT 1;"

$db->select('test_table', array('id' => array('NOT IN' => array(2, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` NOT IN (2,3) LIMIT 1;"

// Usage of BETWEEN or NOT BETWEEN operator
$db->select('test_table', array('id' => array('BETWEEN' => array(1, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` BETWEEN 1 AND 3 LIMIT 1;"

$db->select('test_table', array('id' => array('NOT BETWEEN' => array(2, 10))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` NOT BETWEEN 2 AND 10 LIMIT 1;"

// The same functions can be run using selectAll() rather than select()

$db->selectAll('test_table', array('id' => array('>=', 3)), array('name', 'email'), array('id' => 'DESC'), 150);
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 ORDER BY `id` DESC LIMIT 150;"

// Usage
// select($table, $where = array('field_name' => $value), $selectfield = array('field_1', 'field_2'), $order = array('field_name' => 'ASC' or 'DESC') or RAND());
// selectAll($table, $where = array('field_name' => $value), $selectfield = array('field_1', 'field_2'), $order = array('field_name' => 'ASC' or 'DESC') or RAND(), $limit(default = no limit));

$db->insert('test_table', array('name' => 'Bob', 'email' => '[email protected]'));
// Query Run = "INSERT INTO `test_table` (`name`, `email`) VALUES ('Bob', '[email protected]');"

// Usage
// insert($table, array('field_name' => $value));

$db->update('test_table', array('name' => 'Ken', 'email' => '[email protected]'), array('id' => 12345));
// Query Run = "UPDATE `test_table` SET (`name` => 'Ken', `email` => '[email protected]') WHERE `id` = 12345;"

$db->update('test_table', array('name' => 'Ken'), array('name' => 'Bob'), 50);
// Query Run = "UPDATE `test_table` SET (`name` => 'Ken') WHERE `name` = 'Bob' LIMIT 50;"

// Usage
// update($table, $updatevalues = array('field_name' => $value), $where = array('field' => $value));

$db->delete('test_table', array('id' => 1));
// Query Run = "DELETE FROM `test_table` WHERE `id` = 1;"

$db->delete('test_table', array('name' => 'Ted'), 50);
// Query Run = "DELETE FROM `test_table` WHERE `name` = 'Ted' LIMIT 50;"

// Usage
// delete($table, $where = array('field' => $value), $limit);

$db->count('test_table');
// Query Run = "SELECT COUNT(*) FROM `test_table`;";

$db->count('test_table', array('name' => 'Bob'));
// Query Run = "SELECT COUNT(*) FROM `test_table` WHERE `name` => 'Bob';";

// Usage
// count($table, $where = array('field' => $value));

$db->query("SELECT * FROM `test_table` WHERE `name` = ? OR `name` => ?;", array('John', 'Phil'));
// Query Run = "SELECT * FROM `test_table` WHERE `name` = 'John' OR `name` => 'Phil';";

$db->query("UPDATE `test_table` SET `name` = 'Karl' WHERE `name` = ? OR `name` => ?;", array('John', 'Phil'));
// Query Run = "UPDATE `test_table` SET `name` = 'Karl' WHERE `name` = 'John' OR `name` => 'Phil';";

$db->query("SELECT * FROM `test_table` WHERE `field1` = ? AND (`field2` => ? OR `field3` => ?);", array('value1', 'value2', 'value3'));
// Query Run = "SELECT * FROM `test_table` WHERE `field1` = 'value1' AND (`field2` => 'value2' OR `field3` => 'value3');";

# UNION
$db->query("SELECT * FROM `test_table` WHERE `field1` = ? UNION SELECT * FROM `another_table` WHERE `another_field` = ?;", array('value1', 'value2'));
// Query Run = SELECT * FROM `test_table` WHERE `field1` = 'value1' UNION SELECT * FROM `another_table` WHERE `another_field` = 'value2';";

$db->query("SELECT * FROM `test_table` WHERE `field1` = :search UNION SELECT * FROM `another_table` WHERE `another_field` = :search;", array(':search' => 'value1'));
// Query Run = SELECT * FROM `test_table` WHERE `field1` = 'value1' UNION SELECT * FROM `another_table` WHERE `another_field` = 'value1';";

# JOIN
$db->query("SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field`;");
// Query Run = SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field`;";

$db->query("SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field` AND `test_table`.`field1` <= ? LIMIT 100;", array('value'));
// Query Run = SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field` AND `test_table`.`field1` <= 'value' LIMIT 100;";

// This is similar to the select method except return the column value rather than an array of all of the fields 

$column = $db->fetchColumn('test_table', array('id' => 3), array('name', 'email'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` = 3 LIMIT 1;"
echo($column[0]); // will return the name field
echo($column[1]); // will return the email field

$column = $db->fetchColumn('test_table', array('id' => 3), array('name', 'email'), 1);
echo($column); // will return email as the field number of 1 has been set

$db->numRows();
$db->rowCount();

// Running either of these methods after executing a query will show you how many rows have been affected e.g.
$db->update('test_table', array('name' => 'Ken'), array('name' => 'Bob'));
echo($db->numRows()); // Returns number of results updated e.g. 12

$db->insert('test_table', array('name' => 'Bob', 'email' => '[email protected]'));
echo($db->lastInsertId()); // Returns the key field value number normally the the auto increment field value

$db->isConnected(); // Returns true of false depending on if the connection is active to the server

$caching = new DBAL\Caching\Memcached();
$db = new DBAL\Database($hostname, $username, $password, $database, $backup_server, $caching);

// OR

$caching = new DBAL\Caching\Memcached();
$db = new DBAL\Database($hostname, $username, $password, $database);
$db->setCaching($caching);