php.dbquery

With this library you can update multiple interrelated tables at the same time without writing separated SQL statements.

Installation

Download the project:

git clone https://github.com/soloproyectos/php.dbquery

and copy the classes folder in your preferred location (optionally, rename it). Finally, copy and paste the following PHP code:

require_once "< YOUR PREFERRED LOCATION >/classes/autoload.php";
use com\soloproyectos\common\db\DbConnector;
use com\soloproyectos\common\db\DbTable;

And that's all. You are ready to use this library.

Important methods

1. `DbTable::get($colName)`: Gets a column value
2. `DbTable::set($colName, $colValue)`: Sets a column value
3. `DbTable::insert()`: Inserts a new record
4. `DbTable::update()`: Updates a record
5. `DbTable::delete()`: Deletes a record
6. `DbTable::serOrder($order)`: sets an order
7. `DbTable::addFilter($filter)`: adds a filter
8. `DbTable::refresh()`: refreshes the columns

Introduction

Let's say that we have a content table and several tables that depend on it. That is:

In this scenario, we can update (or eventually insert) records in the tables content, image, video and file at the same time, as if it were a single table. In the following example we are updating a record of the table content and updating (or inserting) the corresponding records of the tables image, video and file:

// connects to the database
$db = new DbConnector("database", "username", "password");

// updates the record "content.id = 101"
$t = new DbTable($db, "content", 101);
$t->set("description", "Record description...");
$t->set("image.path", "/path/to/image.jpg");
$t->set("video.path", "/path/to/video.mp4");
$t->set("file.path", "/path/to/file.pdf");
$t->update();

Let's consider a more complex example, in which the table image depends on the table video which in turn depends on the table content. That is:

In the following example, the expressión image[video.image_id].path means that the table image depends on the table video through the column image_id:

// connects to the database
$db = new DbConnector("database", "username", "password");

// updates the record "content.id = 101"
$t = new DbTable($db, "content", 101);
$t->set("description", "Record description...");
$t->set("video.path", "/path/to/video.mp4");
$t->set("image[video.image_id].path", "/path/to/image.jpg");
$t->update();

Assumptions

By default, DbTable assumes that each table has a primary key called id (although this name can be changed in the constructor). Also it assumes that table1 depends on table0 through the column table1_id. So these expressions are equivalents:

// these three commands are equivalents
echo $t->get("image.path");
echo $t->get("image[image_id].path");
echo $t->get("image[id = image_id].path");

// these three commands are equivalents as well 
echo $t->get("image[video.image_id].path");
echo $t->get("image[id = video.image_id].path");
echo $t->get("image[id = video[id = video_id].image_id].path");

The general format to access a column of a dependent table is as follows:

table1[col0 = col1].column

In case of col0 is id and col1 is table1_id we can write:

table1.column

Some other valids expressions are:

table1[col1].column
table1[col0 = col1].column
table1[col0 = table2.col1].column
table1[col0 = table2[col2 = col3].col4].column
etc...

Examples

See more examples at test.php