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);
?>