Documentation

Table of content

  1. 1. Datasource connection
    1. 1.1 Datasource connection overview
    2. 1.2 Connect to an existing database
    3. 1.3 Connect to a database server
    4. 1.4 Close a connection
  2. 2. Database query
    1. 2.1 Database class overview
    2. 2.2 Transactions
    3. 2.3 Get all tables in database
    4. 2.4 Get a table object
    5. 2.5 Enable/Disable foreign keys
    6. 2.6 Create a foreign keys
    7. 2.7 Drop a foreign keys
  3. 3. Table query
    1. 3.1 Table object overview
    2. 3.2 Select data (SQL SELECT)
      1. 3.2.1 Select class overview
      2. 3.2.2 Output columns
      3. 3.2.3 External columns (SQL JOIN)
      4. 3.2.4 Sub SELECT query column
      5. 3.2.5 Define data filters (SQL WHERE)
      6. 3.2.6 Define group by (SQL GROUP BY)
      7. 3.2.7 Define aggregate filters (SQL HAVING)
      8. 3.2.8 Define order by (SQL ORDER BY)
      9. 3.2.9 Limit results (SQL LIMIT)
      10. 3.2.10 Define offset (SQL OFFSET)
      11. 3.2.11 Output format
      12. 3.2.12 Custom query string
    3. 3.3 Insert data (SQL INSERT)
      1. 3.3.1 Insert class overview
      2. 3.3.2 Basic usage
      3. 3.3.3 Prepared query
      4. 3.4.4 Get affected rows (rowCount)
    4. 3.4 Update data (SQL UPDATE)
      1. 3.4.1 Update class overview
      2. 3.4.2 Basic usage
      3. 3.4.3 Increment/Decrement a column
      4. 3.4.4 Filter data (SQL WHERE)
      5. 3.4.5 Get affected rows (rowCount)
    5. 3.5 Delete data (SQL DELETE)
      1. 3.5.1 Delete class overview
      2. 3.5.2 Basic usage
      3. 3.5.3 Filter data (SQL WHERE)
    6. 3.6. Filter query (SQL WHERE)
      1. 3.6.1 Where class overview
      2. 3.6.2 Operators
      3. 3.6.3 Multiple filters
      4. 3.6.4 Full sample
    7. 3.7 Test if a table exists
    8. 3.8 Rename an existing table
    9. 3.9 Create a table (SQL CREATE TABLE)
      1. 3.9.1 CreateTable class overview
      2. 3.9.2 Add columns
      3. 3.9.3 Define column type
      4. 3.9.4 Define null / not null contraints
      5. 3.9.5 Define primary key contraints
      6. 3.9.6 Define unique contraint
      7. 3.9.7 Define defaults values
      8. 3.9.8 Define auto increment column
      9. 3.9.9 Define foreign key contraints
      10. 3.9.10 Create table if not exists
    10. 3.10 Drop a table (SQL DROP TABLE)
  4. 4. Server query
    1. 4.1 Server class overview
    2. 4.2 Check if a database exists
    3. 4.3 Get the list of databases
    4. 4.4 Create a database
    5. 4.5 Drop a database
    6. 4.6 Check if a user exists
    7. 4.7 Get the list of users
    8. 4.8 Create a user
    9. 4.9 Grant a user
    10. 4.10 Drop a user
  5. 5. Handle query errors
    1. 5.1 Handle errors without transaction
    2. 5.2 Handle errors in a transaction

Overview


Patabase is a minimalist SQL query builder for PHP. No reinvented wheel, Patabase is build on top of PHP PDO. It provides class and methods to build and perform queries on a datasource.

Why using it?

Patabase could help to:

  • Write code that works on any implemented driver, just change a few parameters and keep most of code without any changes,
  • Write complex queries with managed code that takes care of driver specific synthax,
  • Secure your code and prevent from SQL injection using prepared statement.

1. Connect to a datasource


In this section

1.1 Connection overview

Patabase provides two main class to connect to a datasource:

  • the Database class represents a connection to a database. This class allows to connect to a given database and perform queries on that database and its tables (get the tables list, create a table, select, delete, insert or update data, ...).
  • the Server class represents a connection to a database server. This class allows to connect to a database server and perform queries like create users and databases, rename or drop a database, ....

Create a connection consists in create an instance of Database class or Server class. Those class inherits from Datasource object. The class constructor takes as unique argument a key/value pair array containing the connection settings. Connection settings must specify the driver and other parameters that represent the DSN (Data Source Name), and may contain others optional parameters.

The supported drivers are listed below:

Driver Code Database Server
Mysql 'mysql'
Postgres 'pgsql'
Sqlite 'sqlite'

Each driver define the minimal required settings to connect to a datasource. The required and optional parameters for each driver are listed below:

Setting parameter Sqlite Mysql/Maria db? Postgres SQL
'driver' Required Required Required
'hostname' N/A Required Required
'port' N/A Optional Optional
'database' Required (1) Required or N/A (2) Required or N/A (2)
'username' N/A Required Required
'password' N/A Required Required
'charset' N/A Optional (3) N/A

  1. In case of sqlite, the database attribute represents the full path to database. The database attribute can be ':memory:' to create an in memory database connection.
  2. Required in case of Database connection, N/A in case of Server connection.
  3. Default is UTF-8.

The code that creates a connection should be placed in a try{} catch{} block. In addition to PDOException create a connection with invalid or missing settings parameters will raise an exception (Patabase\Exception\InvalidArgException or Patabase\Exception\MissingArgException).


Here are examples of connection settings:

// sqlite database settings
$settings = array(
    'driver'    => 'sqlite', 
    'database'  => 'YOUR_FILE_PATH'
);

// postgres server settings with port
$settings = array(
        'driver'    => 'pgsql', 
        'hostname'  => 'localhost', 
        'username'  => 'YOUR_USER_NAME',
        'password'  => 'YOUR_PASSORD'
        'port'      => 'YOUR_PORT'
);

// mysql database settings
$settings = array(
        'driver'    => 'mysql', 
        'hostname'  => 'localhost', 
        'database'  => 'MyDatabase',
        'username'  => 'YOUR_USER_NAME',
        'password'  => 'YOUR_PASSORD'
);

1.2 Connect to a database

To connect to a database, you use the Database class. Database class must be instanciated by passing connection settings in constructor. Patabase may contains driver specific derived class, like SqliteDatabase. Those class can also be instanciated by passing settings as parameters, but they may contains static method to help define the settings. For example, the Driver\Sqlite\SqliteDatabase class contains a build in static method createInstance($fileName) that requires file path as unique parameter.

The following examples show how to create Databaseinstance with minimal configuration. See connection overview section to know the optional parameters for each driver.

Connect to sqlite using Database class and given settings:

use Kristuff\Patabase\Database;
$db = new Database(array('driver' => 'sqlite', 'database' => 'YOUR_FILE_PATH')); 

Connect to sqlite using Kristuff\Patabase\Driver\Sqlite\SqliteDatabase class and given settings:

use Kristuff\Patabase\Driver\Sqlite\SqliteDatabase;
$db = new SqliteDatabase(array('driver' => 'sqlite', 'database' => 'YOUR_FILE_PATH')); 

Connect to sqlite using Kristuff\Patabase\Driver\Sqlite\SqliteDatabase class and build in createInstance() static method (required only file path as parameter):

use Kristuff\Patabase\Driver\Sqlite\SqliteDatabase;
$db = SqliteDatabase::createInstance('YOUR_FILE_PATH');

Create a sqlite memory instance using Kristuff\Patabase\Driver\Sqlite\SqliteDatabase class and build in createMemoryInstance() static method (no parameter):

use Kristuff\Patabase\Driver\Sqlite\SqliteDatabase;
$db = SqliteDatabase::createMemoryInstance('YOUR_FILE_PATH');

In Sqlite, if the specified file doesn't exists, a file is automatically created in case of successfull connection.

Connect to Mysql database:

$db = new Kristuff\Patabase\Database(array(
        'driver' => 'mysql', 
        'username' => 'YOUR_USERNAME', 
        'password' => 'YOUR_PASSWORD', 
        'database' => 'YOUR_DATABASE'
)); 

Connect to Posgres database:

$db = new Kristuff\Patabase\Database(array(
        'driver' => 'pgsql', 
        'username' => 'YOUR_USERNAME', 
        'password' => 'YOUR_PASSWORD', 
        'database' => 'YOUR_DATABASE'
)); 

1.3 Connect to a database server

To connect to a database server, you use the Server class. Server class must be instanciated by passing connection settings in constructor, like Database class. in case of database server connection, the username and password refer to server (when it refers to database in case of database connection).

The following examples show how to create Serverinstance with minimal configuration. See connection overview section to know the optional parameters for each driver.

The following example shows how to connect to a Mysql Server.

$settings = array('driver'=> 'mysql', 
                    'hostname'=> 'localhost', 
                    'username'=> 'USER_NAME',
                    'password' => 'PASSWORD');
$mysql = new Kristuff\Patabase\Server($settings);
// Do something with server

The following example shows how to connect to a Postgres Server.

$settings = array('driver'=> 'pgsql', 
                    'hostname'=> 'localhost', 
                    'username'=> 'USER_NAME',
                    'password' => 'PASSWORD');
$pgsql = new Kristuff\Patabase\Server($settings);
// Do something with server

1.4 Close a connection

Each of the Database and Server class contains a closeConnection() method that releases the current connection. This method releases the connection and settings, that means when you call the closeConnection() method, you cannot connect again to the datasource using the same Datasource instance.

Generally, you wont need to call the closeConnection() method. Just set the Datasource class (Database or Server) to null : the destructor of the class will internally call the closeConnection() method.

The following examples show how to close a database connection:

$db = new Kristuff\Patabase\Database( ... );
(...)
$db = null;

The following examples show how to close a server connection:

$srv = new Kristuff\Patabase\Server( ... ) ; 
(...)
$srv = null;

2. Database query



Use the Database or derived class to peform query on a database and its tables. This section presents the Database object and some of its methods. See also the Table query section that presents the database table methods.

In this section

2.1 Database class overview

The Database class inherits from Datasource and contains the following methods:

Method Parameters Return Description
Database::beginTransaction() void Start a transaction. See Transactions
Database::closeConnection() void Close the current PDO connection (inherited from Datasource). See close a connection
Database::commit() void Commit the current transaction changes. See Transactions
Database::createTable($name) string $name: the name for the table to create TableCreate Get a new CreateTable instance. See Create a table
Database::delete($tablename) string $tableName: the name of the table in which delete. Query\Delete Get a new Delete query instance. See See Delete data
Database::disableForeignKeys() void Disable foreign keys in current database.
Database::dropTable($tableName) string $name: the name of the table bool Drop a table. This method return True if the table has been dropped, otherwise False. See Delete a table
Database::enableForeignKeys() void Enable foreign keys in current database.
Database::getConnection() PDO Get the current PDO connection (inherited from Datasource).
Database::getDriver() Driver\DriverBase Get the driver for current connection (inherited from Datasource).
Database::getDriverName() string Get the name of the driver for current connection (inherited from Datasource).
Database::getVersion() string Get the patabase library version like "0.0.0" (inherited from Datasource).
Database::insert($tableName) string $tableName: the name of the table in which insert to. Query\Insert Get a new Insert query instance. See See Insert data
Database::inTransaction() bool Get whether the driver is in transaction. Return True if the driver is in transaction, otherwise False. See Transactions
Database::renameTable($name, $newName) string $name: the current name of the table, string $newName: the new name for the table. bool Rename a table. This method returns True if the table has been renamed, otherwise False. See Rename a table
Database::rollback() void Rollback changes made during transaction. See Transactions
Database::select([$columns]) mixed$columns (optional) Query\Select Get a new Select query instance. See Select data
Database::table($name) string $name : The Name of the table Table Get a new Table instance. Get a table object
Database::tableExists($name) string $name: the name of the table bool Check if a table exists. This method returns True if the table exists, otherwise False. See Check if a table exists
Database::update($tablename) string $tableName: the name of the table in which update. Query\Update Get a newUpdate query instance. See See Update data

2.2 Transactions

Database queries could be executed in a transaction. Use the Database::beginTransaction(), Database::commitTransaction() to start a transaction and commit changes. Use the Database::rollback() to cancel changes.

You can also check if the driver is in transaction with Database::inTransaction() method.

The way Patabase handles Exception changes when using transaction. A transaction must be placed in a try{} catch{} block. See Handle query errors.

try {
    // start transaction
    $database->beginTransaction();
    (...)
    // commit changes
    $database->commit();
}
catch(SqlException $e) {
    // rollback changes if an error occured
    $database->rollback();
    // you may also want to check error with: 
    //  $e->getCode() and $e->getMessage()
}

2.3 Get all tables in current database

Use the Database::tables() method to get the list of tables in current database. The Database::tables() method returns an arrayarray with the name of each table.

$tables = $db->getTables();
print_r($tables);
This code will produce something like this:
array (size=2)
  0 => string 'user_role' (length=9)
  1 => string 'user' (length=4)

2.4 Get a table object

Use Database::table($name) method to get a new Table instance. You must specify the name of the table. The method does not throw any error when the given table does not exist, but the queries executed with this object will failed.

For example, to access a Table named 'user' from a Database object, you write:

$table = $database->Table('user');

You generally don't need to get an instance of Table to perform query on a table. All Table methods are alias for Database methods. It could be useful to use a Table to perform multiple queries on the same table.

2.5 Enable/Disable foreign keys

Foreign keys are enabled by default. You can use the Database::disableForeignKeys() and Database::enableForeignKeys() to disable or enable foreign key.

$database->disableForeignKeys();
(...)
$database->enableForeignKeys(); 

The enableForeignKeys() and disableForeignKeys() have no effect on Postgres.

2.6 Create a foreign keys

You can use the Database::addForeignKey($fkName, $srcTable, $srcColumn, $refTable, $refColumn) to add a foreign key. You must specify the constraint name, and sources and references tables and columns. This method returns true if the foreign key has been created, otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors

The following example show how to create a foreign key on table 'user' when the column 'user_role' with reference to column 'role_id' on table 'role':

$db->addForeignKey('fk_user_role', 'user', 'user_role', 'role', 'role_id'));

The dropForeignKeys() and addForeignKeys() methods are not supported on Sqlite and return false. Constraints must be created when creating the table on Sqlite and can't be deleted.

2.7 Drop a foreign keys

You can use the Database::dropForeignKeys($fkName, $tableName) to drop a foreign key. You must specify the constraint name and table name. This method returns true if the foreign key has been dropped, otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors

The following example show how to drop a foreign key named 'fk_user_role' on table 'user':

$database->dropForeignKeys('fk_user_role', 'user');

The dropForeignKeys() and addForeignKeys() methods are not supported on Sqlite and return false. Constraints must be created when creating the table and can't be deleted on Sqlite.

3. Tables query


To peform query on a given table in database, you can use both the Database methods, or the Table methods. The second way could be useful to perform different queries on the same table. If so, you will need an instance of Table class. You can get an instance of Table class through the Database::Table() method, see Get a table in database.

In this section:

3.1 Table class overview

The Table class contains the following methods:

Method Arguments Return Description
Table::create() Query\CreateTable Get a new CreateTable instance. See Create a table
Table::database() Database Get the Database parent instance.
Table::delete() Query\Delete Get a new Delete query instance. See See Delete data
Table::drop() bool Delete a table. This method return True if the table has been deleted, otherwise false. See Delete a table
Table::exists() bool Check if a table exists. This method returns True if the table exists, otherwise false. See Check if a table exists
Table::insert([$parameters]) array $parameters (optional) Query\Insert Get a new Insert query instance. See See Insert data
Table::name() string Get the current name of the table.
Table::rename($newName) string $newName: the new name for the table. bool Rename a table. This method returns True if the table has been renamed, otherwise false. See Rename a table
Table::select([$columns]) mixed$column (optional) Query\Select Get a new Select query instance. See Select data
Table::update([$parameters]) array $parameters (optional) Query\Update Get a newUpdate query instance. See See Update data

3.2 Select data (SQL SELECT)

Use the Database::select() or Table::select() method to select data in a given table.

In this section

3.2.1 Select Overview

The Database::select() and Table::select() method returns a new instance of Query\Select you can configure (see Parameter methods below) and execute (see Executive methods below) to return data in desired format (see Output format).

When you get a Select instance from Table object, you don't need to specify the table name. Otherwise, you must define the table name with Select::from($tablename) method.

Synopsys:
Database->select([...])->from('tablename')[(Parameter_Method)]->Executive_Method()
Or
Database->table('tablename')->select([...])->[(Parameter_Method)]->Executive_Method()

Select supports the following synthax:

SELECT [DISTINCT] [(column_expression [AS alias])] [(function_expression AS alias)] [(sub_select [AS alias])]
FROM table_source
[JOIN (table_target)]
[WHERE (filter_expression)]
[GROUP BY (groupBy_expression)]
[HAVING (having_expression)]
[ORDER BY (orderBy_expression)]
[LIMIT value]
[OFFSET value]

Most parameters methods return the Select object itself ($this). So, except for some query, you can set multiple parameters and execute the query with a single line of code. Example:

Database->table('user')->select()->[Parameter_Method]->[Parameter_Method]->[Executive_Method];

Parameter methods:
Function Arguments Return Description
Select::distinct() - $this Define the used of DISTINCT keyword to true. (see Select columns)
Select::columns($columns) mixed $columns: The output columns $this Define the output columns. (see Select columns)
Select::column($column, $alias) string $column: The column name
string $alias: The column alias (optional)
$this Add a column to the list of output columns (see Select columns)
Select::sum($column, $alias) $column: string, the column to sum
$alias: string, the alias for the output column
$this Add a sum column to the list of output columns (see Ouput columns)
Select::count() - $this Add a sum column to the list of output columns (see Ouput columns)
Select::select() - $this Get a sub select query (see sub query)
Select::from($tableName) - $this Define the table name for the FROM statement.
When using the Select object from a Table object ( Table($TableName)::select()), the table name is already defined and you don't need to call this method. You must use this method to specify the referenced table in sub query.
Select::where() - Query\Where Returns the Where instance to define the WHERE conditions (see Filter query).
Select::whereEqual($column, $value) string $column: the column name,
mixed $value: the column value
$this Add a 'WHERE column = value' condition. This method is an alias for Select::where()->equal($column, $value) (see Filter query).
Select::groupBy($columns) $column: array, the list of groupby columns
$this Define the GROUP BY. (see Define Group by)
Select::orderAsc($column) $column: string, the column name
$this Add an ORDER BY column (ascending sort). (see Define Order by)
Select::orderDesc($column) $column: string, the column name
$this Add an ORDER BY column (descending sort). (see Define Order by)
Select::orderRandom($seed) $seed: int, the random seed (optional)
$this Add an ORDER BY column (random sort). (see Define Order by)
Select::limit($limit) int $limit: The query LIMIT. $this Define the query LIMIT. (see Limit result)
Select::offset($offset) int $offset: The query OFFSET. $this Define the query OFFSET. (see Define offset)


Executive methods
Function Arguments Return Description
Select::getAll([$format]) string $format: The desired format (optional). mixed Returns all items in the specified format.
If no format given, the default format is used. (see Output format).
Select::getOne([$format]) string $format: The desired format (optional). mixed Returns first matching in the specified format
If no format given, the default format is used. (see Output format).
Select::getColumn() mixed Returns the value of one column.

3.2.2 Define output columns

The select columns represent the output columns. Select columns could be:

  • a column from the main table, identified with its name and an optional alias.
  • a column from another table, identified with its name and an optional alias
  • a function column like count or sum
  • a sub select query column
  • or nothing. If no column parameters given, the query return all columns (SELECT * ).

When columns are defined, you can use the distinct() function.

There are many way to select the output columns :

  1. Pass column names as arguments of Select() method,
  2. Use the Select::columns($columns) method,
  3. Use Select::column($columnName [, $alias]) method for each column
  4. Use function method like Select::count([$alias]) or Select::sum([$columnName, $alias]) method for each column
  5. Use a sub select query using Select::select() method.

When using the Select::columns($columns) method or passing argument to the Select constructor, the $columns argument could be:

  1. A list of column names (one argument for each column) ('col1', 'col2', ...)
  2. A non indexed array of column names (array('col1', 'col2', ...))
  3. An indexed array of column names/alias
    ( array('col1' => 'alias1', 'col2' => 'alias2', ...))

If no column parameters are given, the query return all columns (SELECT *).


The following example shows a basic usage of select query, without define columns:

// No columns argument: Select *
$database->select()
         ->from('user')
         ->getOne('json')); 

$database->table('user')
         ->select()
         ->getOne('json')); 

The following examples show how to define, using all available methods, columns 'name' and 'age' without alias:

// 1) list of column names in select constructor
$database->select('name', 'age')
         ->from('user')
         ->getOne('json')); 
// or
$database->table('user')
         ->select('name', 'age')
         ->getOne('json')); 

// 2) list of column names in columns() method
$database->table('user')
         ->select()
         ->columns('name', 'age')
         ->getOne('json')); 
// or
$database->select()
         ->columns('name', 'age')
         ->from('user')
         ->getOne('json')); 

// 3) use column() method for each column, define name
$database->select()
         ->column('name')
         ->column('age')
         ->from('user')
         ->getOne('json')); 
// or
$database->table('user')
         ->select()
         ->column('name')
         ->column('age')
         ->getOne('json')); 

The following examples show how to define columns 'name 'and 'age' with alias:

// 1) array of column names/alias in select constructor
$db->table('user')
           ->select(array('name' => 'AliasForName', 'age' => 'AliasForAge'))
           ->getOne('json');

// 2) array of column names/alias with columns() method
$db->table('user')
           ->select()
           ->columns(array('name' => 'AliasForName', 'age' => 'AliasForAge'))
           ->getOne('json');

// 3) use column() method for each column, define name and alias
$db->table('user')
            ->select()->column('name', 'AliasForName')
            ->column('age', 'AliasForAge')
            ->getOne('json');

The following example use the distinct function:

$database->select('name', 'age')
          ->distinct()
          ->from('user')
          ->getOne('json'); 

Following examples show how to use function columns like sum or count:

$result = $db->table('user')->select()->sum('age', 'total_age')->getOne('json');
// Output for $result: 
[{"total_age":224}]

Following examples show how to use count() function column:

$result = $db->table('user')->select()->count('user_number')->getOne('json'); 
// Output for $result: 
[{"user_number":8}]

3.2.3 External columns (JOIN)

If you need columns from other tables in your query (SQL JOIN), you need to use the following methods to specify external table(s):

  • Select::innerJoin() or Select::join() (alias) methods for SQL INNER JOIN, or
  • Select::leftJoin() method for SQL LEFT OUTER JOIN,
  • Select::rightJoin() method for SQL RIGHT OUTER JOIN (not supported on Sqlite),
  • Select::fullJoin() method for SQL FULL OUTER JOIN (not supported on Mysql and Sqlite).

All methods return $this and take the same arguments:

  • string $externalTable: the external table
  • string $externalColumn: the column in the external table
  • string $localTable: the local table
  • string $localColumn: the column in the local table

When you use one of these methods, make sure you specify the table name when define the column, for example:

column('tableName.columnName', 'aliasName')

The following examples suppose a database with tables 'customer' and 'order' with the following structure and data:

            customer                             order              
|-------------------------------|       |----------------------------|
|  customerId  |  customerName  |       |   orderId   |  customerId  |
|--------------|----------------|       |-------------|--------------|
|      1       |   customerB    |       |    10308    |      2       |   
|      2       |   customerZ    |       |    10309    |      1       |
|      3       |   customerA    |       |    10310    |      1       |

The following example shows how to use the Select::innerJoin()

$database->select()
         ->column('customer.customerName')
         ->column('order.orderId')
         ->from('customer')
         ->innerJoin('order', 'customerId', 'customer', 'customerId')
         // could also be writted like with: 
         //->join('order', 'customerId', 'customer', 'customerId')  
         ->orderAsc('order.orderId')
         ->getAll('json');
// Ouput
[{"customerName":"customerZ","orderId":10308},
 {"customerName":"customerB","orderId":10309},
 {"customerName":"customerB","orderId":10310}]

The following example shows how to use the Select::rightJoin()

$database->select()
         ->column('customer.customerName')
         ->column('order.orderId')
         ->from('order')
         ->rightJoin('customer', 'customerId', 'order', 'customerId')
         ->orderAsc('customer.customerName')
         ->getAll('json');
// Ouput
[{"customerName":"customerA","orderId":null},
 {"customerName":"customerB","orderId":10309},
 {"customerName":"customerB","orderId":10310},
 {"customerName":"customerZ","orderId":10308}]

The following example shows how to use the Select::fullJoin()

$database->select()
         ->column('order.orderId')
         ->column('order.orderDate')
         ->column('customer.customerName')
         ->from('order')
         ->fullJoin('customer', 'customerId', 'order', 'customerId')
         ->orderAsc('order.orderId')
         ->getAll('json');
// output
[{"orderId":10307,"orderDate":"2015-10-30","customerName":null},
 {"orderId":10308,"orderDate":"2016-09-18","customerName":"customerZ"},
 {"orderId":10309,"orderDate":"2016-09-20","customerName":"customerB"},
 {"orderId":10310,"orderDate":"2016-10-04","customerName":"customerB"},
 {"orderId":null,"orderDate":null,"customerName":"customerA"}]

3.2.4 Sub SELECT query column

Ouput columns could contain sub SELECT queries using the Select::select($alias) method. This method returns a new Select instance you can configure like the current Select. When created a sub select query, you must specify the alias ($alias) and then define referenced table using Select::from($tableName) method.

The following example shows how to use a count column in a sub query:

$query = $database->select();
// colum from main table
$query->from('user');
      ->column('name', 'userName')
      ->column('role', 'userRole')
      ->from('user');

// sub query
$query->select('test_count')
      ->count('count_role')
      ->from('user_role');

// get sql
echo $query->sql();
SELECT "name" AS "userName", "role" AS "userRole", (SELECT COUNT(*) AS "count_role" FROM "user_role") AS "test_count" FROM "user"

3.2.5 Filter data

Use the Select::where() method to define the WHERE sql filters. You can also use the Select::whereEqual($column, $value) method that is an alias for Select::where()->equal($column, $value) method (returns $this). See Filter query.

3.2.6 Define group by (SQL GROUP BY)

Use the Select::groupBy() to group results. You must specify the column name as parameter of the function. You can pass more than one column in function parameters. This method returns $this.

This is a basic example of groupBy() usage:

$database->select('name)
         ->from('name')
         ->groupBy('name')
         ->getAll('cols')); 

// output: Array
(
    [0] => Bryan
    [1] => Chris
    [2] => Jane
    [3] => Jo
    [4] => John
    [5] => Steve
)

This example show how to use Select::groupBy() combined with Select::count() method.

$database->table('user')
         ->select()
         ->column('name')
         ->count('number')
         ->groupBy('name')
         ->getAll('jsonpp')); 

// output: [
    {
        "name": "Bryan",
        "number": 3
    },
    {
        "name": "Chris",
        "number": 1
    },
    {
        "name": "Jane",
        "number": 1
    },
    {
        "name": "Jo",
        "number": 1
    },
    {
        "name": "John",
        "number": 1
    },
    {
        "name": "Steve",
        "number": 1
    }
]

3.2.7 Aggregate function filters (SQL HAVING)

Select class provides a method having() that returns a Query\Having object you can configure with filter methods.

Synopsis
Select->having()->[filter method]->having()->[filter method]->[executive_method]
Or
Select->having()->beginOr()->[filter_method]->[filter_method]->closeOr()->[executive_method]

Each filter method inside the Having object returns the Select parent or the Having itself ($this), in the same way that the Where object (see Multiples filters). The filters method functions are listed below:

Function Arguments Return Description
Having::fn($function, $column, $operator, $value) string $function: The function name without parenthesis ('SUM', 'COUNT', ...),
string $column: The name of column,
string $operator: The logic operator ('=', '>', ...),
mixed $value: The condition value
$this|QueryBuilder Add a custom HAVING function statement.
Having::sum($column, $operator, $value) string $column: The name of column,
string $operator: The logic operator,
mixed $value: The condition value
$this|QueryBuilder Add an HAVING SUM() statement.
Having::count($operator, $value) string $operator: The logic operator,
mixed $value: The condition value
$this|QueryBuilder Add an HAVING COUNT() statement.

The following example uses the Having::sum() method:

$database->select()
         ->column('name', 'alias_for_name')
         ->sum('numeric_column', 'alias_for_sum')
         ->from('user')
         ->having()->sum('numeric_column','>', 50)
         ->groupBy('name')
         ->getAll();

The following example uses the genreric Having::fn() method:

$database->select()
         ->column('name')
         ->sum('numeric_column', 'alias_for_sum')
         ->having()->fn('SUM','numeric_column','<=', 50)
         ->groupBy('name')
         ->getAll();

3.2.8 Define order by (SQL ORDER BY)

You can use the Select::orderBy($column, $order) to add an ORDER BY statement. You must specify the column name and the order. You can also use the predefined functions like Select::orderAsc($column), Select::orderDesc($column) or Select::orderRand(). All these method returns $this.

The Select query could contain one or more ORDER BY statement. They will be applied in the order they were added.

This is a basic example of Select::orderBy() usage:

$database->select('name', 'age')
         ->from('user')
         ->orderBy('name', 'ASC')
         ->getAll(); 

This example uses Select::orderAsc() and Select::orderDesc():

$database->select('name', 'age')
         ->from('user')
         ->orderAsc('name')
         ->orderDesc('age')
         ->getAll(); 

3.2.9 Limit result

Use the Select::limit(int $value) method before executing the query to limit result. You must define the limit. This method returns $this.

This example limits the result to 2:

$database->select()
         ->from('user')
         ->orderAsc('id')
         ->limit(2)
         ->getAll();

3.2.10 Limit with Offset result

Use the Select::offset(int $value) method before executing the query to limit result. You must define the offset. This method returns $this.

Most drivers do not support OFFSET without a LIMIT

Ths example select limits the result to 5 with offset of 10 :

$database->select()
         ->from('user')
         ->orderAsc('id')
         ->limit(5)
         ->offset(10)
         ->getAll();

3.2.11 Output format

When your select query is ready, to can call an executive method to render query results:

  • The Select::getColumn() does not take any argument and returns the value of one column in one row,
  • The Select::getOne([$format]) returns all values in one row,
  • The Select::getAll([$format]) returns all results.

Select::getAll() and Select::getOne() methods take the desired format as unique argument. The $outputFormat must be a string form the following list. If no output format is specified, the default format is used, (current default is 'asso').

CodeDescriptionComments
'asso' Associative array Corresponds to PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set.
'obj' Object Corresponds to PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set
'json' JSON Returns an associative array encoded in JSON format (JSON_NUMERIC_CHECK option).
'jsonpp' JSON Returns an associative array encoded in JSON format (JSON_NUMERIC_CHECK and JSON_PRETTY_PRINT options).

The following example uses all available output formats:

$asso   = $db->select()->from('user')->limit(2)->getAll('asso');
$obj    = $db->select()->from('user')->limit(2)->getAll('obj');
$json   = $db->select()->from('user')->limit(2)->getAll('json');
$jsonpp = $db->select()->from('user')->limit(2)->getOne('jsonpp');
$cols   = $db->select()->from('user')->getOne('cols');
$col    = $db->select('name')->from('user')->getColumn();
print_r($asso);
print_r($obj);
print_r($json);
print_r($jsonpp);
print_r($cols);
print_r($col);

Output:

[{"id":1,"name":"Bryan","role":1,"age":34},{"id":2,"name":"Steve","role":2,"age":32}]
[
    {
        "id": 1,
        "name": "Bryan",
        "role": 1,
        "age": 34
    },
    {
        "id": 2,
        "name": "Steve",
        "role": 2,
        "age": 32
    }
]
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Bryan
            [role] => 1
            [age] => 34
        )

    [1] => Array
        (
            [id] => 2
            [name] => Steve
            [role] => 2
            [age] => 32
        )

)
Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => Bryan
            [role] => 1
            [age] => 34
        )

    [1] => stdClass Object
        (
            [id] => 2
            [name] => Steve
            [role] => 2
            [age] => 32
        )

)
Array
(
    [0] => Bryan
    [1] => Steve
)
Bryan

3.3 Inserting data (SQL INSERT INTO)

Use the Database::insert() or Table::insert() method to insert data in a table.

In this section

3.3.1 Insert class overview

Use the Database::insert($tableName) method to update data in a given a table. You must specify the table name. If you already have an instance of Table, you can also use the Table::insert() method. Those methods return a new Query\insert instance you can configure (see methods below) and execute with the Insert::execute() method.

The Insert::execute returns true if the query runs without any error, otherwise false. If method return false, you may want to check if an error occured. See Handle query errors

The Insert class contains the following methods:

Function Arguments Return Description
insert::prepare($columns) string(s) $columns: the column name(s) $this Prepare an insert query with given column name(s)
insert::setValue($column, $value) string $columns: the column names,
mixed $value: the column value
$this Define a column value.
insert::values($parameters) array $parameters: indexed array of column names / values $this Define the column values.
Insert::execute() - bool Execute the INSERT query and returns true if the run without error, otherwise false.
Insert::rowCount() - int Get the number of affected rows by the last Insert, Update, or Delete query (inherited from Query\QueryBuilder). (see Get affected rows).

How to use:

Like the update query, there is two ways to use the method : the direct way, and using prepared query.

  1. The prepared query way consists in:
    1. Prepare the command using Insert::prepare() method.
    2. Set parameters values using Insert::setValue() method for each column, or using the Insert::setValues() method.
    3. Execute the query using Insert::execute() method.
  2. The direct way consists in:
    1. Pass the parameters to insert() method.
    2. Execute the query using Insert::execute() method.

    Both method produce the same result. When using the direct way, the program automatically prepares the query and bind values. You may use the prepared query way when you need to reuse the query.

3.3.2 Basic usage (direct way)

The direct insert way consists in passing columns values and execute the query. You pass values using Insert::values() or Insert::setValue() to define the column values, and call Insert::execute() to execute the query. For a single insert call, no need to explicitly call the Insert::prepare() method (the method will be internally called anyway).

Synopsys:
Database->insert('tablename')->values(...)->execute()
Or
Database->table('tablename')->insert(...)->execute()

The following example show how to use the Database::insert() method:

$database->insert('user')
         ->values(array('id'=>1, 'name'=> 'Bryan', 'age'=> 42))
         ->execute();

The following example show how to use the Table::insert() method:

$database->table('user')
         ->insert(array('id'=>1, 'name'=> 'Bryan', 'age'=> 42))
         ->execute();

3.3.3 Prepared insert query

If you want to reuse the same insert query more than one time, you can use the prepared query way. Use first Insert::prepare() method to prepare the query with desired columns. The Insert::prepare() method takes one argument for each column. Then use Insert::values() or Insert::setValue() to define the column values, and call Insert::execute() to execute the query.

Synopsys:
$query = Database->insert('tablename')->prepare( ... );
$query->values( ... )->execute()
Or
$query = Database->table('tablename')->insert( ... )->prepare( ... );
$query->values( ... )->execute()

The following example shows how to use the insert::prepare(), insert::setValue() and insert::values() method:

// prepare query with desired columns
$query = $database->insert('user')->prepare('id','name','age');

// set values and execute query. Here we use values() method
// so we can set all values in one statement
$query->values(array('id' => 4, 'name' => 'Chris', 'age' => 38))
      ->execute();

// set other values and execute query.  Here we use setValue() method
// for each column
$query->setValue('id', 2);
$query->setValue('name', 'Steve');
$query->setValue('age', 32);
$query->execute();

// set other values and execute query (same code like before writted on one call)
$query->setValue('id', 3)
      ->setValue('name', 'John')
      ->setValue('age', 18)
      ->execute();

3.4.6 Get affected rows (rowCount)

Use the Insert::rowCount() method to get the number of affected row by the last insert query.

Example:

//define the query
$query =  $database->insert('user')->values(array('id'=>1, 'name'=> 'Bryan', 'age'=> 42));
// execute
$query->execute();
// get rowCount
$rowCount = $query->rowCount();

3.4 Update data (SQL UPDATE)

Use the Database::update() or Table::update() method to update data in a given table.

In this section

3.4.1 Update overview

Use the Database::update($tableName) method to update data in a given a table. You must specify the table name. If you already have an instance of Table, you can also use the Table::update() method. Those methods return a new Query\Update instance you can configure (see Parameter methods below) and execute with the Update::execute() method.

The Update::execute returns true if the query runs without any error, otherwise false. If method return false, you may want to check if an error occured. See Handle query errors


Synopsys:
Database->update('tablename')->values(...)->[(Parameter_Method)]->execute()
Or
Database->table('tablename')->update(...)->[(Parameter_Method)]->execute()

The Update class contains the following methods:

Function Arguments Return Description
Update::setValue($column, $value) string $column: the column name
mixed $value: the column value
$this Define a value for a column
Update::values($column, $value) array $parameters: the keys/values parameters $this Define the comumns values
Update::increment($column [, $value]) string $column: the column name
int $value: the increment value (optional)
$this Increment a column value with given increment value (default is 1).
Update::decrement($column [, $value]) string $column: the column name
int $value: the increment value (optional)
$this Decrement a column value with given decrement value (default is 1).
Update::where() - Query\Where Returns the Where instance to define the WHERE conditions. (see Filter query)
Update::whereEqual($column, $value) string $column: the column name,
mixed $value: the column value
$this Add a 'WHERE column = value' condition. This method is an alias for Update::where()->equal($column, $value) (see Filter query).
Update::execute() - bool Execute the query. This methods returns true if the query runs without any error, otherwise false.
Update::rowCount() - int Get the number of affected rows by the last Insert, Update, or Delete query (inherited from Query\QueryBuilder). (see Get affected rows).

3.4.2 Basic usage

You need to pass columns values and execute the query. You pass values using Update::values() or Update::setValue() to define the column values, and call Update::execute() to execute the query. If you are using the Table class, you can pass values to the Table::update() method.

The following examples show how to use the Database::update() method:

$database->update('user')
         ->values(array('name'=> 'toto', 'email' => 'changedmail@example.com'))
         ->whereEqual('id', 222);

//or
$database->update('user')
         ->setValue('name'  , 'toto')
         ->setValue('email' ,'changedmail@example.com')
         ->whereEqual('id', 222);

The following examples show how to use the Table::update() method:

$database->table('user')
         ->update(array('name'=> 'toto', 'email' => 'changedmail@example.com'))
         ->whereEqual('id', 222)
         ->execute();
//or
$database->table('user')
         ->update()
         ->values(array('name'=> 'toto', 'email' => 'changedmail@example.com'))
         ->whereEqual('id', 222)
         ->execute();

//or
$database->table('user')
         ->update()
         ->setValue('name'  , 'toto')
         ->setValue('email' ,'changedmail@example.com')
         ->whereEqual('id', 222)
         ->execute();

3.4.3 Increment/decrement column

Use the Update::increment($column [, $value]) or Update::decrement($column [, $value]) to increment or decrement a column value. You must specify the column name. Default value for increment is 1. This method returns $this.

Example, increment column age without define the increment value (default is 1) method

$database->update('user')
         ->increment('age')
         ->whereEqual('id', 1)
         ->execute();

Example, decrement the age column with 10. method

$database->update('user')
         ->decrement('age', 10)
         ->whereEqual('id', 1)
         ->execute();

3.4.5 Filter data

Use the Update::where() method to define the WHERE sql filters. See Filter query


3.4.5 Get affected rows (rowCount)

Use the Update::rowCount() method to get the number of affected row by the last update query.

Example:

//define the query
$query = $database->update('user')
                  ->increment('age')
                  ->whereEqual('id', 222);
// execute
$query->execute();

// get rowCount
$rowCount = $query->rowCount();

3.5. Delete data (SQL DELETE)

Use the Database::delete() or Table::delete() method to delete data in a given table.

In this section

3.5.1 Overview

Use the Database::delete($tableName) method to delete data in an existing table. You must specify the current table name. If you already have an instance of Table, you can also use the Table::delete() method that does not take any parameter. Those method return a new instance of Query\Delete you can configure (see Parameter methods below) and execute with the Delete::execute() method.

The Delete::execute returns true if the query runs without any error, otherwise false. If method return false, you may want to check if an error occured. See Handle query errors


Synopsys:
Database->delete('tableName')->[parameter_methods]->execute()
Or
Database->table('tableName')->delete()->[parameter_methods]->execute()

The Delete class contains the following methods:

Method Arguments Return Description
Delete::whereEqual($column, $value) string $column: the column name,
mixed $value: the column value
$this Add a 'WHERE column = value' condition. This method is an alias for Delete::where()->equal($column, $value) (see Filter query).
Delete::execute() - bool Execute the DELETE query and returns true if the run without error, otherwise false (inherited from Query\QueryBuilder).
Delete::rowCount() - int Get the number of affected rows by the last Insert, Update, or Delete query (inherited from Query\QueryBuilder). (see Get affected rows).
Delete::where() - Query\Where Returns the Where instance to define the WHERE conditions (see Filter query).

3.5.2 Basic usage

When to parameters are given, the query deletes all data in the given table

The following example show how to delete all data in table user.

$database->delete('user')
         ->execute();
//or
$database->table('user')
         ->delete()
         ->execute();

3.5.3 Filter query (SQL WHERE)

Use the Delete::where() method to define the WHERE sql filters. You can also use the Delete::whereEqual($column, $value) method that is an alias for Delete::where()->equal($column, $value) method (returns $this). See Filter query.

The following example show how to delete user with id of 2 in table user:

$database->delete('user')
         ->whereEqual('id', 2)
         ->execute();
//or
$database->table('user')
         ->delete()
         ->whereEqual('id', 2)
         ->execute();

//or
$database->table('user')
         ->delete()
         ->where()->equal(('id', 2)
         ->execute();

3.5.4 Get affected rows (rowCount)

Use the Delete::rowCount() method to get the number of affected row by the last delete query.

Example:

//define the query
$query =  $database->delete('user');
// execute
$query->execute();
// get rowCount
$rowCount = $query->rowCount();

3.6. Filter query (SQL WHERE)


Select, Update and Delete class provide a method where() that returns a Where object instance you can configure with filter methods.

In this section

3.6.1 Where class overview

Synopsis
QueryBuilder->where()->[filter method]->[executive_method]
Or
QueryBuilder->where()->beginOr()->[filter_method]->[filter_method]->closeOr()->[executive_method]

3.6.2 Operators functions [filter methods]

Each filter method inside the Where object returns the QueryBuilder parent or the Where object itself (see Multiples filters). The filters method functions are listed below:

Function Logic Operator Code Operator Arguments
Where::equal($column, $value) Equal to = string $column: The name of column,
mixed $value: The condition value
Where::notEqual($column, $value) Not Equal to != string $column: The name of column,
mixed $value: The condition value
Where::greater($column, $value) Greater than > string $column: The name of column,
mixed $value: The condition value
Where::greaterEqual($column, $value) Greater than or equal to >= string $column: The name of column,
mixed $value: The condition value
Where::lower($column, $value) Lower than < string $column: The name of column,
mixed $value: The condition value
Where::lowerEqual($column, $value) Lower than or equal to <= string $column: The name of column,
mixed $value: The condition value
Where::null($column) Is null IS NULL string $column: The name of column
Where::notNull($column) Is not null IS NOT NULL string $column: The name of column
Where::like($column, $pattern) Is like 'expression' LIKE string $column: The name of column,
string $pattern: The condition pattern
Where::notLike($column, $pattern) Is not like 'expression' NOT LIKE string $column: The name of column,
string $pattern: The condition pattern
Where::in($column, $values) In 'values' IN string $column: The name of column,
array $values: The 'IN' values
Where::notIn($column, $values) Not in 'values' NOT IN string $column: The name of column,
array $values: The 'NOT IN' values

3.6.3 Multiple filters

By default, all conditions are cumulative and grouped with the AND prefix. For example, if you set multiple filters like this:

QueryBuilder->where->[filter_method1]->where->[filter_method2]->where->[filter_method3]->[...]

Then the generated SQL query will look like this :

[...] WHERE [filter1] AND [filter2] AND [filter3] [...]

If you need more complex query and group filters with AND or OR operator, you must use the special functions beginOr() / closeOr() or beginAnd() / closeAnd() wich allow you to group some filters with the desired operator.

By default, [Filters methods] inside the Where object return the QueryBuilder parent (ie: Select, Update, Delete...). When an AND or OR group is open, then the [Filters methods] will return the Where object itself, to allow you adding more filters to the group.

QueryBuilder->where()->beginOr()->[filter_method]->[filter_method]->closeOr()->execute()

For example, the rule: [(age < 17) OR (age >= 37)] AND (id > 4) will be writed like this :

QueryBuilder->where()
           ->beginOr()
                ->lower('age', 17)
                ->greaterEqual('age', 37)
           ->closeOr()
           ->where()->greater('id', 4)
           ->[...]

Each group open with Where::beginOr() or Where::beginAnd() must be closed with Where::closeOr() or Where::closeAnd(). No closed groups will produce invalid SQL query and generate an error.

3.6.4 Full sample

$database = Sqlite\SqliteDatabase::createMemoryInstance(); 
$table = $database->table('user');
$table->create()
    ->column('id',   'int',         'pk')
    ->column('name', 'varchar(50)', 'NULL')
    ->column('age',  'int',         'NULL')
    ->execute(); 

$table->insert(array('id'=>1,'name'=> 'Bryan', 'age'=> 34))->execute();
$table->insert(array('id'=>2,'name'=> 'Steve', 'age'=> 32))->execute();
$table->insert(array('id'=>3,'name'=> 'John',  'age'=> 18))->execute();
$table->insert(array('id'=>4,'name'=> 'Chris', 'age'=> 38))->execute();
$table->insert(array('id'=>5,'name'=> 'Jane',  'age'=> 16))->execute();

echo 'id EQUAL 2: '.PHP_EOL;
echo $table->select()->where()->equal('id', 2)->getAll('json').PHP_EOL;

echo 'name EQUAL Steve'.PHP_EOL;
echo $table->select()->where()->equal('name', 'Steve')->getAll('json').PHP_EOL;

echo 'name EQUAL Steve AND age EQUAL 32'.PHP_EOL;
echo $table->select()->where()->equal('name', 'Steve')->where()->equal('age', 32)->getAll('json').PHP_EOL;

echo 'name NOT EQUAL Bryan LIMIT 1'.PHP_EOL;
echo $table->select()->where()->notEqual('name', 'Bryan')->getOne('json').PHP_EOL;

echo 'age INF 18'.PHP_EOL;
echo $table->select()->where()->lower('age', 18)->getAll('json').PHP_EOL;

echo 'age SUP 35'.PHP_EOL;
echo $table->select()->where()->greater('age', 35)->getAll('json').PHP_EOL;

echo 'age SUP OR EQUAL 38 '.PHP_EOL;
echo $table->select()->where()->greaterEqual('age', 38)->getAll('json').PHP_EOL;

echo 'age INF OR EQUAL 16 '.PHP_EOL;
echo $table->select()->where()->lowerEqual('age', 16)->getAll('json').PHP_EOL;

echo 'age INF 17 OR age SUP 37'.PHP_EOL;
echo $table->select()->where()->beginOr()->lower('age', 17)->greater('age', 37)->closeOr()->getAll('json').PHP_EOL;

echo '(age INF 17 OR age SUP 37) AND id SUP  4'.PHP_EOL;
echo $table->select()->where()->beginOr()->lower('age', 17)->greater('age', 37)->closeOr()
                                 ->where()->greater('id', 4)
                                 ->getAll('json').PHP_EOL;
echo 'name like Chr%'.PHP_EOL;
echo $table->select('name')->where()->like('name','Chr%')
                                 ->getAll('json').PHP_EOL;

echo 'id, name with (id IN {2,3,4})'.PHP_EOL;
echo $table->select('id', 'name')->where()->in('id', array(2,3,4))->getAll('json').PHP_EOL;

// 'id, name with (id NOT IN {2,3,4})'.PHP_EOL;
echo $table->select('id', 'name')->where()->notIn('id', array(2,3,4))->getAll('json');
Output
id EQUAL 2: 
[{"id":2,"name":"Steve","age":32}]
name EQUAL Steve
[{"id":2,"name":"Steve","age":32}]
name EQUAL Steve AND age EQUAL 32
[{"id":2,"name":"Steve","age":32}]
name NOT EQUAL Bryan LIMIT 1
[{"id":2,"name":"Steve","age":32}]
age INF 18
[{"id":5,"name":"Jane","age":16}]
age SUP 35
[{"id":4,"name":"Chris","age":38}]
age SUP OR EQUAL 38 
[{"id":4,"name":"Chris","age":38}]
age INF OR EQUAL 16 
[{"id":5,"name":"Jane","age":16}]
age INF 17 OR age SUP 37
[{"id":4,"name":"Chris","age":38},{"id":5,"name":"Jane","age":16}]
(age INF 17 OR age SUP 37) AND id SUP  4
[{"id":5,"name":"Jane","age":16}]
name like Chr%
[{"name":"Chris"}]
id, name with (id IN {2,3,4})
[{"id":2,"name":"Steve"},{"id":3,"name":"John"},{"id":4,"name":"Chris"}]
[{"id":1,"name":"Bryan"},{"id":5,"name":"Jane"}]

3.7 Test if a table exists

Use the Database::tableExists($name) method to test if a table exists in current database. You must specify the table name. If you already have an instance of Table, you can also use the Table::exists() method that does not take any parameter. Those methods return true if the table exists, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors

For example, test if a table named 'user' exists in database:

$result = $db->tableExists('NotExistingTable');
Or
$result = $db->table('NotExistingTable')->exists();

3.8 Rename a table

Use the Database::renameTable($name, $newName) method to rename an existing table. You must specify the current name and the new name for the table. If you already have an instance of Table, you can also use the Table::rename($newName) method that does take only the new name as parameter. Those methods return true if the table has been renamed, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors

For example, rename the table named 'user' to 'new_name' in current database:

$db->renameTable('user', 'new_name');
Or
$db->table('user')->rename('new_name');

3.9 Create a table (SQL CREATE TABLE)

Use the Database::createTable($name) or Table::create() method to create a table. Those method return a new Query\CreateTable instance you can configure and execute using CreateTable::execute().

In this section

2.8.1 CreateTable class overview

Use the Database::createTable($tableName) method to create a table. You must specify the table name. If you already have an instance of Table, you can also use the Table::create() method. Those methods return a new Query\CreateTable instance you can configure (see methods below) and execute with the CreateTable::execute() method.

The CreateTable::execute returns true if the query runs without any error, otherwise false. If method return false, you may want to check if an error occured. See Handle query errors

Synopsys:
Database->createTable('tableName')->[parameter_methods]->execute()
Or
Database->table('tableName')->create()->[parameter_methods]->execute()

The CreateTable class contains the following methods:

Method Arguments Return Description
column($columnDefinition) array $columnDefinition: The column definition $this Add a column in the columns definitions. See Columns defintion
fk($fkName, $srcColumn, $refTable, $refColumn [, $onUpdate = 'CASCADE', $onDelete = 'RESTRICT']) $this Add a foreign key contraint. See Foreign key constraint
ifNotExists() $this Define the usage of IF NOT EXIST keyword to true, to create the table only when it doesn't exist.
execute() bool Execute the query. This method returns TRUE if the table has been created, otherwise false.

3.9.2 Add columns

Use the CreateTable::column($columnName, $columnType [, ... ]) method to add a column definition to the list. The CreateTable::column() method takes at least two arguments representing column name and column type. Others arguments are optional and can be added in any order. This method returns $this.

Most optional parameters are case insentive keywords. Some of theme have multiple synthax. The supported optional parameters are listed below:

Parameter Description Comments
string 'PK' or
'PRIMARY KEY' or
'PRIMARY_KEY'
Primary Key column. Case insensitive (see Define primary key constraints)
string 'AI' or
'AUTO INCREMENT' or
'AUTO_INCREMENT'
Auto increment column. Case insensitive (see Define auto increment column)
string 'NULL' Nullable column Case insensitive
If NULL and PK attributes are set, then NULL attribute is ignored. (see Define null / not null constraints)
string 'NOT NULL' Not null column Case insensitive (see Define null / not null constraints)
string 'DEFAULT' Use a default value for the column. Case insensitive. When using a default value for the column, the following argument must be the default value. (see Define default values)
mixed $defaultValue The default value for the column The default value must follow the 'DEFAULT' argument. The default value could be something you define or a predefined value or function
Supported predefined values and functions are:
- string 'NULL'
- string 'CURRENT_TIMESTAMP'
(see Define default values).

The following example shows how to add a column of type 'varchar(50)' named 'user_name':

$database->createTable('user')
         ->column('user_name', 'varchar(50)')
         ->execute();

3.9.3 Define column type

Use the second argument of the CreateTable::column(string $columnName, string $columnType [, ... ]) method to define the column type. Except for the 'AUTO INCREMENT' attribut (see Define auto increment column) , type there is no conversion. Make sure you use a type that works with selected driver.

The following example shows how to create a table with some universal types:

$database->createTable('user')
         ->column('id',     'int',          'PK')
         ->column('name',   'varchar(50)',  'NOT NULL')
         ->column('email',  'varchar(255)', 'NOT NULL')
         ->execute();

3.9.4 Define null / not null contraints

To allow or not null column values , pass the keyword 'NULL' or 'NOT NULL' to the CreateTable::column() method. If none of those keyworks are used, the 'NOT NULL' is currently used (may change in futur release).

The 'NULL' attribute is ignored if 'PRIMARY KEY' attribute is set.

The following example shows how to create a table with a primary key column 'id', a not null column 'name' and an optional column 'option':


$database->createTable('user')
         ->column('id',     'int',         'PK')
         ->column('name',   'varchar(50)', 'NOT NULL')
         ->column('option', 'varchar(50)', 'NULL')
         ->execute();

3.9.5 Define primary key contraints

To define the primary key, pass the keyword 'PRIMARY KEY', 'PRIMARY_KEY' or 'PK' to the CreateTable::column() method.

The following example shows how to add a primary key column of type 'int' named 'id':

$database->createTable('user')
         ->column('id', 'int', 'pk')
         ->execute();

3.9.6 Define unique contraint

To define a column unique, pass the keyword 'UNIQUE' to the CreateTable::column() method.

The following example shows how to create a table with an unique column 'email':

$database->createTable('user')
         ->column('id',     'int',          'PK')
         ->column('name',   'varchar(50)',  'NOT NULL')
         ->column('email',  'varchar(255)', 'NOT NULL' , 'UNIQUE')
         ->execute();

3.9.7 Define default values

To define default values, pass the keyword 'DEFAULT' to the CreateTable::column() method. The default value must follow the 'DEFAULT' keyword in a separed argument. The value could be a predefined string like 'CURRENT_TIMESTAMP' or 'NULL'.

The following example shows how to create a table with a column 'option' with a default value:

$query = $database->createTable('user');
$query->column('id',       'int',         'PK');

// default null is generallay not required for nullable columns:
$query->column('option1',  'varchar(10)', 'NULL',     'DEFAULT', 'NULL');   // similar
$query->column('option2',  'varchar(10)', 'NULL');                          // similar

// int value
$query->column('option3',  'int',         'NOT NULL', 'DEFAULT',  555);

// string value
$query->column('name',     'varchar(50)', 'NOT NULL', 'DEFAULT', 'toto');

// current timestamp
$query->column('created',  'timestamp',   'NOT NULL', 'DEFAULT', 'CURRENT_TIMESTAMP');
$query->execute();

3.9.8 Define auto increment column

To define the primary key, pass the keyword 'AUTO_INCREMENT', 'AUTO INCREMENT' or 'AI' to the CreateTable::column() method. Please note the type will be overided with some driver. For example, the Postres equivalent to Mysql 'int' AUTO_INCREMENT is a type ('SERIAL'). The Sqlite driver use 'PRIMARY KEY INTEGER' column type without AUTO INCREMENT keyword for best performances. Finally for most drivers like Sqlite, the AUTO INCREMENT feature is only available for the PRIMARY KEY.

The following example shows how to add a primary key column of type 'int' named 'id' with auto increment:

$database->createTable('user')
         ->column('id', 'int', 'pk', 'ai')
         ->execute();

3.9.9 Define foreign key constraints

Use the CreateTable::fk($fkName, $srcColumn, $refTable, $refColumn [, $onUpdate = 'CASCADE', $onDelete = 'RESTRICT']) method to define a foreign key. This method returns $this.

The following example show how to create two tables 'customer' and 'order' with a foreign key.

$database->createTable('customer')
          ->column('customerId'     , 'int'         , 'PK')
          ->column('customerName'   , 'varchar(50)' , 'NOT NULL')
          ->execute(); 

$database->createTable('order')
         ->column('orderId'         , 'int'         , 'PK')
         ->column('orderCustomerId' , 'int'         , 'NOT NULL')
         ->column('orderDate'       , 'varchar(10)' , 'NOT NULL')
          // add foreign key
         ->fk('fk_order_customer'   , 'orderCustomerId', 'customer', 'customerId')
         ->execute();

3.9.10 Create the table if not exists

To create a table only when it does not exist, call the CreateTable::ifNotExists() method before executing the query. This method returns $this.

The following example shows how to create a table 'user only if it does not exist.

$database->createTable('user')
         ->ifNotExists()
         ->column('id',     'int',         'PK')
         ->column('name',   'varchar(50)', 'NOT NULL')
         ->column('option', 'varchar(50)', 'NULL')
         ->execute();

3.10 Drop a table (SQL DROP TABLE)

Use the Database::dropTable($name) method to drop a table in current database You must specify the table name. If you already have an instance of Table, you can also use the Table::drop() method that does not take any parameter. Those methods return true if the table has been dropped, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors.

The followwing example shows how to drop a table named 'user'in current database:

$database->dropTable('user');
Or
$database->table('user')->drop();

4. Server query


Use the Server or derived class to peform query on a database server.

In this section:

4.1 Server class overview

The Server class inherits from Datasource and contains the following methods:

Method Parameters Return Description
closeConnection() void Close the current PDO connection (inherited from Datasource). See close a connection
databaseExists($databaseName) $databaseName: string, the name of the database bool Check if a database exists. Returns true if the database exists, otherwise false. See Check if a database exists
createDatabase($databaseName) $databaseName: string, the name of the database bool Create a database. Returns true if the database has beed created, otherwise false. See Create a database
createDatabaseAndUser($databaseName) $databaseName: string, the name of the database bool Create a database. Returns true if the database has beed created, otherwise false. See Create a database
dropDatabase($name [, $ifExists]) string $name: the database name
bool $ifExists: Set whether the database must be deleted only when exists. Default is false
bool Drop a database in the server. Returns true if the database has been deleted (or does not exists when $ifExists is set to true), otherwise false. See Drop a database.
dropUser($uesrName [, $ifExists]) string $userName: the user name
bool $ifExists: Set whether the user must be deleted only when exists. Default is false
bool Drop a user in the server. Returns true if the user has been deleted (or does not exists when $ifExists is set to true), otherwise false. See Drop a user.
createUser($username, $password) XXX.
getConnection() PDO Get the current PDO connection (inherited from Datasource).
getDatabases() - array Get the list of active databases in the server. See Get databases list
getDriver() Driver\DriverBase Get the driver for current connection (inherited from Datasource).
getDriverName() string Get the name of the driver for current connection (inherited from Datasource).
getUsers() - array Get the list of active user in the server. See Get users list
getVersion() string Get the patabase library version like "0.0.0" (inherited from Datasource).
userExists($userName) $userName: string, the user name bool Check if a user exists. Returns true if the given user exists, otherwise false. See Check if a user exists

4.2 Check if a database exists

Use the Server::databaseExists($databaseName) method to check if a database exists in the current server. You must specify the database name. This method returns true if the given database exists, otherwise false.

The following example show how to test if a database named 'mydatabase' exists on the current server.

$server = new Kristuff\Patabase\Server([...]);
$exists = $server->databaseExists('mydatabase');

4.3 Get databases list

Use the Server->getDatabases() method to get the list of databases in the current server. This method returns an array with databases names.

The following example shows how to list the databases in a mysql server:

$settings = array('driver'=> 'mysql', 
    'hostname'=> 'localhost', 
    'username'=> 'YOUR_USER_NAME',
    'password' => 'YOUR_PASSWORD');
$mysql = new Kristuff\Patabase\Server($settings);
$databases = $mysql->getDatabases();

// print results
print_r(json_encode($databases)); 

// Output
["information_schema","Database1,"Database2","Database3", ...]

4.4 Create a database

Use the Server::createDatabase($databaseName [, $owner = NULL]) method to create a new database on server. You must specify the database name. The $owner argument can be specified for Postgres (default is null).The $owner argument will be ignored on all ohers drivers.

If you want to create a user, a database and grant permission on database to that user, you can also use Server::createDatabaseAndUser($databaseName, $userName, $password), the method internally call the Server::createUser($userName, $password), Server::createDatabase($databaseName), and Server::grantUser(databaseName, $userName).

Those methods return true if the database has been created, otherwise false. If methods return false, you may want to check if an error occured. See Handle query errors.

The following example show how to create a database named 'testdb' in a mysql server:

$settings = array('driver'=> 'mysql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createDatabase('testdb');

The following example show how to create a database named 'testdb' with owner'toto' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createDatabase('testdb', 'toto');

The following example show how to create a database named 'testdb' and a user named 'toto' in a mysql server:

$settings = array('driver'=> 'mysql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createDatabaseAndUser('testdb', 'toto', 'totopass');

In Sqlite, there is no server concept, create a Database with wanted filename to create a database. if the specified file doesn't exists, a file is automatically created in case of successfull connection on Sqlite.

4.5 Drop a database

Use the Server::dropDatabase($databaseName [, $ifNotExists = false]) method to drop a database. You must specify the database name. When $ifNotExists is set to false (Its default value), this methods returns true if the database has been dropped, otherwise false. When $ifNotExists is set to true, database is dropped only when exists and method will return true if the query runs without error (database dropped or does not exists), otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to drop a database named 'testdb' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);

// suppose a database 'testdb' that exists, can be dropped and you have permission to drop it

$drop1      = $pgsql->dropDatabase('testdb');           // Drop the database 'testdb' (shoud be OK)
$drop1error = $pgsql->hasError();                       // check for errors after drop 

$drop2      = $pgsql->dropDatabase('testdb', TRUE);     // Drop the database 'testdb' ONLY when exists (shoud be OK)
$drop2error = $pgsql->hasError();                       // check for errors after drop 

$drop3      = $pgsql->dropDatabase('testdb');           // Drop the database 'testdb' (shoud be wrong, it still not exists...))
$drop3error = $pgsql->hasError();                       // check for errors after drop

// the code will produce the following results
// $drop1 => true,  $drop1error  => false
// $drop2 => true,  $drop2error  => false
// $drop3 => false, $drop3error  => true

4.6 Check if a database exists

Use the Server::userExists($userName) methodTo check if a database exists in the current server. You must specify the user name. This method returns true if the given user exists, otherwise false.


The following example show how to test if a user named 'toto' exists on the current server.

$server = new Kristuff\Patabase\Server([...]);
$exists = $server->userExists('toto');

4.7 Get users list

Use the Server::getUsers() method to get the list of users in the current server. This method returns an array with users names.

The following example shows how to list the users in a mysql server:

$settings = array('driver'=> 'mysql', 
    'hostname'=> 'localhost', 
    'username'=> 'YOUR_USER_NAME',
    'password' => 'YOUR_PASSWORD');
$mysql = new Kristuff\Patabase\Server($settings);
$databases = $mysql->getDatabases();

// print results
print_r(json_encode($databases)); 

// Output
["xxx","xxx,"xxx","xxx", ...]

4.8 Create a user


$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->createUser('toto');

4.9 Grant a user

Use the Server::grantUser($databaseName, $userName) method to grant user permission on a database. You must specify the database name and user name. This methods returns true if permission have been granted, otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to grant permission to a user named 'toto' on database named 'mydatabase' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'XXXX', 'password' => 'XXXX');
$pgsql = new Kristuff\Patabase\Server($settings);
$pgsql->grantUser('mydabase', 'toto);

4.10 Drop a user

Use the Server::dropUser($userName [, $ifNotExists = False]) method to drop a user. You must specify the user name. When $ifNotExists is set to False (Its default value), this methods returns true if the user has been dropped, otherwise false. When $ifNotExists is set to true, user is dropped only when exists and method will return true if the query runs without error (user dropped or does not exists), otherwise false. If method returns false, you may want to check if an error occured. See Handle query errors.

The following example show how to drop a user named 'toto' in a postgres server:

$settings = array('driver'=> 'pgsql', 'hostname'=> 'localhost', 'username'=> 'postgres', 'password' => 'XXXXXXXXXXXX');
$pgsql = new Kristuff\Patabase\Server($settings);

// suppose a user 'toto' that exists, can be dropped and 
// you have permission to drop it

$drop1 =        $pgsql->dropUser('toto');       // Drop the user 'toto'(should be OK)
$drop1error =   $pgsql->hasError();             // check for errors after drop 

$drop2 =        $pgsql->dropuser('toto', true); // Drop the user 'toto' only when exists (should be OK)
$drop2error =   $pgsql->hasError();             // check for errors after drop

$drop3 =        $pgsql->dropUser('toto');       // Drop the user 'toto' (should be wrong, it still not exists...)
$drop3error =   $pgsql->hasError();             // check for errors after drop

// the code will produce the following results
// $drop1 => true,  $drop1error  => false
// $drop2 => true,  $drop2error  => false
// $drop3 => false, $drop3error  => true

5. Handle query errors


Patabase may thow PatabaseException when instanciating a Datasource object, see Connection overview. Then, the manner in which Patabase handles exceptions differs depending in which context the queries are made.

In this section:

5.1 Handle errors without transaction

Without transaction, Patabase does not throw any error when a query failed. The query method will return false or an empty result in case of Select query:

  • When a method from Datasource failed or has no results, you can check the presence of errors with Datasource::hasError() (return bool) then check the error with Datasource::errorCode() and Datasource::errorMessage() methods.

    Errors are reseted before each query. You need to check error after each query you made.

    The following example show a way to handle error in a Database::renameTable() method:

    // define a wrong query
    $renamed = $database->renameTable('wrongTable', 'newname');
    
    // if the query failed, check error in Database
    if (!$renamed && $database->hasError()) {
        echo $database->errorCode();
        echo $database->errorMessage();
    }

    The following example show a way to handle error in a Server::createDatabase() method.

    // define a wrong query
    $created = $server->createDatabase('AlreadyExistingDatabase');
    
    // if the query failed, check error in Server
    if (!$created && $server->hasError()) {
        echo $server->errorCode();
        echo $server->errorMessage();
    }
  • When a method from QueryBuilder (base class for all builded query) failed or has no results, you can check the presence of errors with QueryBuilder::hasError() then check the error with QueryBuilder::errorCode() and QueryBuilder::errorMessage() methods.

    Errors are reseted before each query execution. You need to check error after each query you execute.

    The following example show a way to handle error in a Insert query:

    // define a wrong query
    $insert = $database->insert('wrongTable')->setValue(['wrongcolumn', 1]);
    
    // get the result of execute()
    $inserted = $insert->execute();
    
    // if the query failed, check error in Query
    if (!$inserted && $insert->hasError()) {
        echo $insert->errorCode();
        echo $insert->errorMessage();
    }

5.2 Handle errors in transaction

When using transaction, Patabase may throw SqlException. That way, developpers do not need to check the result of each query inside a transaction block. But that mean the transaction code must be placed in a try{} catch{} block. You can use the SqlException::getCode() and SqlException::getMessage() methods to check the error.

The following example show a way to handle error in trsaction block:

try {
    // start transaction
    $database->beginTransaction();
    (...)
    // commit changes
    $database->commit();
}
catch(SqlException $e) {
    // rollback changes if an error occured
    $database->rollback();

    // you may also want to check error with: 
    //  $e->getCode() and $e->getMessage()
}

See also Transactions