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