Wiki: Topnew DB helper

2018-11-05 00:00
183292 0 1
CMS, Wiki

Wiki: Topnew DB Helper v 2018.08.08

Topnew DB provides a full list of database functions for PHP PDO only. Generally there are 5 groups of functions:

A - General duties:

1. Database connection

  $conn = [
    'eng'  => 'mysql', // pgsql | sqlite | mssql
    'host' => 'localhost',
    'user' => 'user',
    'pass' => 'password',
    'db'   => 'database',
    'port' => 3306,   // 5432 etc optional
    'char' => 'utf8', // mysql optional
    'enc'  => 0, // if 1 pass = Auth::dec(pass) and need Auth plugin
  ];
  $db = new \Topnew\Db($conn);

2. Show database debug message

  $db->debug();  // show all SQL msg
  ...
  $db->debug('err'); // show only err msg
  $db->debug(-1);// turn off debug

3. Run any SQL

  $sql = 'SELECT * FROM user WHERE id = :id';
  $param = ['id' => 123];
  $res = $db->run($sql, $param);

B - INSERT / DELETE / UPDATE / Replace / Save:

4. INSERT into $tab VALUES $data

  $tab = 'users';
  $data = ['name' => 'Topnew', 'male' => 1];
  $id = $db->insert($tab, $data); // return auto_increment PK
  // pgsql you need to indicate which id to return:
  $id = $db->insert($pgsql_tab, $data, 'user_id'); // return pg serial id
  // or $id = $db->table($tab)->data($data)->insert();
  // or $id = $db->data($data)->insert($tab);

5. REPLACE INTO $tab with $data

  $db->replace($tab, $data);
  $db->table($tab)->data($data)->replace();
  $db->data($data)->replace($tab);

6. DELETE $tab $where

  $where = ['id' => 123];
  $db->delete($tab, $where);
  // OR plain SQL way:
  $where = 'WHERE id = :id';
  $param = ['id' => 123];
  $db->delete($tab, $where, $param);
  // or $db->table($tab)->where('id', 123)->delete();
  // or $db->where('id', 123)->delete($tab);

7. UPDATE $tab SET $data $where

  $db->update($tab, $data, $where);
  $db->update($tab, $data, $where, $param);
  $db->table($tab)->data($data)->where('id', 123)->update();
  $db->data($data)->where('id', 123)->update($tab);
  $db->where('id', 123)->update($tab, $data);

8. Save

  $id = $db->save($tab, $data, $where[, $param]);
  // if $where found, UPDATE, else INSERT
  // if update there will be no return of $id
  $db->table($tab)->data($data)->where('id', 123)->save();
  $db->data($data)->where('id', 123)->save($tab);
  $db->where('id', 123)->save($tab, $data);

C - SELECT:

Any $sql here can be a string of SQL, or array used for $db->sql() Also $param is optional.

11. Get a value

  $sql = 'SELECT name FROM user WHERE id = :id';
  $param = ['id' => 158];
  $name = $db->val($sql, $param);
  // OR
  $sql = [
    'select' => 'name',
    'from'   => 'users',
    'where'  => ['id' => 158],
  ];
  $name = $db->val($sql);
  $name = $db->select('name')->from('user')->where('id', 158)->val();

11a. Count

  $num = $db->table('user')->where(...)->count(); // default count(*)
  $num = $db->table('user')->count('distinct name');
  $num = $db ... ->count(1); // id, etc

12. Get an array of enum

  $sql = 'SELECT id FROM user WHERE male = :male';
  $param = ['male' => 1];
  $ids = $db->enum($sql, $param);
  // When there is only field in select, it is same as:
  $ids = $db->arr($sql, $param);
  $ids = $db->select('id')->from('user')->where('male', 1)->enum();

13. Get an array of list used for an HTML select option

  $sql   = 'SELECT id, name FROM user WHERE ...';
  $param = [ ... ];
  $list  = $db->list($sql, $para);
  // When there is exact 2 fields in select, it is same as:
  $list  = $db->arr($sql, $param);
  $list = $db->select('id', 'name')->from('user')->where(...)->list();

14. Get one row

  $mode = 'ASSOC'; // eg ['key' => 'val', ...] -- Default
  $mode = 'NUM';   // eg ['a', 'b', 'c',  ...]
  $mode = 'BOTH';  // get both of above
  $mode = { KEY }  // same as rows($sql, $param, $key)
  $row  = $db->row($sql, $param);
  $user = $db->row('SELECT * FROM user WHERE id = 1');
  // $user = ['id' => 1, 'name' => 'Topnew', 'male' => 1]
  $user = $db->row($sql, '', 'num');
  // $user = [0 => 1, 2 => 'Topnew', 3 => 1];
  $row = $db->from('user')->where('id', 1)->row();

15. Get all rows

  $sql = 'SELECT * FROM user WHERE male = 1';
  $users = $db->rows($sql);
  // Result will be something like following:
  $users = [
    0 => ['id' => 123, 'name' => ...],
    1 => ['id' => 258, 'name' => ...],
    ...
  ];

  $db->from('user')->where(...)->rows(); // ->rows('num') // rows('id')

  $db->rows(...) == $db->all(...);

  $users = $db->rows($sql, $param, 'id'); // { key }
  // same as $db->row($sql, $param, $key);
  // Result will be something like following:
  $users = [
    123 => ['id' => 123, 'name' => ...],
    258 => ['id' => 258, 'name' => ...],
    ...
  ];

  // compare with arr():
  $users = $db->arr($sql);
  // note that id is missing in the sub-array:
  $users = [
    123 => ['name' => ...],
    258 => ['name' => ...],
    ...
  ]

16. Get nested array

  $sql = 'SELECT y, m, count(*) hit, sum(sales) sales FROM log GROUP BY 1, 2';
  $log = $db->arr($sql);
  $log = $db->select(...)->from(...)->where(...)->arr();
  // 1 level of array: last value will be used for duplicated keys
  $log = [
    2017 => [ // last record of 2017 will be kept
      'm'    => 12,
      'hit'  => 123,
      'sale' => 456,
    ],
    2018 => [
      'm'    => 08,
      'hit'  => 158,
      'sale' => 789,
    ],
  ];

  $log = $db->arr2($sql);
  // 2 levels of array:
  $log = [
    2017 => [
      11 => [
        'hit'  => 100,
        'sale' => 200,
      ],
      12 => [
        'hit'  => 123,
        'sale' => 456,
      ],
    ],
    2018 => [
      05 => [
        'hit'  => 10,
        'sale' => 20,
      ],
      08 => [
        'hit'  => 158,
        'sale' => 789,
      ],
    ],
  ];

  // We can also keep all values for duplicated keys eg arrAll(), arr3All(), arrAll3() etc
  $log = $db->arrAll($sql);
  // result will be something like following:
  $log = [
    2017 => [
      0 => [
        'm'    => 11,
        'hit'  => 100,
        'sale' => 200,
      ],
      1 => [
        'm'    => 12,
        'hit'  => 123,
        'sale' => 456,
      ],
    ],
    2018 => [
      0 => [
        'm'    => 05
        'hit'  => 10,
        'sale' => 20,
      ],
      1 => [
        'm'    => 08,
        'hit'  => 158,
        'sale' => 789,
      ],
    ],
  ];

D - More SELECT with $sql = [ ]

In section C $sql is string of raw SQL with bind $param. $sql can also be an array, and no need of $param. Let's start with an example. However you might feel easier to use trained methods in section E

$sql = [
'select'=> ['ymd' => 'date(created)', 'team', 'sales' => 'sum(amt)'],
'from' => 'orders',
'where' => ['team' => ['US', 'CN'], 'product' => 'iphone6'],
'group' => ['ymd', 'team'],
'having'=> [['sales', '>', 10000]],
'order' => ['ymd', 'sales' => 'desc'],
'limit' => [10, 'page' => 20],
];
$sql = $db->sql($sql);

will return the following SQL:

SELECT date(created) AS ymd, team, sum(amt) AS sales
FROM orders
WHERE team IN (:0, :1)
AND product = :2
GROUP BY ymd, team
HAVING sales > 10000
ORDER BY ymd, sales DESC
LIMIT 10 OFFSET 190
$param = [
:0 => US
:1 => CN
:2 => iphone6
];

1 - field|select => 'string' | array() | null

If field is not set or no value, default to SELECT *

  eg 'field' => 'col1, count(*) AS alias, ...'
  eg 'select'=> ['col1', 'alias' => 'count(*)', ...]

You can have distinct as first element of array() eg:

  eg field => ['distinct', 'col1', 'alias' => 'col2', ...]
  // SELECT DISTINCT col1, col2 AS alias, ...

2 - from => 'string' | [...]

  eg from => 'tab1, tab2 AS alias, ...'
  eg from => ['tab1', 'alias' => 'tab2', ...]
  eg from => ['tab1', 'alias' => 'SELECT col FROM tab5']

3 - join => join1 | [join1, join2, ...]

where join1 = [alias => join_table, [on-where], left | right | inner | full outer]

  eg join => [[tab1, [tab1.col, tab2.col], left], [join2], ...]

Default inner join, check where for details of on-where

Join table can also be a sub-query

4 - group => 'col1, col2, ...' | [col1, col2, ...]

Also support eg group => 'col1, col2, 3, ...'

5 - having => 'where-string' | [where1, where2, ...]

check where for details

6 - order => 'string' | [...]

  eg order => ['col1', 2, 'col3' => 'desc']

7 - limit => int | [int, int] | [int, 'page' => int]

  eg limit => 10;                // LIMIT 10
  eg limit => [10, 20];          // LIMIT 10 OFFSET 20
  eg limit => [10, 'page' => 3]; // LIMIT 10 OFFSET 20

8 - where => 'string' | [...]

8.1 where = 'raw SQL string'

  eg: where => "col = 'val' AND ymd = now()"

In most cases, where is array(where1, where2, where3, ...)

used in where-clause | join-on-condition | having-clause,
either in string, or
array of arrays of condition, even there is only one condition:

  where => [ [ 'col', 'val' ] ]
  having=> [ [ 'count(*)', '>', 100 ] ]
  join  => [
    'alias' => 'tab',
    'user.id = alisa.id',
    'left'
  ]

8.2 where => [ key => val, ... ]

eg where => [
    'a1' => 1.23,
    'a2' => 'string',
    'a3' => '45.6',
    'a4' => [1, '2', null, 'abc'],
    'a5' => null,
    'a6' => 'null',
]