PEAR is archived and read-only

This mirror preserves historical PEAR package releases and metadata so existing references remain available.

Home » Database » MDB_QueryTool » Manual

This package is an OO-abstraction to the SQL-Query language, it provides methods such as setWhere, setOrder, setGroup, setJoin, etc. to easily build queries.

Introduction

Introduction – An OO-interface for easily retrieving and modifying data in a database

MDB_QueryTool Description

This package is an OO-abstraction to the SQL-Query language, it provides methods such as setWhere(), setOrder(), setGroup(), setJoin(), etc. to easily build queries. It also provides an easy to learn interface that interacts nicely with HTML-forms using arrays that contain the column data, that shall be updated/added in a database. This package bases on an SQL-Builder which lets you easily build SQL-Statements and execute them. It supports all the db engines supported by MDB and MDB2.

Since it's a 1:1 port of DB_QueryTool, it has the same API, the only difference being the class name (and the constructor name, of course). Unfortunately, complete documentation is not available at the moment.

Example 1 - Arrays

The best way to use MDB_QueryTool is creating a class that extends it. Here's a sample usage:

<?php
require_once 'MDB/QueryTool.php';
define('TABLE_CARS', 'cars');
$dsn = 'mysql://user:pass@host/dbname';

/**
 * Let's suppose the "car" table has the following fields:
 * (id, model, hp, color, clima, price)
 */
class Car extends MDB_QueryTool
{
    var $table        = TABLE_CARS;
    var $sequenceName = TABLE_CARS;

    // this is default, but to demonstrate it here ...
    var $primaryCol =   'id';

    /**
     * This table spec assigns a short name to a table name
     * this short name is needed in case the table name changes
     * i.e. when u put the application on a provider's db, where you have to
     * prefix each table, and you dont need to change the entire application to
     * where you refer to joined table columns, for that joined results the
     * short name is used instead of the table name
     */
    var $tableSpec = array(
        array('name'  => TABLE_CARS, 'shortName' =>  'cars'),
        //array('name'  => TABLE_TIME, 'shortName' =>  'time'),
    );
}


//instanciate an object of the Car class
$car = new Car($dsn);

//get the car #3
$car->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $car->get(3);
var_dump($res);

//get all the cars
$car->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $car->getAll();
var_dump($res);

// get the first 10 cars
$car->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $car->getAll(0, 10);
var_dump($res);

//get all the red cars with clima, sorted by price
$car->reset();
$car->setWhere('color="red"');
$car->addWhere('clima=1');
$car->setOrder('price');
$res = $car->getAll();
var_dump($res);

//add a new car to the database
$data = array(
    'model' => 'Super Trooper',
    'hp'    => 140,
    'color' => 'black',
    'clima' => 0,
    'price' => 19000
);
$newCarId = $car->save($data);
var_dump($newCarId);

//update an existing car
$data = array(
    'id'    => $newCarId,
    'clima' => 1,
    'price' => 20000,
);
$res = $car->save($data);   //equivalent to $car->update($data);
var_dump($res);


//remove the car from the database
$res = $car->remove($newCarId);
var_dump($res);
?>

Example 2 - Classes

MDB_QueryTool also offers working with classes. Here's a sample usage:

<?php
require_once 'MDB/QueryTool.php';
define('TABLE_CARS', 'cars');
$dsn = 'mysql://user:pass@host/dbname';

/**
 * Let's suppose the "car" table has the following fields:
 * (id, model, hp, color, clima, price)
 */
class Car extends MDB_QueryTool
{
    var $table        = TABLE_CARS;
    var $sequenceName = TABLE_CARS;

    // this is default, but to demonstrate it here ...
    var $primaryCol =   'id';

    /**
     * This table spec assigns a short name to a table name
     * this short name is needed in case the table name changes
     * i.e. when u put the application on a provider's db, where you have to
     * prefix each table, and you dont need to change the entire application to
     * where you refer to joined table columns, for that joined results the
     * short name is used instead of the table name
     */
    var $tableSpec = array(
        array('name'  => TABLE_CARS, 'shortName' =>  'cars'),
        //array('name'  => TABLE_TIME, 'shortName' =>  'time'),
    );
}


//instanciate an object of the Car class
$car = new Car($dsn);
$car->useResult('object');

//get the car #3
$car->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $car->get(3)->fetchRow();
var_dump($res);

//get all the cars
$car->reset();     // reset the query-builder, so no where, order, etc. are set
$cars = $car->getAll();
while ($res = $cars->getNext()) {
    var_dump($res);
}

// get the first 10 cars
$car->reset();     // reset the query-builder, so no where, order, etc. are set
$cars = $car->getAll(0, 10);
while ($res = $cars->getNext()) {
    var_dump($res);
}

//get all the red cars with clima, sorted by price
$car->reset();
$car->setWhere('color="red"');
$car->addWhere('clima=1');
$car->setOrder('price');
$cars = $car->getAll();
while ($res = $cars->getNext()) {
    var_dump($res);
}

//add a new car to the database
$newCar = $car->newEntity();
$newCar->model = 'Super Trooper';
$newCar->hp    = 140;
$newCar->color = 'black';
$newCar->clima = 0;
$newCar->price = 19000;

$newCarId = $newCar->save();
var_dump($newCarId);

//update an existing car
$car->reset();
$res        = $car->get($newCarId)->fetchRow();
$res->clima = 1;
$res->price = 20000;
$res->save();
var_dump($res);

//remove the car from the database
$car->reset();
$res = $car->get($newCarId)->fetchRow();
var_dump($res->remove());
unset($res);
?>

Object

Object – An introduction to the usage of Objects with MDB_QueryTool

MDB_QueryTool Object Description

It is possible to use objects as result. A comprehensive example may be seen in the intro.

But using objects is not a simple alternative to using arrays as result. It is also possible to register a custom Class to be returned instead of the default MDB_QueryTool_Result_Row. The new resulting class has to be child of the MDB_QueryTool_Result_Row class.

Example 1 - Change the default result class

To change the resulting class the method setReturnClass is used.

<?php
require_once 'MDB/QueryTool.php';
require_once 'MDB/QueryTool/Result/Object.php';
define('TABLE_CARS', 'cars');
$dsn = 'mysql://user:pass@host/dbname';

/**
 * Let's suppose the "cars" table has the following fields:
 * (id, model, hp, color, clima, price)
 */
class Car extends MDB_QueryTool
{
    public $table = TABLE_CARS;
}

class CarEntity extends MDB_QueryTool_Result_Row
{
}

//instanciate an object of the Car class
$car = new Car($dsn);
$car->useResult('object');
$car->setReturnClass('CarEntity');
?>

Controlling class variable access

This can now be used to implement getter and setter and thus regulate the accessability to the values. In order to block the access to the class variables from outside they have to be declared as protected. Declaring them as private would result in also blocking the parent class, which gets the data, of accessing them. Of course when doing so corresponding methods have to be implemented to access the variables again.

Example 2 - Restricting class variable access

To keep the example short only methods for dealing with model, hp and clima have been implemented.

<?php
require_once 'MDB/QueryTool.php';
require_once 'MDB/QueryTool/Result/Object.php';
define('TABLE_CARS', 'cars');
$dsn = 'mysql://user:pass@host/dbname';

/**
 * Let's suppose the "cars" table has the following fields:
 * (id, model, hp, color, clima, price)
 */
class Car extends MDB_QueryTool
{
    public $table = TABLE_CARS;
}

class CarEntity extends MDB_QueryTool_Result_Row
{
    protected $id;
    protected $model;
    protected $hp;
    protected $color;
    protected $clima;
    protected $price;
    public function getModel() {
        return $this->model;
    }
    public function setModel($model) {
        $this->model = $model;
    }
    public function getHp() {
        return $this->hp;
    }
    public function setHp($hp) {
        $this->hp = $hp;
    }
        public function getClima() {
        if ($this->clima) {
            return true;
        } else {
            return false;
        }
    }
    public function setClima($clima) {
        if ($clima) {
            $this->clima = 1;
        } else {
            $this->clima = 0;
        }
    }
}

//instantiate an object of the Car class
$car = new Car($dsn);
$car->useResult('object');
$car->setReturnClass('CarEntity');
?>

Outlook

This example only demonstrates a very basic feature. But it enables for example the implementation of Decorators. Every feature of modern OOP is now possible to implement.

Quick API reference

Quick API reference – A quick overview of the available methods. Please refer to the full API docs for a detailed documentation.

MDB_QueryTool available methods

MDB_QueryTool provides the following methods: