Skip to content

getting-started.md

Latest commit

 

History

History
252 lines (193 loc) · 10.6 KB

getting-started.md

File metadata and controls

252 lines (193 loc) · 10.6 KB
Raw

Getting Started

Install ZenDB, connect to a database, and run your first queries. By the end of this page you will have selected, inserted, updated, and deleted rows.

Contents:

Installation

Using CMS Builder? ZenDB is already installed and connected; skip ahead to Your First Query.

composer require itools/zendb

Requirements: PHP 8.1+, ext-mysqli, ext-openssl, and MySQL 5.7.32+ or an equivalent MariaDB. Composer also installs SmartArray and SmartString, the two libraries behind ZenDB's result sets and values.

Connecting - DB::connect()

Call DB::connect() once at startup:

use Itools\ZenDB\DB;

DB::connect([
    'hostname' => 'localhost',
    'username' => 'dbuser',
    'password' => 'secret',    // use '' for none
    'database' => 'my_app',
]);

All four config keys are required; a missing one throws RuntimeException with the key name, and a failed connection throws with the MySQL error. Those four are enough for most apps; for all DB::connect() options (timeouts, tablePrefix, SSL, encryption), see Configuration Options at the bottom of this page.

Your First Query - DB::select()

DB::select() returns every matching row; loop the result with foreach and echo fields with property syntax:

$users = DB::select('users', ['status' => 'active']);
// SELECT * FROM `users` WHERE `status` = 'active'

foreach ($users as $user) {
    echo "<li>$user->name from $user->city</li>";  // values HTML-encode themselves
}

The Mental Model

Three rules explain most of the library:

  1. Methods are named after the SQL they run. select() runs a SELECT, insert() runs an INSERT. If you know MySQL, you already know what these methods do, and each example below shows the exact SQL it generates.
  2. Values only enter through placeholders (? or :name). You never quote or escape a value yourself. SQL templates (the SQL strings you pass in) containing inline quotes or standalone numbers are rejected before the query runs, which is what makes injection impossible on the normal path.
  3. Output is HTML-encoded by default. Every value from the database encodes itself when echoed, so XSS protection doesn't depend on anyone remembering htmlspecialchars().

What Queries Return

Results are SmartArrays and the values inside them are SmartStrings, both installed alongside ZenDB. They work like ordinary arrays and strings in foreach, echo, and string interpolation, with more methods available when you want them. Comparisons and math need the raw value; see Getting Raw Values.

$users = DB::select('users', ['status' => 'active']);
// SELECT * FROM `users` WHERE `status` = 'active'

echo count($users);                           // row count
echo $users->column('email')->implode(', ');  // one column, joined

$user = $users->first();
echo $user->joinDate->dateFormat('M j, Y');  // Mar 16, 2026
echo $user->bio->maxChars(100, '...');       // truncated
echo $user->nickname->or('(none)');          // fallback when empty

Working with Results has the full method lists.

Fetching One Row - DB::selectOne()

DB::selectOne() returns the first matching row and adds LIMIT 1 for you.

$user = DB::selectOne('users', ['id' => 1]);
// SELECT * FROM `users` WHERE `id` = 1 LIMIT 1

if ($user->isEmpty()) {
    die("No such user");
}

echo "Name: $user->name";
echo "City: $user->city";

Both select methods also accept SQL conditions with placeholders:

$admins = DB::select('users', "isAdmin = ? AND city = ?", 1, 'Vancouver');
// SELECT * FROM `users` WHERE isAdmin = 1 AND city = 'Vancouver'

Querying Data covers every WHERE form, sorting, and pagination.

Inserting Rows - DB::insert()

DB::insert() takes a table name and column-value pairs, and returns the new auto-increment ID.

$newId = DB::insert('users', [
    'name'    => 'Alice',
    'isAdmin' => 0,
    'status'  => 'active',
    'city'    => 'Toronto',
]);
// INSERT INTO `users` SET `name` = 'Alice', `isAdmin` = 0, `status` = 'active', `city` = 'Toronto'

echo "Created user #$newId";

Updating Rows - DB::update()

DB::update() takes the new values and a WHERE condition, and returns the number of affected rows. The WHERE condition is required: updating without one throws InvalidArgumentException rather than silently rewriting every row.

$affected = DB::update('users',
    ['city' => 'Montreal'],   // columns to set
    ['id'   => $newId],       // WHERE condition
);
// with $newId = 42 this runs: UPDATE `users` SET `city` = 'Montreal' WHERE `id` = 42

// SQL conditions with placeholders work here too
DB::update('users', ['status' => 'inactive'], "city = ? AND isAdmin = ?", 'Vancouver', 0);
// UPDATE `users` SET `status` = 'inactive' WHERE city = 'Vancouver' AND isAdmin = 0

Deleting Rows - DB::delete()

DB::delete() takes a WHERE condition and returns the number of deleted rows. Like update(), the WHERE condition is required.

$deleted = DB::delete('users', ['id' => $newId]);
// with $newId = 42 this runs: DELETE FROM `users` WHERE `id` = 42

DB::delete('users', "status = ? AND city = ?", 'inactive', 'Vancouver');
// DELETE FROM `users` WHERE status = 'inactive' AND city = 'Vancouver'

Getting Raw Values

Encoded output is the default: every value is a SmartString that HTML-encodes itself when echoed. When you need the underlying data instead (for comparisons, math, or a function that expects a plain string), ask for it:

// One field's raw value
$user = DB::selectOne('users', ['id' => 1]);
$name = $user->name->value();   // string as stored in the database

// A whole result as a plain PHP array
$users = DB::select('users', ['status' => 'active'])->toArray();

Working with Results covers the result objects and their methods in full.

Catching Errors

ZenDB fails fast: every error throws an exception rather than returning false. Misuse of the API throws InvalidArgumentException, connection problems throw RuntimeException, and MySQL errors throw mysqli_sql_exception, which extends RuntimeException. Catch \Exception to handle all of them:

try {
    $user = DB::selectOne('users', ['id' => 1]);
    echo "Hello, $user->name!";
} catch (\Exception $e) {
    echo "Database error: " . htmlspecialchars($e->getMessage()); // exception text is a plain string, not a SmartString, so encode it yourself
}

Exception messages state what went wrong and how to fix it, and Troubleshooting lists the common messages with explanations.

Configuration Options

The supported DB::connect() options. Only the first four are required; an unknown key throws InvalidArgumentException, so typos fail at connect time rather than being silently ignored.

Option Type Default Description
Connection
hostname string (required) Database server hostname
username string (required) Database username
password string (required) Database password (use '' for none)
database string (required) Database name (use '' for none)
Query Behavior
tablePrefix string '' Prefix prepended to all table names, e.g. 'app_' makes users query app_users
useSmartJoins bool true Add qualified table.column keys to JOIN results so same-named columns stay separate
useSmartStrings bool true Return values as SmartString objects with auto HTML-encoding
Connection Options
connectTimeout int 3 Connection timeout in seconds
readTimeout int 60 Read timeout in seconds
requireSSL bool false Encrypt the database connection; no certificate verification
versionRequired string '5.7.32' Minimum MySQL version or compatible; connecting to an older server throws
usePhpTimezone bool true Set the MySQL session timezone to PHP's timezone
sqlMode string (next column) STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
databaseAutoCreate bool false Create the database if it does not exist
Advanced
encryptionKey string (none) Encrypt MEDIUMBLOB columns with AES; see Encryption

← Documentation Index | Next: Querying Data →