Executing queries

Today I will show how to execute queries in LIZ DB.

1. LIZ DB RunQuery API

All non-SELECT statements are executed by calling only one method. The method is:

• public function RunQuery() - executes any valid DDL (CREATE, ALTER, DROP) or DML (INSERT, UPDATE, DELETE) statement
  

INFO
Before invoking this functions, public field Query should be set to valid SQL statement otherwise LIZ DB will throw LIZDBException.
LIZDBException exception will be also thrown if Query will contain SELECT statement.

The returned value differs according to the type of executed statement.
Juxtaposition of types of statements and returned values is as follows:

• INSERT - returns ID of newly created record (if table has an auto increment primary key, otherwise returns null)
• UPDATE/DELETE - returns number of updated/deleted records
• CREATE, ALTER, DROP, GRANT, REVOKE - returns true or false whether the statement was successfully executed

Examples of usage.

Example 1. Inserting new records.

$ids = array();

$chars = "some text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

echo 'First new record id: ' . $ids[0] . "\n";

$chars = "some other text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

echo 'Second new record id: ' . $ids[1] . "\n";

Example 2. Updating and deleting records.

$db->Query = "UPDATE `lizdb_test_table` SET `double_column` = 0.0 WHERE `id` = '{$ids[0]}'";
$updatedRows = $db->RunQuery();

echo 'No of updated rows: ' . $updatedRows . "\n";

$ids = implode(',', $ids);

$db->Query = "DELETE FROM `lizdb_test_table` WHERE `id` IN ({$ids})";
$deletedRows = $db->RunQuery();

echo 'No of deleted rows: ' . $deletedRows . "\n";

Example 3. Executing DDL statements.

$db->Query = "CREATE TABLE `t` (`f` int)";

if ($db->RunQuery()) {
    echo "New table successfully created\n";
}

$db->Query = "DROP TABLE IF EXISTS `t`";

if ($db->RunQuery()) {
    echo "table deleted successfully\n";
}

2. Live examples

Live examples are available at LIZ DB - RunQuery API Examples

First steps with LIZ DB

This article introduces LIZ DB basics.

LIZ DB is very easy to install. All You have to do is simply download the newest version and unzip downloaded archive to Your working directory.

1. Defining LIZDB_PATH constant

First, in order to start working with LIZ DB we need to define LIZDB_PATH that points to the directory in which we have installed (unzipped) our archive.

Example 1. Defining LIZDB_PATH.

// LIZDB_PATH must point to directory where LIZ DB is installed
define('LIZDB_PATH',  '/home/xh/public_html/lizdb_test/LIZDB');

LIZDB_PATH can be also set dynamically, the below code will work
assuming LIZ DB was installed into current directory.

Example 2. Automatic defining LIZDB_PATH.

if (false == is_dir(LIZDB_PATH)) {
define('LIZDB_PATH',  getcwd() . '/LIZDB');
}

Now we're ready to create DB objects and connect to our database.

2. Creating DB object

Currently LIZ DB 2.0 provides implementation only for MySQL5.
We need to include class definition and create its instance.

There is MySQLimp class (imp stands for improved, not implementation) in
LIZDB_PATH . '/classes/MySQLimp.inc.php' file.

MySQLimp's constructor expects associative array that must define following:

• server - name or IP of MySQL server
• user - username for authorisation
• passwd - password for authorisation
• database - database to connect to

All MySQLimp code should be surrounded by try/catch blocks.
There are two types of exceptions that can be thrown during script's execution:

• Exception - all generic PHP5 exceptions
  
• LIZDBException - thrown explicitly by LIZ DB, these exceptions are strictly linked with database errors
  

Example 3. Creating DB objects.

require LIZDB_PATH . '/classes/MySQLimp.inc.php';
// change Your settings!
// or run sql/create_user.sql to create lizdb user
$config = array();
$config['server'] = 'localhost';
$config['user'] = 'lizdb';
$config['passwd'] = 'lizdb';
$config['database'] = 'test';

try {
    $db = new MySQLimp($config);

    $db->CloseConnection();
} catch (LIZDBException $e) {
    echo $e->getMessage() . "\n";
    echo $e->GetQuery();
} catch (Exception $e) {
    echo $e->getMessage();
}

3. Basic fields and methods

There is only one public field in MySQLimp class:

• public $Query - this field stores SQL statement to be executed (any valid MySQL statement!)

There are few methods that execute queries and handle their results, these are:

• public Select() - returns array of records
• public SelectOne() - returns single record
• public SelectColumn() - return array of given column's values
• public RunQuery() - runs INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, and more...
  

Usage of these methods will be covered by upcoming articles.

Example 4. The very basic usage of SelectOne and RunQuery methods.

$db->Query = 'SELECT * FROM `lizdb_test_table` ORDER BY id DESC LIMIT 1';
$record = $db->SelectOne();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$newId = $db->RunQuery();

Stay tuned for more examples!