Wiki: Topnew DB Docs

2019-10-10 00:00
12322 0 10

Wiki: Howto use Topnew\Db v 2019.10.10

Topnew DB is a PHP class to use PDO database library easily.

1 - Quick start

Save Topnew\Db file into somewhere eg:
vendor/topnew/src/Db.php

An example of your applications:

namespace App;

use Topnew\Db;

class TestApp
{
    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 configure should contain something like following:

$config['db'] = [
    'host' => 'localhost',
    'user' => 'db-user',
    'pass' => 'db-pwd',
    'db'   => 'my_db',
    'eng'  => 'mysql', // pgsql, sqlite, mssql, cubrid, etc
    ...
];

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
    '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()

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->all('select * from users');

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

3.1.3 - use Topnew\Db trained methods
$rows = $db->select(...)->from(...)->where(...)->all();

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

3.2 - $db->arr()

This method to get all rows in a nested array. Similiar usage as all() by either direct $sql or via trained Db methods

$sales = $this->db->select('yr, mon, sum(amt) amt')->from('sales')->group(1,2)->arr2();

And sample result will be something like following:

$sales[2019][10] = 123;
$sales[2019][12] = 234;
$sales[2020][01] = 345;
...

arr($sql, $param, $level, $all_record)

It supports unlimitted levels of nested array, however it is always less 1 than the total number of cols in each row. eg above example $db->arr10() will be auto changed to ->arr2() as it has only 3 cols in each row.

Same as above example, if you call $db->arr() or $db->arr1()

The result will be as following:

$sales = [
    2019 => ['mon' => 12, 'amt' => 234],
    2020 => ['mon' => 01, 'amt' => 345],
];

It is in one level of array, and it keeps last value if same key found.

If you want to keep all records if same key occurs, use ->arrXall() instead. eg

Same as above example, if you call $db->arrAll()

The result will be as following:

$sales = [
    2019 => [
        ['mon' => 10, 'amt' => 123],
        ['mon' => 12, 'amt' => 234],
    ],
    2020 => [
        ['mon' => 01, 'amt' => 345],
    ],
];

Aliases or dynamic calls

arr1() == arr(); // default to 1 level of array
arr4() == arr($sql, $param, 4);
arr4All() == arr($sql, $param, 4, true);
arrAll() == arr($sql, $param, 1, true);

Same results:

arr() == enum(); // when there is only 1 col in each row
arr() == list() == lists(); // when there are exactly 2 cols in each row

3.3 - $db->count()

$db->from('users')->count() == SELECT count(*) FROM users;

$db->count('distinct col') == SELECT count(distinct col) ...

3.4 - $db->enum()

$db->select('distinct gender')->from('users')->enum();

Result will be something like:

['Male', 'Female']

And it is exactly same as $db->arr() if there is only one col in SELECT. When there are multiple cols in SELECT, then enum() will only return the first col

3.5 - $db->lists(), $db->list()

No matter how many cols in each row, it will return a col1 => col2 key pair of each row, if there are only 2 cols in each row, it is same as arr()

$db->select('uid, name')->from('users')->list()

list() is alias of lists(), and safe method name for PHP 7.0+

3.6 - $db->row()

Get one row from SELECT

$user = $db->from('users')->where(...)->row();

By default, it will return only one assoc row, eg above result will be something like:

$user = [
    'uid' => 1,
    'name' => 'Topnew',
    'gender' => 'Male',
    ...
];

Same as all(), you can always insert a raw SQL to get a result:

$user = $db->row('SELECT * FROM users WHERE email=:email', ['email'=>'...']);

You can param it to return in other data format:

row() == row('ASSOC') // return assoc row by default
row('NUM') // return numeric row eg $user = [1, 'Topnew', 'Male', ...]
row('BOTH')// return both assoc and numeric rows

$row = $db->row($sql, $binds, 'NUM');
$row = $db->...->row('BOTH');

The following will return multiple rows:

row('ALL ASSOC') == rows() == all() == all('ASSOC')
row('ALL NUM') == rows('NUM') == all('NUM')
row('ALL BOTH') == rows('BOTH') == all('BOTH')

You can also choose which one as a key, and the following is returning multiple rows:

$users = $this->db->select('name,uid')->from('users')->row('uid');

It will return something like:

$users = [
    1 => ['name' => 'Topnew', 'uid' => 1],
    2 => ['name' => 'Geo',    'uid' => 2],
];

row($key) == rows($key) == all($key):

$users = $this->db->select('uid,name')->from('users')->rows('uid');

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

3.7 - $db->val()

Get the value of first col of first row

$name = $db->select('name')->from('users')->where('uid', 1)->val(); // Topnew

OR

$name = $db->val('SELECT name FROM users WHERE uid=1')

4 - Debug

You might need debug SQL on development, by turning it on before any of other methods:

$this->db->debug(); // turn on all debug
$users = $this->db->from('users')->all();
// then all SQL will be shown on screen even there is no error

$this->db->debug(-1); // turn off the debug after you turn it on
$this->db->debug('err'); // only shown any error SQL

$db->err() == return last PDO error code

$db->err(1) == return last PDO error message

5 - INSERT / DELETE / UPDATE

5.1 - $db->insert()

$new_user = ['name' => 'Tom', 'gender' => 'Male', ...];
$uid = $db->insert('users', $new_user);

OR

$uid = $db->data($new_user)->insert('users');

For PostgreSQL you need indicate which serial to return

$uid = $db->insert('users', $new_user, 'uid');

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

5.2 - $db->delete()

$db->where('uid', 12)->delete('users');
// DELETE FROM users WHERE uid = 12

OR via raw SQL

$db->delete('users', 'WHERE uid=:uid', ['uid' => 123])

OR

$db->where(...)->data($data)->delete($table)

You need be careful of ->where() here incase all rows be deleted!

5.3 - $db->update()

$db->where('uid', 12)->update('users', ['name' => 'New name']);
// UPDATE users SET name='New name' WHERE uid = 12

OR via raw SQL

$db->update($table, $data, $where, $bind)

OR

$db->where(...)->data($data)->update($table)

You can also try lazy raw SQL

$db->where(...)->update('users', 'hit=hit+1');

Same as

$db->where(...)->update('users', ['hit=hit+1', 'updated'=>'now()']);

5.4 - $db->save()

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

$db->where(...)->save($table, $data);

OR via raw SQL

$db->save($table, $data, $where, $bind)

If $where is found, update, otherwise insert

5.5 - $db->run()

If you can not find any method matches your need, or you are trying to run a complicated report, you can always run any SQL by this method

$res = $db->run($sql, $bind);

6 - Trained methods of select and where

Let's try an example

$rows = $this->db
    ->select('u.uid, c.name AS category, sum(s.amt) AS ttl')
    ->from('users AS u')
    ->join(['c' => 'cats'], 'u.cat_id = c.cat_id')
    ->leftJoin(['s' => 'sales'], 'u.uid = s.uid')
    ->where('u.uid', '>', 123)
    ->where('u.gender', 'Male')
    ->where('c.cat_root', [123, 456])
    ->where('OR', [
        ['u.name', 'To"*m'],
        ['u.manager', 'like', 'Sally'],
        's.flag = 1',
    ])
    ->group(1,2)
    ->having('ttl', 'between', 1000, 3000)
    ->order('2,3 DESC')
    ->limit(20)
    ->all();

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

SELECT u.uid, c.name AS category, sum(s.amt) AS amt
FROM users AS u
JOIN cats  AS c ON u.cat_id = c.cat_id
LEFT JOIN sales AS s ON u.uid = s.uid
WHERE u.uid > 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

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')

->select(['alias' => 'col_name'])
// SELECT col_name AS alias

// If no ->select() default to SELECT *

->select['distinct', 'col1', 'alias' => 'col2'])
// SELECT DISTINCT col1, col2 AS alias

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

->from('table_name')
->from('users AS u')
->from(['u' => 'users']) // alias => table_name

->table() is alias of ->from()

6.3 - $db->join()

->join('users AS u ON u.id = g.user_id')

->join(['u' => 'users'], 'u.id = g.user_id')

6.4 - $db->leftJoin()

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

->leftJoin('users AS u ON u.id = g.user_id')

->join(['u' => 'users'], 'u.id = g.user_id', 'Left')

->leftJoin('users AS u', 'u.id = g.user_id')

6.4 - $db->rightJoin()

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

->rightJoin('users AS u ON u.id = g.user_id')

->join(['u' => 'users'], 'u.id = g.user_id', 'Right')

->rightJoin('users AS u', 'u.id = g.user_id')

6.5 - $db->group()

->group(1,2,3)
// GROUP BY 1,2,3

->group('col1, col2', 'col3')
// GROUP BY col1, col2, col3

6.6 - $db->having()

Please see ->where() for exactly same usage

6.7 - $db->order()

->order('raw SQL')
// ORDER BY raw SQL

->order(['col1', 'col2' => 'desc'])
// ORDER BY col1, col2 DESC

6.8 - $db->limit(), $db->page()

->limit(10)
// LIMIT 10

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

->limit([10, 'page' => 5])
// LIMIT 10 OFFSET 40 // (5 - 1) * 10 = 40

->limit(10)->page(5) // same as above

6.9 - $db->bindKey()

Sometimes you need manually bind key for raw SQL

->where('user_id in (select id from users where name = :name)')
->bindKey('name', 'Topnew')

->bindKey('name', $name) == ->bindKey(':name', $name)

6.10 - $db->bind()

Sometimes you need bind multiple keys, this method loop the array to call bindKey

->where('user_id in (select id from users where name = :name and email = :email)')
->bind(['name' => 'Topnew', 'email' => 'm@a.com'])

6.11 - $db->sql()

This is a get method, to return the raw SQL of the chained method. And the SQL will be reset after this call

$sql = $db->from('users')->where('id', 1)->sql();
// SELECT * FROM users WHERE id = 1

However and binded data not reset after this method call. In case this SQL is re-used for a more complicated sub-SQL while its binded data is to be used togather with its parent SQL

6.12 - $db->pgno($pgno, $ttl, $max)

This is a quick call to get the result of a page, with total count of data, used for pagination.

$data = $db->from('users')->limit(10)->pgno(2, $ttl, $max)

If total rows in user table = 120, $ttl will return 120, and $max = 120 / pgsize (10) = 12

And $data will be the sub-set of data of LIMIT 10 OFFSET 10

Without ->limit(10), ->pgno() will default pgsize = 15, as it will make no sense to call this method without a pgsize

By default it will return ASSOC arrays of data, if you need other format of data, you might need call the following instead eg:

$data = $db->select('id', 'name')->from('users')->ttl()->arr();
$ttl = $db->ttl;

->ttl() to let the system to keep $ttl after get method eg arr(), and you can get it back via $db->ttl

6.13 - $db->where()

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

Back « Wiki: Topnew Data Docs
Next » Wiki: Topnew Db Where Docs