Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
PHP
branch: master

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.
classes
README.md
test.php

README.md

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:

Example 1

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:

Example 2

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

Something went wrong with that request. Please try again.