PHP Classes

DbTable: Update table records in multiple related tables

Recommend this page to a friend!
  Info   View files Example   View files View files (23)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 139 This week: 1All time: 9,215 This week: 560Up
Version License PHP version Categories
dbtable 1.0BSD License5.5PHP 5, Databases
Description 

Author

This package can update table records in multiple related tables.

It can connect with a given MySQL database using the MySQLi extension and then it can perform operations that update table records of a given table and any other related tables according to parameters that define the relations between the tables.

Currently it can perform SELECT, INSERT, UPDATE and DELETE operations composed using functions for defining parameters like table columns and their values, filter conditions, column order, etc..

Picture of Gonzalo Chumillas
Name: Gonzalo Chumillas <contact>
Classes: 8 packages by
Country: Spain Spain
Age: 49
All time rank: 135129 in Spain Spain
Week rank: 416 Up14 in Spain Spain Up
Innovation award
Innovation award
Nominee: 2x

Example

<?php
header
("Content-type: text/plain; charset=UTF-8");
require_once
"classes/autoload.php";
use
com\soloproyectos\common\db\DbConnector;
use
com\soloproyectos\common\db\DbDataSource;
use
com\soloproyectos\common\db\DbTable;

$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();

// inserts a new record
$t = new DbTable($db, "content");
$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->insert();

// deletes the record "content.id = 101"
$t = new DbTable($db, "content", 101);
$t->delete();

// prints info from the record "content.id = 101"
$t = new DbTable($db, "content", 101);
$t->get("description");
$t->get("image.path");
$t->get("video.path");
$t->get("file.path");

// loops through the records of the table "content"
$t = new DbTable($db, "content");
$t->setOrder("id desc");
$t->setFilter("section_id = 'blah'");
foreach (
$t as $row) {
   
// updates the record
   
$row->set("description", "New description");
   
$row->set("image.path", "/path/to/new/image.jpg");
   
$row->update();
   
   
// prints some info
   
echo "Description: " . $row->get("description") . "\n";
    echo
"Image: " . $row->get("image.path") . "\n";
    echo
"Video: " . $row->get("video.path") . "\n";
    echo
"File: " . $row->get("file.path") . "\n";
}


Details

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


  Files folder image Files  
File Role Description
Files folder imageclasses (1 file, 4 directories)
Accessible without login Plain text file README.md Doc. Auxiliary data
Accessible without login Plain text file test.php Example Example script

  Files folder image Files  /  classes  
File Role Description
Files folder imagedb (7 files)
Files folder imagedebug (1 file)
Files folder imagesys (3 directories)
Files folder imagetext (1 file, 3 directories)
  Accessible without login Plain text file autoload.php Aux. Class source

  Files folder image Files  /  classes  /  db  
File Role Description
  Accessible without login Plain text file db-column-constant.php Class Class source
  Accessible without login Plain text file db-column.php Class Class source
  Accessible without login Plain text file db-connector.php Class Class source
  Accessible without login Plain text file db-data-source.php Class Class source
  Accessible without login Plain text file db-helper.php Class Class source
  Accessible without login Plain text file db-table-column-manager.php Class Class source
  Accessible without login Plain text file db-table.php Class Class source

  Files folder image Files  /  classes  /  debug  
File Role Description
  Accessible without login Plain text file debug-capable.php Class Class source

  Files folder image Files  /  classes  /  sys  
File Role Description
Files folder imagecmd (4 files, 1 directory)
Files folder imageexception (1 file)
Files folder imagefile (1 file)

  Files folder image Files  /  classes  /  sys  /  cmd  
File Role Description
Files folder imageexception (1 file)
  Accessible without login Plain text file sys-cmd-argument.php Class Class source
  Accessible without login Plain text file sys-cmd-arguments-parser.php Class Class source
  Accessible without login Plain text file sys-cmd-helper.php Class Class source
  Accessible without login Plain text file sys-cmd.php Class Class source

  Files folder image Files  /  classes  /  sys  /  cmd  /  exception  
File Role Description
  Accessible without login Plain text file sys-cmd-exception.php Class Class source

  Files folder image Files  /  classes  /  sys  /  exception  
File Role Description
  Accessible without login Plain text file sys-exception.php Class Class source

  Files folder image Files  /  classes  /  sys  /  file  
File Role Description
  Accessible without login Plain text file sys-file-helper.php Class Class source

  Files folder image Files  /  classes  /  text  
File Role Description
Files folder imageexception (1 file)
Files folder imageparser (1 file, 1 directory)
Files folder imagetokenizer (1 file)
  Accessible without login Plain text file text-helper.php Class Class source

  Files folder image Files  /  classes  /  text  /  exception  
File Role Description
  Accessible without login Plain text file text-exception.php Class Class source

  Files folder image Files  /  classes  /  text  /  parser  
File Role Description
Files folder imageexception (1 file)
  Accessible without login Plain text file text-parser.php Class Class source

  Files folder image Files  /  classes  /  text  /  parser  /  exception  
File Role Description
  Accessible without login Plain text file text-parser-exception.php Class Class source

  Files folder image Files  /  classes  /  text  /  tokenizer  
File Role Description
  Accessible without login Plain text file text-tokenizer.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:139
This week:1
All time:9,215
This week:560Up