Database - ApinePHP/legacy-framework GitHub Wiki

The Database utility eases off management of connections to database and provides minimal interfaces to simplify the interaction with the database. It is the core of modules like the Session Manager or the Entity Model. The utility will keep only one connection to the system database active at any time.

The Database utility is build for MySQL using the PDO extention. It may work with any SQL language compatible with MySQL although we recommend you to use MySQL only. PDO must be installed with the required drivers and being active for the utility to work. PDO is normaly installed and active by default as of PHP 5.4.

To use the Database utility, you must import the Database class from the Core module.

use Apine\Core\Database;

Basic Usage

The database includes simple to use methods specialised for the four most common request type : SELECT, INSERT, UPDATE and DELETE.

Fetch Data

The select method returns an array containing the rows matching the request.

$database = new Database();
$result_select = $database->select(
    'SELECT * from apine_users WHERE `register_date` > "2015-08"'
);

Insert Data

The insert method requires the table name and an associative array of each field and their values. The insertion returns the id of the newly inserted row.

$database = new Database();
$result_insert = $database->insert("apine_users", array(
    "username" => "Youmy001",
    "password" => \Apine\Core\Encryption::hash_password("raw_password"),
    "email" => "[email protected]"
));

Update Data

The update method requires three arguments : a table name, a condition associative array and an associative array with new values.

$database = new Database();
$result_update = $database->update("apine_users", array(
    "email" => "[email protected]"
), array("id" => 5));

Delete Data

The row deletion requires a table name and a condition associative array. It returns true on success.

$database = new Database();
$result_delete = $database->delete("apine_users", array(
    "id" => 5
));

Generic calls

It is also possible to execute any other requests using the exec() method. It will return the results without any validation nor formating. This is the equivalent of calling the exec() of PDO. The limits of what you can do resides in the permissions of the active SQL user.

Example

$database = new Database();
$result = $database->exec('ALTER TABLE `article` ADD PRIMARY KEY (`id`), ADD KEY `author` (`author`);');

Prepared requests

The database utility also allows to use the prepared request feature of PDO. The utility will save you the need of keeping track open statements for the current instance of the Database class. The utility will automatically store any statements within itself and let you execute them later. You have the oportunity to create, execute or delete prepared statements.

The statements are managed with a stack. This means the execution and the deletion will be performed on the last statement added. This behavior can be skipped when using the numeric token returned upon statement creation.

A statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. This behavior follows the behavior of the PDO prepared statements.

Example

$database = new Database();
$prepare_one = $database->prepare($query_one);
$prepare_two = $database->prepare($query_two);
$prepare_three = $database->prepare($query_three);

$database->execute($params); // Executes the third statement
$database->excecute($params, $prepare_one); // Executes the first statement
$database->close_cursor($prepare_three);

$database->execute($params); // Executes the second statement

Methods

__construct ([string $db_type, string $db_host, string $db_name, string $db_user, string $db_password [, string $db_charset = "utf8"]])

Create an instance of Database representing a new or an existing database connection.

If no parameters are provided, the instance of Database will use the existing connection to the system database. If you want to connect to another database, you will need to provide the parameters identified bellow. They are the same as the configuration for the database :

  • $db_type : Type of PDO connection. Usually mysql, pgsql, sqlite, sqlsrv or odcb;
  • $db_host : Hostname of the server. It can be either a domain name, an IP address or a local host name;
  • $db_name : Database name. Name of the database to connect to;
  • $db_username : Database user;
  • $db_password : Database user's password;
  • $db_charset : (Optional) Connection encoding. We strongly recommend utf8. It is utf8 by default;

If PDO fails to connection to the database, the connection will return an exception.

select (string $query) : array

Fetch table rows from database through the PDO handler using a query string.

The method will return an associative array containing the matching rows. It will throw an exception if the query is invalid.

insert (string $table_name, array $ar_values) : integer

Insert a new table row into the database through the PDO handler.

The first parameter is the name of the table in which to insert the row. The second is an array containing the fields to include in the row and their values.

The method will return the identifier of the newly created row on success. The method will throw an exception if it is impossible to execute the query.

update (string $table_name, array $ar_values, array $ar_conditions)

Update one or many table rows from the database through the PDO handler.

The first parameter is the name of the table in which to modify one or many rows. The second parameter is an array containing the fields to modify in the row and their new values. The thrid parameter is an array containing the fields and the values matching the rows to modify. It is used to build the WHERE instruction of the SQL statement using the equality operator.

The method will throw an exception if it is unable to execute the query.

delete (string $table_name, array $ar_conditions) : boolean

Delete one or many table rows from the database through the PDO handler.

The first parameter is the name of the table in which to modify one or many rows. The second parameter is an array containing the fields and the values matching the rows to modify. It is used to build the WHERE instruction of the SQL statement using the equality operator.

The method will return TRUE on success or FALSE if it did not delete any rows. The method will throw an exception if it is unable to execute the query.

exec (string $query) : mixed

Execute operation onto database through the PDO handler.

The parameter is a valid SQL query string.

The method will return the result of the query. The method will throw an exception if it is unable to execute the query.

last_inserted_id ([string $name = null]) : integer

Return the Id of the last inserted row.

The optional operator is the name of the sequence object from which the ID should be.

The method will return the id of the last inserted row.

quote (string $string [, integer $parameter_type = PDO::PARAM_STR]) : string

Quotes a string for use in a query.

The first parameter is the string to quote following the settings of the database server. The optional parameter provides a data type hint for drivers that have alternate quoting styles. It is set to PDO::PARAM_STR by default.

The method returns the string quoted using the provided data type hint.

prepare (string $query [, array $driver_options = array()]) : integer

Prepare a statement for later execution

The first parameter is a query string that can contain zero or more named (:name) or question mark (?) parameter markers. These are replaced with real values when the statement is executed. The second parameter is an array that can hold one or more key/value pairs to set attributes for the PDO statement object. For more information on driver options, read the page about statement preparation on the PHP manual.

The method returns an numeric token representing the newly created statement. The token give direct access to the statement using the execute() and the close_cursor() methods.

execute (array $input_parameters [, integer $index = null]) : mixed

Execute a previously prepared statement.

The first paramether is an array containing the values replacing the markers in the statement. The optional parameter is a numeric token representing a statement. If a numeric token is not provided, the method will execute the statement at the top of the stack. See the page about statement execution in the PHP manuel for more information on the format of the values replacing the markers.

The method returns the number of rows affected by the statement or the matching row if the query was a SELECT statement. The method will throw an exception if it is unable to execute the statement.

close_cursor ([integer $index = null])

Close a previously prepared PDO statement.

The optional parameter is a numeric token representing a statement. If a numeric token is not provided, the method will close the statement at the top of the stack.