Database
, (*1)
PHP database access, (*2)
Installation
Composer
Install this library using composer:, (*3)
composer require ceus-media/Database
Within your code, load library:, (*4)
require_once 'vendor/autoload.php';
Code Examples using PDO
Database Connection
$dbDriver = 'mysql';
$dbName = 'myDatabase';
$dbUsername = 'myDatabaseUser';
$dbPassword = 'myDatabasePassword';
$dbc = new \CeusMedia\Database\PDO\Connection(
new \CeusMedia\Database\PDO\DataSourceName( $dbDriver, $dbName ),
$dbUsername, $dbPassword
);
Tables
Existing database tables can be declared as tables:, (*5)
Table class
class MyFirstTable extends \CeusMedia\Database\PDO\Table
{
protected string $name = "my_first_table";
protected array $columns = [
'id',
'maybeSomeForeignId',
'content',
];
protected string $primaryKey = 'id';
protected array $indices = [
'maybeSomeForeignId',
];
protected int $fetchMode = \PDO::FETCH_OBJ;
}
Table instance
Having this defined structure, you can use a table instance for reading from and writing into the database table. Hence that you need to create a database connection beforehand., (*6)
$table = new MyFirstTable( $dbc );
Reading an entry
Example for getting an entry by its primary key:, (*7)
$entry = $table->get( 1 );
The result will be an object of table columns and their values, since the fetch mode is set to object-wise by table structure:, (*8)
object stdObject(
'id' => 1,
'maybeSomeForeignId' => 123,
'content' => 'Content of first entry.'
)
Not having the fetch mode set would result in an associated array, which is set as default fetch mode in underlaying table reader. To change the fetch see below., (*9)
Hint: There are more methods to read a single entry:, (*10)
which allow to focus on foreign indices instead of the primary key., (*11)
Finding entries
A group of entries, filtered by a foreign key:, (*12)
$someEntries = $table->getAllByIndex( 'maybeSomeForeignId', 123 );
A group of entries, filtered by several foreign keys:, (*13)
$indices = [
'maybeSomeForeignId' => 123,
'notExistingKey' => 'will result in an exception',
];
$someEntries = $table->getAllByIndices( $indices );
To get all entries, call:, (*14)
$allEntries = $table->getAll();
which may be bad in scaling, so reduce the result set by defining limits and conditions:, (*15)
$conditions = ['content' => '%test%'];
$orders = [];
$limits = [$offset = 0, $limit = 10];
$allEntries = $table->getAll( $conditions, $orders, $limits );
Conditions can be indices or any other column., (*16)
Orders are pairs of columns and directions, like:, (*17)
$orders = [
'maybeSomeForeignId' => 'DESC',
'content' => 'ASC',
];
There are more parameters possible for each of this indexing methods, which allow:, (*18)
- fields: restricting columns in result set
- grouping: apply GROUP BY
- having: apply HAVING
Counting
To count entries by a foreign key:, (*19)
$number = $table->countByIndex( 'maybeSomeForeignId', 123 );
To count entries, filtered by several foreign keys:, (*20)
$number = $table->countByIndices( [
'maybeSomeForeignId' => 123,
'notExistingKey' => 'will result in an exception',
] );
To get all entries, call:, (*21)
$number = $table->count();
which may be bad in scaling, so reduce the result set by defining conditions:, (*22)
$Conditions = [
'maybeSomeForeignId' => 123,
'content' => '%test%',
];
$number = $table->count( $conditions );
Hint: Counting having really large MySQL tables may be slow.
There is a method to count in large tables in a faster way. You will find it., (*23)
Adding an entry
$data = [
'maybeSomeForeignId' => 123,
'content' => 'Second entry.',
];
$entryId = $table->add( $data );
Attention: For security reasons, all HTML tags will be striped. Set second parameter to FALSE to avoid that, if needed. Make sure to strip HTML tags of none-HTML columns manually!, (*24)
Updating an entry
$primaryKey = 2;
$data = [
'maybeSomeForeignId' => 124,
'content' => 'Second entry - changed.',
];
$result = $table->edit( $primaryKey, $data );
where the result will be the number of changed entries., (*25)
Attention: For security reasons, all HTML tags will be striped. Set third parameter to FALSE to avoid that, if needed. Make sure to strip HTML tags of none-HTML columns manually!, (*26)
Updating several entries
$indices = [
'maybeSomeForeignId' => 123,
];
$data = [
'maybeSomeForeignId' => 124,
];
$result = $table->editByIndices( $indices, $data );
where the result will be the number of changed entries., (*27)
Attention: For security reasons, all HTML tags will be striped. Set third parameter to FALSE to avoid that, if needed. Make sure to strip HTML tags of none-HTML columns manually!, (*28)
Removing an entry
$primaryKey = 2;
$result = $table->remove( $primaryKey );
where the result will be the number of removed entries., (*29)
Removing several entry
$indices = [
'maybeSomeForeignId' => 123,
];
$result = $table->removeByIndices( $indices );
where the result will be the number of removed entries., (*30)
Change fetch mode
In your table structure class, set:, (*31)
protected int $fetchMode = \PDO::[YOUR_FETCH_MODE];
where YOUR_FETCH_MODE is one of these standard PDO fetch modes:, (*32)
- FETCH_ASSOC
- FETCH_NAMED
- FETCH_NUM
- FETCH_BOTH
- FETCH_OBJ
Entities
Reading from tables can return lists of arrays or anonymous objects, easily.
To use entity classes to receive data objects, PDO's fetch mode can be set to FETCH_CLASS.
A table implementation needs to set ::fetchEntityClass to a class name., (*33)
This could be an entity class:, (*34)
class MyFirstTableEntity
{
public string $id;
public string $maybeSomeForeignId;
public string $content;
}
This entity class can be linked within the table as class to use on fetch:, (*35)
class MyFirstTable extends Table
{
...
public ?string $fetchEntityClass = '\\MyProject\\MyFirstTableEntity';
}
Now, all indexing methods will return lists of filled entity classes., (*36)
Code Examples using OSQL
Having a config file like this:, (*37)
driver = 'mysql';
host = 'myHost';
port = 'myPort';
database = 'myDatabase';
username = 'myDatabaseUser';
password = 'myDatabasePassword';
and assuming that you load things up like this:, (*38)
require_once 'vendor/autoload.php';
use CeusMedia\Database\PDO\DataSourceName;
use CeusMedia\Database\OSQL\Client;
use CeusMedia\Database\OSQL\Connection;
use CeusMedia\Database\OSQL\Condition;
use CeusMedia\Database\OSQL\Table;
use CeusMedia\Database\OSQL\Query\Select;
$config = (object) parse_ini_file( 'myConfigFile.ini' );
you can connect to a database like this:, (*39)
$client = new Client( new Connection( DataSourceName::renderStatic(
$config->driver,
$config->database,
$config->host,
$config->port,
$config->username,
$config->password
), $config->username, $config->password ) );
Now you can query the database like this:, (*40)
$result = Select::create( $client )
->from( new Table( 'galleries', 'g' ) )
->where( new Condition( 'galleryId', 1, Condition::OP_EQ ) )
->execute();
The result will contain the requested rows (only one in this example):, (*41)
new UI_DevOutput();
print_m( $result );
will produce:, (*42)
php
[O] 0 -> stdClass
[S] galleryId => 1
[S] status => 0
[S] rank => 1
[S] path => test
[S] title => Test
[S] description => Das ist ein Test.
[S] timestamp => 1402008611, (*43)
Wallogit.com
