-
Notifications
You must be signed in to change notification settings - Fork 0
Documentation
The best way to install O2DB is to use Composer
composer require o2system/o2db:"dev-master"
There are two ways of configuration to connect to a database:
use O2System\DB;
// Using DSN Connection
$DB = new DB('dbdriver://username:password@hostname/database?charset=utf8&collate=utf8_general_ci');
// Using Config Array
$DB = new DB(array(
'driver' => 'mysql',
'dsn' => '',
'hostname' => 'localhost',
'port' => 3306,
'username' => 'root',
'password' => 'mysql',
'database' => 'example',
'charset' => 'utf8',
'collate' => 'utf8_general_ci',
'table_prefix' => '',
'strict_on' => FALSE,
'encrypt' => FALSE,
'compress' => FALSE,
'buffered' => FALSE,
'persistent' => TRUE,
'trans_enabled' => FALSE,
'options' => array(
PDO::ATTR_CASE => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
),
));$DB will be used as an example variable at this documentation page.
If the database server’s idle timeout is exceeded while you’re doing some heavy PHP lifting (processing an image, for instance), you should consider pinging the server by using the reconnect() method before sending further queries, which can gracefully keep the connection alive or re-establish it.
$DB->reconnect();$DB->disconnect();If you have configured a database prefix and would like to prepend it to a table name for use in a native SQL query for example, then you can use the following:
/*
* @param string $table Database Table Name
*
* @return string Database Table Name with Prefix
*/
$DB->table_prefix('table_name');Return: prefix_table_name
If for any reason you would like to change the prefix programatically without needing to create a new connection, you can use this method:
/*
* @param string $prefix Database Table Prefix
*
* @return string Database Table Prefix
*/
$DB->set_table_prefix('newprefix');In many databases it is advisable to protect table and field names - for example with backticks in MySQL. Query Builder queries are automatically protected, however if you need to manually protect an identifier you can use:
/*
* @param string Database Table Name
* @param bool Use Table Prefix (default is FALSE)
*
* @return string Protected Identifiers Database Table Name
*/
$DB->protect_identifiers('table_name');Although the Query Builder will try its best to properly quote any field and table names that you feed it, note that it is NOT designed to work with arbitrary user input. DO NOT feed it with unsanitized user data.
It’s a very good security practice to escape your data before submitting it into your database. O2DB has three methods that help you do this:
This function determines the data type so that it can escape only string data. It also automatically adds single quotes around the data so you don’t have to:
$sql = "INSERT INTO table_name (title) VALUES(" . $DB->escape($title) . ")";This function escapes the data passed to it, regardless of type. Most of the time you’ll use the above function rather than this one. Usage example:
$sql = "INSERT INTO table_name (title) VALUES(" . $DB->escape_string($title) . ")";This method should be used when strings are to be used in LIKE conditions so that LIKE wildcards (‘%’, ‘_’) in the string are also properly escaped.
$search = '20% something';
$sql = "SELECT id FROM table WHERE column LIKE '%" . $DB->escape_like_string($search) . "%'";The execute method is a simplified version of the regular query method below. It DOES NOT return a database result set, nor does it set the query timer, or compile bind data, or store your query for debugging. It simply lets you submit a query using basic PDO::query() and return PDOStatement Object. Most users will rarely use this function.
/*
* @param string $sql SQL Statement
*
* @return bool
*/
$DB->execute('SELECT * FROM table_name');The query() function returns a database result object when “read” type queries are run, which you can use to show your results. When “write” type queries are run it simply returns TRUE or FALSE depending on success or failure. When retrieving data you will typically assign the query to your own variable, like this:
/*
* @param string $sql SQL Statement
*
* @return \O2System\DB\Factory\Result
*/
$result = $DB->query('SELECT * FROM table_name');Bindings enable you to simplify your query syntax by letting the system put the queries together for you.
$sql = "SELECT * FROM table_name WHERE id = ? AND record_status = ?";
$result = $DB->query($sql, [1, 'PUBLISH']);Produces: SELECT * FROM table_name WHERE id = 1 AND record_status 'PUBLISH'
Binding also work with arrays, which will be transformed to IN sets.
$sql = "SELECT * FROM table_name WHERE id IN ? AND record_status = ?";
$result = $DB->query($sql, [array(1, 2, 3), 'PUBLISH']);Produces: SELECT * FROM table_name WHERE id IN (1,2,3) AND record_status 'PUBLISH'
If you need to get the last error that has occured, the error() method will return an array containing its code and message. Here’s a quick example:
if( ! $this->db->execute('SELECT `field_name` FROM `table_name`') )
{
$error = $DB->error(); // Returns error code and message
}All the results from regular query methods will return an iterator class named \O2System\DB\Factory\Result. By default each row of data will be included in an object class named \O2System\DB\Factory\Row that implements IteratorAggregate Interface Class, therefore the row data can be call as an array or object and if row field has a value in JSON format or Serialize format, it will automatically be transformed into an object class named \O2System\DB\Factory\Row
$results = $DB->query('SELECT * FROM table_name');
foreach($results as $row)
{
echo $row->title . '<br />' // you can type like array also $row['title']
}$results will be used as an example variable at results topics below.
This method returns a single result row. If your query has more than one row, it returns "number" of rows
/*
* @param int Number of result row position (default is zero (0) identical of first row)
*
* @return \O2System\DB\Factory\Row
*/
$results->row();If you want a specific row returned you can submit the row number as a digit in the first parameter:
$results->row(5);In addition, you can walk forward/backwards/first/last through your results using these variations:
$row = $results->first();
$row = $results->last();
$row = $results->next();
$row = $results->previous();
$row = $results->current();If you want to seek a row position:
$row = $results->seek(5);Convert rows array into serialize array string format
echo $results->serialize();Convert rows array into JSON encode string format
echo $results->json();The number of rows returned by the query.
$results->num_rows();The number of fields (columns) returned by the query.
$results->num_fields();It frees the memory associated with the result and deletes the result resource ID. Normally PHP frees its memory automatically at the end of script execution. However, if you are running a lot of queries in a particular script you might want to free the result after each query result has been generated in order to cut down on memory consumption.
$results->destroy(); // The PDOStatement object will no longer be availableReturn an array of fields name.
$results->fields_list();Return an array of field metadata.
$results->fields_metadata();