Skip to main content

ZTeraDB Query Examples

This file contains real-world examples of all major operations in ZTeraDB:

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
  • Complex Filters
  • Sorting
  • Pagination

These examples are written for PHP developers and show the SQL that the ZTeraDB query represents. Adjust table/column names to match your actual schema as needed.

📦 Setup: Create a Connection (Used in All Examples)

use ZTeraDB\Config\ZTeraDBConfig;
use ZTeraDB\Connection\ZTeraDBConnection;
use ZTeraDB\Query\ZTeraDBQuery;

$config = new ZTeraDBConfig(json_decode(getenv('ZTERADB_CONFIG'), true));

$db = new ZTeraDBConnection($config, getenv('ZTERADB_HOST'), (int)getenv('ZTERADB_PORT'));

1️⃣ INSERT Examples

✔ Insert a User​

$query = (new ZTeraDBQuery('user'))
  ->insert()
  ->fields([
    'email' => 'john@example.com',
    'password' => 'hashed_pw',
    'status' => true
  ]);

$result = $db->run($query);

echo 'Inserted ID: ' . $result['last_insert_id'] . PHP_EOL;

Equivalent SQL

INSERT INTO "user" (email, password, status)
VALUES ('john@example.com', 'hashed_pw', TRUE);

2️⃣ SELECT Examples

✔ Select All Users​

$query = (new ZTeraDBQuery('user'))->select();

$users = $db->run($query);

foreach ($users as $row) {
  print_r($row);
}

Equivalent SQL

SELECT * FROM "user";

✔ Select Users Where Status = True​

$query = (new ZTeraDBQuery('user'))
  ->select()
  ->filter(['status' => true]);

$users = $db->run($query);

Equivalent SQL

SELECT * FROM "user"
WHERE status = TRUE;

✔ Select Only Email Field​

$query = (new ZTeraDBQuery('user'))
  ->select()
  ->fields(['email' => 1]);

$users = $db->run($query);

Equivalent SQL

SELECT email FROM "user";

✔ Limit Results​

$query = (new ZTeraDBQuery('user'))
  ->select()
  ->limit(0, 10);   // first 10 rows

$users = $db->run($query);

Equivalent SQL

-- limit(start, count) where start=0, count=10
SELECT * FROM "user"
LIMIT 10 OFFSET 0;

3️⃣ SELECT with Advanced Filters

✔ Get Products Where price * quantity > 500​

$condition = ZTGT([
  ZTMUL(['price', 'quantity']),
  500
]);

$query = (new ZTeraDBQuery('product'))
  ->select()
  ->filterCondition($condition);

$rows = $db->run($query);

Equivalent SQL

SELECT * FROM product
WHERE (price * quantity) > 500;

✔ Find Users with name containing "john" (case-insensitive)​

$condition = ZTICONTAINS('name', 'john');

$query = (new ZTeraDBQuery('user'))
  ->select()
  ->filterCondition($condition);

$rows = $db->run($query);

Equivalent SQL

-- PostgreSQL example (case-insensitive)
SELECT * FROM "user"
WHERE LOWER(name) LIKE '%john%';

(On MySQL use LOWER(name) LIKE '%john%', or name LIKE '%john%' COLLATE utf8_general_ci depending on collation.)

4️⃣ UPDATE Examples

✔ Update User Status​

$query = (new ZTeraDBQuery('user'))
  ->update()
  ->fields(['status' => false])
  ->filter(['id' => 1]);

$result = $db->run($query);

echo 'Updated: ' . ($result['is_updated'] ? 'true' : 'false') . PHP_EOL;

Equivalent SQL

UPDATE "user"
SET status = FALSE
WHERE id = 1;

5️⃣ DELETE Examples

✔ Delete a Product by ID​

$query = (new ZTeraDBQuery('product'))
  ->delete()
  ->filter(['id' => 'PRODUCT_ID']);

$result = $db->run($query);

echo 'Deleted: ' . ($result['is_deleted'] ? 'true' : 'false') . PHP_EOL;

Equivalent SQL

DELETE FROM product
WHERE id = 'PRODUCT_ID';

6️⃣ Example with Related Fields (Join-like)

Get all orders where the related user's status = true.

$userFilter = (new ZTeraDBQuery('user'))
  ->select()
  ->filter(['status' => true]);

$query = (new ZTeraDBQuery('order'))
  ->select()
  ->relatedFields([
    'user' => $userFilter
  ]);

$rows = $db->run($query);

Equivalent SQL (example mapping)

-- Assuming `order.user_id` references `user.id`
SELECT o.*, u.*
FROM "order" o
JOIN "user" u ON o.user_id = u.id
WHERE u.status = TRUE;

(Adjust field names to match your schema — relatedFields maps to joins between FK columns.)

7️⃣ Sorting Examples

✔ Sort Products by Price ASC​

$query = (new ZTeraDBQuery('product'))
  ->select()
  ->sort(['price' => 1]);

$rows = $db->run($query);

Equivalent SQL

SELECT * FROM product
ORDER BY price ASC;

✔ Sort by Multiple Fields​

$query = (new ZTeraDBQuery('product'))
  ->select()
  ->sort([
    'price' => 1,
    'quantity' => -1
  ]);

$rows = $db->run($query);

Equivalent SQL

SELECT * FROM product
ORDER BY price ASC, quantity DESC;

8️⃣ Counting Rows

$query = (new ZTeraDBQuery('product'))->count();

$result = $db->run($query);

echo 'Count: ' . $result['count'] . PHP_EOL;

Equivalent SQL

SELECT COUNT(*) AS count FROM product;

9️⃣ Full Example: Combined Query

Get available products where quantity > 10, sorted by price, first 20 rows.

$condition = ZTGT(['quantity', 10]);

$query = (new ZTeraDBQuery('product'))
  ->select()
  ->fields(['name' => 1, 'price' => 1, 'quantity' => 1])
  ->filterCondition($condition)
  ->filter(['status' => 'A'])
  ->sort(['price' => 1])
  ->limit(0, 20);

$rows = $db->run($query);

Equivalent SQL

SELECT name, price, quantity
FROM product
WHERE quantity > 10
  AND status = 'A'
ORDER BY price ASC
LIMIT 20 OFFSET 0;

🎉 End of Examples

You now have SQL equivalents for every major PHP ZTeraDB query pattern shown above!
Next:
👉 Check Quick Start for a 10-minute onboarding guide.