axisstudios / db-record
Manage database records. This library is part of the SoloProyectos PHP API.
Requires
- php: >=5.5.0
- soloproyectos-php/array: ~1.0
- soloproyectos-php/db: ~1.1
- soloproyectos-php/text: ~1.0
README
Introduction
DbRecord
is a library that allows us to operate databases without manually execute SQL statements. Unlike other similar libraries, DbRecord allows us to operate on multiple tables at the same time, resulting in a more concise and clear code.
Installation
This package is provided via composer package manager. Just create a composer.json
file and execute the following command in the same directory:
composer install
See Basic Usage for more info.
Database requirements
This library can operate any MySQL database, with the only condition that each table MUST have a primary key composed by a single auto-increment column. By default, the primary key is called ID
, but you can change it from the constructor.
Basic examples: insert(), update(), select() and delete()
We use the DbRecordTable
class to perform basic operations.
Inserting records
To insert records, we ommit the id
parameter from the constructor:
// insert a record, as the 'id' parameter is not present $t = new DbRecordTable($db, "table0"); $id = $t->insert(["title" => "New title", "created_at" => date("Y-m-d H:i:s")]); echo "Inserted record ID: $id";
Updating records
Updates the record ID=1:
$t = new DbRecordTable($db, "table0"); $t->update(["title" => "New title", "created_at" => date("Y-m-d H:i:s")], 1);
Inserting/Updating records
You can insert or update a record in the same line. For example:
$t = new DbRecordTable($db, "table0"); // if $recordId is null, insert a new record and returns the id, // otherwise update the current record $recordId = $t->save(["title" => "New title", "created_at" => date("Y-m-d H:i:s")], $recordId); echo $recordId;
Selecting records
Selects the record ID=1:
$t = new DbRecordTable($db, "table0"); list($title, $createdAt) = $t->select(["title", "created_at"], 1); echo "title: $title, Created at: $createdAt";
Deleting records
Delete the record ID=1:
$t = new DbRecordTable($db, "table0"); $t->delete(1);
For a more complex example see test1.php.
General example: Accessing several tables at the same time
Let's say that we have a main table (table0
) and three secondary tables (table1
, table2
and table3
). The three tables are 'left joined' to the main table through the columns table1_id
, table2_id
and table3_id
. That is:
Instead of operating on tables individually, we can do it at the same time. The following example selects a record (ID = 1) and updates or inserts records on table1
, table2
and table3
:
// the following example updates table0, table1, table2 and table3 at the same time $t = new DbRecordTable($db, "table0"); $t->update( [ "title" => "My title", "created_at" => date("Y-m-d H:i:s"), "table1.title" => "Title 1", "table2.title" => "Title 2", "table3.title" => "Title 3" ], 1 );
The following example selects a record (ID = 1) and retrieves columns from table0
, table1
, table2
and table3
at the same time:
$t = new DbRecordTable($db, "table0"); list($title, $createdAt, $t1Title, $t2Title, $t3Title) = $t->select( [ "title", "created_at", "table1.title", "table2.title", "table3.title" ], 1 ); echo "title: $title, created_at: $createdAt, table1.title: $t1Title, table2.title, $t2Title, table3.title, $t3Title";
For a more complex example see test2.php.
Column path expressions
In the previous examples we accessed the title
column of table1
through the following expression: table1.title
. The general format is as follows:
table1[id = table0_id].title
We can omit id
and table0_id
, as they are taken by default. So the previous expression can by simplified as follows:
table1.title
Let's imagine a more complex example. Let's say that table2
depends on table1
which, at the same time, depends on table0
. That is:
In that case, we use the following code to access the table1
and table2
columns:
$t = DbRecordTable($db, "table0"); list($title, $t1Title, $t2Title) = $t->select( [ "title", "table1.title", "table2[table1.table2_id].title" ], 1 );
The previous expressios are simplified versions of the general expressions:
table1[id = table1_id].title
table2[id = table1.table2_id].title
We can omit id
and <table>_id
, as they are taken by default.
For more complex examples see test3.php, test4.php y test5.php