Wiki: Howto use Topnew\Db\Db v 2025.01.01

Topnew\DB\Db is a lightweight PHP wrapper around PDO that simplifies database queries.

1 - Quick start

Save the Db.php file somewhere in your project, for example:
vendor/topnew/db/src/Db.php

Example usage

namespace App;

use Topnew\Db\Db;

class TestApp
{
    protected $db;
    public function __construct() {
        $this->db = Db::make($this->config['db']);
        ...
    }
    public function getUser($uid) {
        return $this->db->from('users')->where('uid', $uid)->row();
    }
}

Your database configuration should look like this:

$config['db'] = [
    'host' => 'localhost',
    'user' => 'db-user',
    'pass' => 'db-pwd',
    'db'   => 'my_db',
    'eng'  => 'mysql', // pgsql, sqlite, mssql, etc
    'mode' => 'ASSOC', // OBJ: default data type in return
    ...
];

Helper Functions (Optional)

If you are using Topnew CMS, these helpers are already included. If not, you can define them yourself:

function db($config = null) { return clone make('Topnew.Db.Db', $config); }

function table($t) { return db()->table($t); }

Basic query:

$user = db()->table('user')->where('id', $id)->row();

Or using shortcut:

$user = table('user')->where('id', $id)->row();

Or even:

$user = table('user')->find($id); // return as an array or object

Above is nearly similar to

$user = repo('user')->find($id); // return as a model with additional model methods and fk relationships

So that you can do additional eg

$companyName = $user->company->name; // this only works with model, not db()

2 - Database connection

Topnew\Db works for most of the major databases, eg MySQL, PostgreSQL, SQLite, MS-SQL etc via PHP PDO class

As you see the config in above example, the following is a full list of config supported:

$config['db'] = [
    'host' => 'localhost',
    'user' => 'db-user',
    'pass' => 'db-pwd',
    'db'   => 'my_db',
    'eng'  => 'mysql', // pgsql, sqlite, mssql, cubrid, etc
    'mode' => 'ASSOC', // OBJ: default data type in return
    'char' => 'utf8',
    'port' => 3306,
    'enc'  => 0,    // if 1 need Topnew\Auth::dec()
    'log'  => '',   // log table for SQL err or slow SQL eg log_err
    'slow' => 1000, // SQL slower than this will be logged
];

You can also encryt password in the config file as following:

$config['db'] = [
    ....
    'pass' => 'aC4r@xd._e.......',
    'enc'  => 1
];

Then you need Topnew\Auth plugin to be able to decrypt the password

3 - Methods to get data

3.1 - $db->all($mode = '')

This method to get all rows from a select SQL. eg select * from users

You can use it in several ways though:

3.1.1 - direct input SQL eg
$rows = $db->sql('select * from users')->all();

3.1.2 - direct input SQL with variable to bind
$sql = 'select * from users where name = :name';
$rows = $db->sql($sql, ['name' => 'John'])->all();

3.1.3 - use trained methods
$rows = $db->from('user')->where('name', 'John')->all();

3.1.4 - this method has an alias = $db->rows()

3.1.5 - customize return data type

$rows = $db->all(); // Default (eg assoc array)

$rows = $db->all('obj'); // return array of objects

$mode = assoc | num | both | obj

3.2 - $db->arr($level = 1, $all = 0)

This method returns all rows as a nested array, based on selected columns. Its usage is similar to $db->all(), but it does not support $mode.

$sales = $db->select('y, m, amt')->from('sales')->arr();

Sample result:

$sales = [
    2025 => ['m' => 12, 'amt' => 168],
    2026 => ['m' => 10, 'amt' => 555],
];

First column y becomes the key
Remaining columns form the value

If $all = 1, rows with the same key will be grouped into an array

$sales = table('sales')->select('y, m, amt')->arr(1, 1);

Sample result:

$sales = [
    2025 => [
        ['m' => 11, 'amt' => 100],
        ['m' => 12, 'amt' => 168],
    ],
    2026 => [
        ['m' => 09, 'amt' => 105],
        ['m' => 10, 'amt' => 555],
    ],
];

You can increase $level to create deeper nested structures. Max $level = (number of selected columns − 1)

$sales = $db->select('y, m, amt')->from('sales')->arr2(); // or ->arr(2)

Sample result:

$sales = [
    2025 => [
        11 => 100,
        12 => 168,
    ],
    2026 => [
        09 => 105,
        10 => 555,
    ],
];

y → first-level key
m → second-level key
amt → final value

Convenience methods are available:

->arr2($all) == ->arr(2, $all)
->arr3($all) == ->arr(3, $all)
->arr4($all) == ->arr(4, $all)
->arr5($all) == ->arr(5, $all)

3.3 - $db->col($col = '')

$names = table('user')->col('name');

SQL
SELECT name FROM user;

Sample Result
$names = ['John', 'Lucas', ...]

If you do not indicate $col, it will default to the first col in the row

$uids = table('user')->col();

Backend SQL will select * if there is no ->select()
SELECT * FROM user;

Sample Result
$uids = [11, 17, 32, 48]
//Typically, the first col in a table like user is id, so the result often contains IDs.

Although this works, it is best practice to explicitly specify the column to ensure predictable results:

$uids = table('user')->select('id')->col();

3.4 - $db->count($col = '*')

table('user')->count() == SELECT count(*) FROM users;

table('user')->count('distinct name') == SELECT count(distinct name) ...

3.5 - $db->find($v, $k = '')

This method retrieves the first matching row based on a key-value condition. It automatically applies a LIMIT 1.

$user = table('user')->find(48);

same as
SELECT * FROM user WHERE id = 48 LIMIT 1;

If $k is not specified, it defaults to 'id'

$user = table('user')->find('John', 'name');

same as
SELECT * FROM user WHERE name = 'John' LIMIT 1;

3.6 - $db->first($mode = '')

$user = table('user')->first();

This is just a quick hand for:

$user = table('user')->limit(1)->skip(0)->row($mode);

We will show more details in ->row()

Three alias

->get()  == ->all() // Familiar for developers coming from Laravel
->rows() == ->all() // Alternative name; same behavior as all()
->var()  == ->val() // Helps if you forget whether to use val() or var()

3.7 - $db->val()

This method retrieves a single scalar value: the first column of the first row from a query.

$name = table('user')->where('id', 32)->select('name, age')->val();

SQL
SELECT name, age FROM user WHERE id = 32

3.8 - $db->list()

No matter how many cols in each row, it will return a col1 => col2 key pair of each row. i.e. any cols after 2nd will be ignored

$users->table('user')->select('id, name')->list();

Sample result:
$users = [
    11 => 'Namo',
    17 => 'Amitabha',
    ...
];

3.9 - $db->pgno(&$pgno = 1, &$ttl = 0, &$max = 0)

This method is used for pagination in CMS or any application that requires page-based results.

Automatically validates and fixes the current page number ($pgno) if it’s invalid

Returns total rows ($ttl) and maximum pages ($max) based on the current page size set via ->limit($pgsize)

$users = table('user')->pgno($pgno, $ttl, $max);

SQL
SELECT * FROM user WHERE ... LIMIT 15 OFFSET 0

When no pgsize passed in, default to 15 per page

3.10 - $db->row($mode = '')

Get one row from SELECT

$user = table('users')->row();

Sample result

$user = [
    'id' => 1117,
    'name' => 'Namo Amitabha',
    ...
];

You can fetch all rows directly from ->row() using:

->row('all num')   == ->all('num')
->row('all both')  == ->all('both')
->row('all assoc') == ->all('assoc')
->row('all obj')   == ->all('obj')

You can pass a column name as $mode to key the result by that column:

$user = table('user')->row('id');

Sample result:
$user = [
    11 => ['id' => 11, 'name' => 'John', ...],
    ...
]

The key is the column value (here id)
The value preserves the column in the row (unlike ->arr() which removes it)

eg result of ->arr()

$user = [
    11 => ['name' => 'John', ...],
    ...
]

Any $mode not recognized as num, assoc, both, obj, or all ... is treated as a key column
->row($key) === ->all($key) === ->rows($key)

The difference between row($key) and arr() is that:
arr() is nested by first col, $key in row() can be any col
arr() the first col is removed from array, row() key-col also kept in the result

4.1 - $db->debug($show = 'all')

This method is used to debug SQL queries during development.

It allows you to see the generated SQL and optionally errors, which is very helpful for troubleshooting.

$users = table('users')->where('id', 11)->debug()->row();

SQL will be displayed on screen before execution
Useful for identifying errors or unexpected query results

->debug() // default show all SQL even no error
->debug('err') // show errors only
->debug(-1) // turn off debug if previously or globally turned on by $config

4.2 - $db->run($sql = '', $bind = [])

In case you have a raw SQL to run, you can do:

$db->run('INSERT INTO user SELECT * FROM temp WHERE id = :id', ['id' => 48]);

->run() // Execute raw SQL, no fetch, return PDOStatement

If you want to run a raw SQL and expect to return something like row() etc do this:

$user = $db->sql($sql, $bind)->row();

4.3 - $db->bind()

This method is used to manually bind variables to a query, usually for a WHERE clause or any prepared statement.

->sql('SELECT * FROM user WHERE name = ? AND age = ? AND gender = ?')
->bind($a, $b, $c) // same as
->bind([$a, $b, $c])

However you usually do:
->where('id = :id AND name = :name')
->bind(['id' => 11, 'name' => 'John']);

->bind([':id' => 11]) // same as ->bind(['id' => 11])

4.4 - $db->toSql()

Purpose: Returns the SQL query string that has been built, without executing it.

Useful for debugging or logging what SQL will actually run.

$sql = table('users')->where('id', 11)->toSql();

echo $sql;

4.5 - $db->mode($mode) -- TODO

This method is intentionally not implemented yet, as it is rarely needed.

The default return data type should be configured globally via $conn['mode']. For most use cases, a per-call override is already supported:

->row('assoc')
->get('obj')
->first('num')
... etc

If needed in the future, this method would act as a temporary override for the current query chain only (no global side effects), e.g.:

$sales = table('sales')->select('y,m,amt,num')->mode('assoc')->arr2();

5 - INSERT / DELETE / UPDATE

5.1 - $db->insert()

$user = ['name' => 'Tom', 'gender' => 'Male', ...];

// Recommended usage:
$id = table('user')->insert($user);

// Alternative forms (less common, optional):
$id = table('user')->data($user)->insert();
$id = db()->insert('user', $user);
$id = db()->data($user)->insert('user');

// All produce the same result. Use the first form for clarity.

For PostgreSQL you need indicate which serial to return

$id = table('user')->returning('id')->insert($user);

Also $db->replace($table, $data)

// same usage as ->insert()
// Recommended usage:
table('user')->replace($user);

5.2 - $db->delete()

table('user')->where('id', 12)->delete();
// DELETE FROM user WHERE id = 12

Always use where() to avoid deleting all rows accidentally!

5.3 - $db->update()

table('user')->where('id', 12)->update(['name' => 'New name']);
// UPDATE user SET name='New name' WHERE id = 12

table('user')->where('id', 12)->update('hit = hit + 1');
table('user')->where('id', 12)->update(['hit = hit + 1', 'a' => 123]);

5.4 - $db->save()

Perform an insert or update depending on whether a matching record exists.

This is a lazy call of insert() or update() based on $where

$data = ['id' => 12, 'name' => 'John'];

// Update if id=12 exists, otherwise insert a new record
table('user')->where('id', 12)->save($data);

// Repeat key in $data if used in where()
// If where matches multiple result, multiple rows will be updated

6 - Trained methods of select and where

You can chain multiple methods to build complex queries. Methods can generally appear in any order except the final method, which must be a data-fetching method (get(), all(), row(), etc.).

$rows = $db
    ->select('u.id, c.name AS cat, sum(s.amt) AS ttl')
    ->from('user AS u')
    ->join('cat AS c', 'u.cat_id = c.id')
    ->leftJoin('sale AS s'], 'u.id = s.user_id')
    ->where('u.id', '>', 123)
    ->where('u.gender', 'Male')
    ->where('c.cat_root', [123, 456])
    ->orWhere([
        ['u.name', 'T*om'],
        ['u.manager', 'like', 'Sally'],
        's.flag = 1',
    ])
    ->group(1,2)
    ->having('ttl', 'between', 1000, 3000)
    ->order('2,3 DESC')
    ->limit(20)
    ->all()

SELECT u.id, c.name AS cat, sum(s.amt) AS amt
FROM user AS u
JOIN cat  AS c ON u.cat_id = c.id
LEFT JOIN sale AS s ON u.d = s.user_id
WHERE u.id > 123
  AND u.gender = 'Male'
  AND c.cat_root IN (123, 456)
  AND (
    u.name = :1 OR
    u.manager like '%Sally%' OR
    s.flag = 1
  )
GROUP BY 1,2
HAVING ttl BETWEEN 1000 AND 3000
ORDER BY 2,3 DESC
LIMIT 20

Any methods can be in any order, except the last method has to be the get data method eg rows() etc

Please note that:
join / leftJoin / rightJoin / where / having -- accept multple calls;
any other method will use the last call, eg if you have:
$db->select('abc')->...->select('*')
The final SELECT will be *

Any of the chained method can be raw SQL, or params explained as following:

6.1 - $db->select()

->select('any raw SQL text here')

// If no ->select() default to SELECT *
table('user')->all() // SELECT * FROM user

6.2 - $db->from(), $db->table()

$db->from('table_name')
$db->from('user AS u')
$db->table('user u')

// For brevity and readability, prefer the global table() helper:
table('user as u')->...

6.3 - $db->join()

$db->join('user AS u ON u.id = g.user_id') // raw SQL

$db->join('user u', 'u.id = g.user_id')

6.4 - $db->leftJoin()

->leftJoin($tab, $on) == ->join($tab, $on, 'left')

->leftJoin('user AS u ON u.id = g.user_id')
->join('user u', 'u.id = g.user_id', 'left')
->leftJoin('user AS u', 'u.id = g.user_id')

6.4 - $db->rightJoin()

->rightJoin($tab, $on) == ->join($tab, $on, 'right')

->rightJoin('user AS u ON u.id = g.user_id')
->join('user u', 'u.id = g.user_id', 'right')
->rightJoin('user AS u', 'u.id = g.user_id')

6.5 - $db->group() / $db->groupBy()

->group('1,2,3') // GROUP BY 1,2,3
->groupBy('a, b, c') // GROUP BY a, b, c

// group() and groupBy() are equivalent; use whichever reads better in your context.
// Only the last call overrides previous calls:

6.6 - $db->having()

Please see ->where() for exactly same usage

6.7 - $db->order() / $db->orderBy()

->orderBy('a, b desc') // ORDER BY a, b DESC

6.8 - $db->limit(), $db->page(), $db->take(), $db->offset(), $db->skip()

Control the number of rows returned (LIMIT) and the starting point (OFFSET).

->limit(10) // LIMIT 10

->limit(10, 20) // LIMIT 10 OFFSET 20

->take(10) // LIMIT 10

->skip(20) // OFFSET 20

->take(10)->page(4) // LIMIT 10 OFFSET 30 <-- page # 4

6.9 - $db->where()

Let's talk about the ->where in another wiki, as this wiki is getting too long now.