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.

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.

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.

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.

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.

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