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