PHP Classes

DataBase Object (DBO): Manipulate database table records

Recommend this page to a friend!
  Info   View files Example   View files View files (9)   DownloadInstall with Composer Download .zip   Reputation   Support forum (1)   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 161 This week: 1All time: 8,946 This week: 560Up
Version License PHP version Categories
db_dbo 1.0.0GNU General Publi...5PHP 5, Databases
Description 

Author

This package can manipulate database table records.

There is one class that connects to database and execute queries.

Another class performs common operations with database table records based on information of the tables and fields stored in class variables.

Currently it can perform operations like creating, updating, deleting records, load records, validate record values, get a list records, delete a list of records.

Applications may create sub-classes with predefined values for specific tables with given fields.

Picture of Oleg Zorin
  Performance   Level  
Name: Oleg Zorin <contact>
Classes: 3 packages by
Country: Russian Federation Russian Federation
Age: 39
All time rank: 186255 in Russian Federation Russian Federation
Week rank: 106 Up7 in Russian Federation Russian Federation Up
Innovation award
Innovation award
Nominee: 1x

Recommendations

Edit, eliminate, search, and sort table records
Maintain tables in a MySQL database

Example

<?php
   
require_once('./../db.class.php');
    require_once(
'./../dbo.class.php');
   
   
/* Mysql access */
   
$sql_driver = 'mysql';
   
$sql_host = 'localhost';
   
$sql_name = 'opensource.my';
   
$sql_user = 'root';
   
$sql_pass = '';
   
OZ\DB::init($sql_driver, $sql_host, $sql_name, $sql_user, $sql_pass);
   
   
/* Load your DBO childs */
   
require_once('./dbo_classes/company.dbo.class.php');
    require_once(
'./dbo_classes/company_office.dbo.class.php');
   
   
   
/* Try to get company with ID 1 */
   
$company = new Company(1);
   
   
/* Check if DB entry exists. */
    /* You should check id field! */
   
if(!empty($company->id)) {
       
/* modify and update some data */
       
$company->name = 'My new company name';
       
$company->save();
    }
   
   
/* Create new company office */
   
$office = new CompanyOffice();
   
$office->companyID = $company->id;
   
$office->name = 'Office 2';
   
$office->address = 'Office address 2';
   
/* check data */
   
$validation = $office->validate();
    if(
$validation['result']) {
       
$office->save();
    }
    else {
        echo
'<pre>';
       
print_r($validation['errors']);
        echo
'</pre>';
    }
   
   
/* Validation error, create office without companyID (required) and with same name as previous one (unique) */
   
$office = new CompanyOffice();
   
$office->name = 'Office 2';
   
$office->address = 'Office address 3';
   
/* check data */
   
$validation = $office->validate();
    if(
$validation['result']) {
       
$office->save();
    }
    else {
        echo
'<pre>';
       
print_r($validation['errors']);
        echo
'</pre>';
    }
   


Details

db_dbo

Package contains 2 classes:

  • DBO is database tabels manager.
  • DB is static wrapper for PDO (allow to use it in every contnent of your application). Required for DBO.

PHP Tested: 5.6.19, 7.0.11

CONTENTS

1. SHORT EXAMPLE
2. DBO
  2.1. TABLE DEFINITION
  2.2. FIELDS TYPES DEFINITION
  2.3. DBO PUBLIC METHODS
    2.3.1. __construct()
    2.3.2. prepare()
    2.3.3. save()
    2.3.4. delete()
    2.3.5. reload()
    2.3.6. validate()
    2.3.7. unique()
    2.3.8. static getList()
    2.3.9. static deleteList()
3. DB
  3.1. DB PUBLIC METHODS
    3.1.1. init()
    3.1.2. query()
    3.1.3. prepare()
    3.1.4. execute()
    3.1.5. fetch()
    3.1.6. id()
    3.1.7. rows()
    3.1.8. escape()

1. SHORT EXAMPLE

<?php

class User extends OZ\DBO {
  public $name;
  public $login;

  public static $definition = array(
    'table' => 'users',
    'id' => 'userID',
    'fields' => array(
      'name' => array('type' => 'text', 'required' => true),
      'login' => array('type' => 'text', 'required' => true, 'unique' => true)
    )
  );

  function __construct($id = 0) {
    parent::__construct($id);
  }
}

/Update name of user with ID 35/
$user = new User(35);
$user->name = 'New name';
$user->save();

/Delete user with ID 12/
$user = new User(12);
$user->delete();

/Or simple use static deleteList() method/
User::deleteList(array(12));

/Create new user and check unique login/
$user = new User();
$user->name = 'Name';
$user->login = 'Login';
$validation = $user->validate();
if($validation['result']) {
  $user->save();
}
else {
  print_r($validation['errors']);
}

/Get users list sorted by name/
$result = User::GetList(array('order' => array('name' => 'asc')));
if(!empty($result['list'])) {
  print_r($result['list']);
}

/Get users list with names John, sorted by ID, and use pagination/
$result = User::GetList(
	array(
		'filter' => array('name' => 'John'),
		'order' => array('userID' => 'asc'),
		'pager' => array(
			'page' => 2,
			'onpage' => 10
		)
	)
);
if(!empty($result['list'])) {
  print_r($result['list']);
}

2.DBO

Each data base table should have its own DBO child class with its own table definition.

Simlest class for users table with fields userID, name and login:

class User extends OZ\DBO {
  public $name;
  public $login;

  public static $definition = array(
    'table' => 'users',
    'id' => 'userID', /ID will store in $id field/
    'fields' => array(
      'name' => array('type' => 'text', 'required' => true),
      'login' => array('type' => 'text', 'required' => true, 'unique' => true)
    )
  );

  function __construct($id = 0) {
    parent::__construct($id);
  }
}

2.1. TABLE DEFINITION

There are several steps to create the table definition in your DBO child:

  1. Define public fields of class equal to fields in database, except ID field.

    $name and $login fields in example above.

  2. Define any additional fields of class.

    It could be field that related on database fields such as $full_name field, which can be as a result of table fields $name_first and $name_last.

    Or it could contain list of data from other database tables (see company.dbo.class.php in example).

  3. Create $definition array.

    'table' - name of your database table

    'id' - id field in your database table

    'fields' - list of all other fields with some params.

    For each field in 'fields' section you should provide type of field. Also you can add required or unique field properties. See example above.

    Class provide several default types: numeric (numeric database fields), text, html, mail, password (all other database fields). You can define your own types such as 'date' for date or datetime fields (see section 2.2).

  4. Define class constructor and redefine other parent methods (if required).

    For example, you can skip parent construcor if you have data in cache. Or update you cache after calling parent save() or delete() methods.

  5. Define you own methods.

2.2. FIELDS TYPES DEFINITION

Actualy you can use any type you want. But several DBO parent methods use type for several purposes:

  1. Preparetion fields before SQL insert or update statment.

    DBO::prepare() has check all fields and try to convert them to proper format or set empty (zero, null) data.

    So, it's to modify this method for your field types

  2. Fields validation rules.

    DBO::validate() has check all fields according rules for field type.

    So, if you want some validation rules for your field types modify validate() method.

2.3. DBO PUBLIC METHODS

2.3.1. __construct($id = 0)

Class constructor.

Create object and try to get database record with $id.

You can check data by checking id field:

$user = new User(2);
if(!empty($user->id)) {
  /data was load from DB/
}
else {
  /there is no such ID in DB/
}

2.3.2. prepare()

Check all fields and try to convert them to proper format based on field type definition.

2.3.3. save()

Insert (if $id is empty) or update data in DB.

2.3.4. delete()

Remove data with current $id from DB.

2.3.5. reload()

Reload data from DB. Additional fields of class are skipped.

2.3.6. validate()

Validate current object fields.

Returns array with keys:

  • result boolean - result of validation
  • errors array - list of errors for each fields (if validation fails)

2.3.7. unique($field, $value)

Check if $value of $filed of current object is unique in database

Returns boolean result of checking.

2.3.8. static getList($params = array())

Get list of data from database.

You can filter and sort your list. You can get single page of list (pagination) also.

Filer your result:

$params = array(
	'filter' => array('<field1>' => '<value1>', '<field2>' => '<value2>', ...)
)

where field - name of field in database,
      value - filterd value
			

Sort your result:

$params = array(
	'sort' => array('<field1>' => '<direction1>', '<field2>' => '<direction2>', ...)
)

where field - name of field in database,
      direction - sort direction: asc or desc

Pagination:

$params = array(
	'pager' => array('page' => '<current_page>', 'onpage' => '<items_on_page>')
)

where page - current list page,
      onpage - number of items per page

You can mix these params.

In result you'll get an array with keys:

  • list - list of selected items
  • filter - list of accepted filters (if provided in params)
  • sort - list of accepted sorting params (if provided in params)
  • pager - array with data fo pager (if provided in params):

    page - current list page

    onpage - number of items in page

    pages - total list pages

    record - total items number

2.3.9. static deleteList($list = array())


Removes DB entries according to $list of IDs.

3. DB

DB class is simple wrapper of PDO class.

It's static, so you have access to it from every context of your application.

3.1. DB PUBLIC METHODS

DB provide all basic PDO routine:

3.1.1. init($drvr, $host, $name, $user, $pass)

Initialization method. Create instance of PDO.

Params:

  • $drvr string - DB driver.
  • $host string - DB server.
  • $name string - DB name.
  • $user string - DB user.
  • $pass string - DB user password.

3.1.2. query($query)

Executes an SQL statement.

Params:

  • $query string - SQL request.

Returns:

  • resource - result set as a PDOStatement object.

3.1.3. prepare($query)

Prepares an SQL statement to be executed by the DB::execute() method.

Params:

  • $query string - SQL request.

Returns:

  • resource - result set as a PDOStatement object.

3.1.4. execute($res, $placeholders = array())

Prepares an SQL statement to be executed by the DB::execute() method.

Params:

  • $res resource - PDOStatement object.
  • $placeholders array - Array of placeholders (placeholder => value).

Returns:

  • boolean - true on success or false on failure.

3.1.5. fetch($res)

Fetches the next row from a result set

Params:

  • $res resource - PDOStatement object.

Returns:

  • array|false - DB record as associated array.

3.1.6. id()

Returns the ID of the last inserted row or sequence value

Returns:

  • array|false - ID of the last inserted row or sequence value.

3.1.7. rows($res)

Returns the number of rows affected by the last SQL statement.

Returns:

  • int - number of rows.

3.1.8. escape($string)

Quotes a string for use in a query.

Params:

  • $string string - The string to be quoted.

Returns:

  • string - quoted string that is theoretically safe to pass into an SQL statement.

  Files folder image Files  
File Role Description
Files folder imageexamples (3 files, 1 directory)
Plain text file db.class.php Class DB class
Plain text file dbo.class.php Class DBO class
Accessible without login Plain text file LICENSE Lic. License
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  examples  
File Role Description
Files folder imagedbo_classes (2 files)
  Accessible without login Plain text file demo.sql Data Demo SQL dump
  Accessible without login Plain text file example_db.php Example DB demo
  Accessible without login Plain text file example_dbo.php Example DBO demo

  Files folder image Files  /  examples  /  dbo_classes  
File Role Description
  Plain text file company.dbo.class.php Class Company DBO class (demo)
  Accessible without login Plain text file company_office.dbo.class.php Example CompanyOffice DBO class (demo)

 Version Control Unique User Downloads Download Rankings  
 100%
Total:161
This week:1
All time:8,946
This week:560Up
User Comments (1)
very good
7 years ago (muabshir)
80%StarStarStarStarStar