PHP interface

Once you have created a list of queries you can easily use them in your PHP project. In this section we will show you how to do that. Please read the get started section first if you haven't done so.

Hello World

Suppose you have created a query called 'helloWorld', you can call the query as follows:

// Include the libs
// Note: only needed if you have installed the client-libs manually
// If you used Composer you can skip this line
require_once("[path-to-libs]/TinyQueries/libs/DB.php");

// Create database object
$db = new TinyQueries\DB();

// Connect to database
$db->connect();

// Fetch the result of a query
$result = $db->get("helloWorld");

var_dump( $result );

$result will contain an array of associative arrays, representing the tabular output coming from the database.

DB class

The DB class is a PDO based database layer. You can either choose to use your own PDO object and attach it to DB or you can choose to let the DB class create one for you. The first option might be handy in case you already have another database framework and you want to use TinyQueries in conjunction with it. The second option is handy in case you don't use another framework.

Use existing PDO object

If you already have a PDO object it is straightforward to connect it to the DB object. You can do that as follows:


// Create database object using existing PDO
$db = new TinyQueries\DB( $myPDO );

// Just start using your queries
$result = $db->get("myQuery");

Create new PDO object

If you don't have a PDO object and you want to let it be created by DB, you should first set your database credentials in the file config.xml. See get started for details. Then you can just do this:


// Create database object 
$db = new TinyQueries\DB();

// Connect to the databse
$db->connect();

// Just start using your queries
$result = $db->get("myQuery");

DB::get

As you already have seen in the previous examples you can fetch the result of a query by calling the get method. It has two arguments: queryID and queryParams. The first one is mandatory, the second one is only mandatory if the query has parameters which have no default value. Below you can see some examples.


// Fetch result without parameters
$result = $db->get("myQuery");

// Fetch result if query has one parameter
$result = $db->get("myQuery", $paramValue);

// Fetch result if query has more than one parameter
$result = $db->get("myQuery", array(
	'paramName1' => $paramValue1,
	'paramName2' => $paramValue2,
	...
));

DB::get1

By default query will return an array of associative arrays, representing the rows coming from the database. If you want the first row only you can use get1


// Get user with id = 20
$user = $db->get1("users", 20);

In this example $user will be an associative array containing the fields from the first record.

DB::insert

You can insert a record by calling insert and supplying the record as an associative array


// Insert a user record and get new ID
$userID = $db->insert("users", array(
	'first_name' => '...',
	'last_name'  => '...',
	'email'      => '...'
));

DB::update

You can update a record by calling update and supplying the record ID and the fields to be updated as an associative array.

NOTE: it is assumed that records have a field 'id' which is the primary key. If this is not the case for your database, you can instead pass an associative array with the primary key name and value


// Update record with id = 20
$db->update("users", 20, array(
	'first_name' => '...',
	'last_name'  => '...',
	'email'      => '...'
));

DB::save

Many times you need to either insert or update a record, depending on wether a record already exists. You can use save for that. It tries to insert first and if that doesn't work it does an update instead.

NOTE: currently only available for MySQL


// Either update or insert a record, depending on wether a record already exists
$db->save("users", array(
	'id'         => 20,
	'first_name' => '...',
	'last_name'  => '...',
	'email'      => '...'
));

DB::delete

To delete a record you can use delete.

NOTE: it is assumed that records have a field 'id' which is the primary key. If this is not the case for your database, you can instead pass an associative array with the primary key name and value


// Delete record with id = 20
$db->delete("users", 20);

DB::execute

If you, despite all efforts by TinyQueries to create SQL for you, still want to do your own SQL you can use execute :)


// Execute a SQL query
$db->execute($sql, $params);