ceus-media / database
PHP database access
Installs: 1 079
Dependents: 2
Suggesters: 0
Security: 0
Stars: 0
Watchers: 3
Forks: 0
Open Issues: 0
Requires
- php: ^8.1
- ext-pdo: *
- ceus-media/cache: ^0.5 | 0.5.x-dev
- ceus-media/common: ^1.0 | 1.0.x-dev
Requires (Dev)
- ext-mysqli: *
- ceus-media/doc-creator: ^1.0 | 1.0.x-dev
- friendsofphp/php-cs-fixer: *
- php-parallel-lint/php-parallel-lint: ^1.3 | dev-master
- phpstan/phpstan: ^1
- phpstan/phpstan-strict-rules: ^1
- phpunit/phpunit: ^9.5 | ^10.1
- rector/rector: *
README
PHP database access
Installation
Composer
Install this library using composer:
composer require ceus-media/Database
Within your code, load library:
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:
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.
$table = new MyFirstTable( $dbc );
Reading an entry
Example for getting an entry by its primary key:
$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:
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.
Hint: There are more methods to retrive a single entry:
- getByIndex
- getByIndices
which allow to focus on foreign indices instead of the primary key.
Finding entries
A group of entries, filtered by a foreign key:
$someEntries = $table->getAllByIndex( 'maybeSomeForeignId', 123 );
A group of entries, filtered by several foreign keys:
$indices = [ 'maybeSomeForeignId' => 123, 'notExistingKey' => 'will result in an exception', ]; $someEntries = $table->getAllByIndices( $indices );
To get all entries, call:
$allEntries = $table->getAll();
which may be bad in scaling, so reduce the result set by defining limits and conditions:
$conditions = ['content' => '%test%']; $orders = []; $limits = [$offset = 0, $limit = 10]; $allEntries = $table->getAll( $conditions, $orders, $limits );
Conditions can be indices or any other column.
Orders are pairs of columns and directions, like:
$orders = [ 'maybeSomeForeignId' => 'DESC', 'content' => 'ASC', ];
There are more parameters possible for each of this indexing methods, which allow:
- fields: restricting columns in result set
- grouping: apply GROUP BY
- having: apply HAVING
Counting
To count entries by a foreign key:
$number = $table->countByIndex( 'maybeSomeForeignId', 123 );
To count entries, filtered by several foreign keys:
$number = $table->countByIndices( [ 'maybeSomeForeignId' => 123, 'notExistingKey' => 'will result in an exception', ] );
To get all entries, call:
$number = $table->count();
which may be bad in scaling, so reduce the result set by defining conditions:
$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.
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!
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.
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!
Updating several entries
$indices = [ 'maybeSomeForeignId' => 123, ]; $data = [ 'maybeSomeForeignId' => 124, ]; $result = $table->editByIndices( $indices, $data );
where the result will be the number of changed entries.
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!
Removing an entry
$primaryKey = 2; $result = $table->remove( $primaryKey );
where the result will be the number of removed entries.
Removing several entry
$indices = [ 'maybeSomeForeignId' => 123, ]; $result = $table->removeByIndices( $indices );
where the result will be the number of removed entries.
Change fetch mode
In your table structure class, set:
protected int $fetchMode = \PDO::[YOUR_FETCH_MODE];
where YOUR_FETCH_MODE is one of these standard PDO fetch modes:
- FETCH_ASSOC
- FETCH_NAMED
- FETCH_NUM
- FETCH_BOTH
- FETCH_OBJ
Code Examples using OSQL
Having a config file like this:
driver = 'mysql'; host = 'myHost'; port = 'myPort'; database = 'myDatabase'; username = 'myDatabaseUser'; password = 'myDatabasePassword';
and assuming that you load things up like this:
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:
$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:
$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):
new UI_DevOutput(); print_m( $result );
will produce:
[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