Wiki: CMS DB Helper v5.5.2 - 2017.02.02

Topnew cms_db provides a full list of database functions for PHP PDO only. For code legacy, cms_db(...) === cms_sidu(...) Generally there are 4 groups of functions:

A - General duties:

1. Database connection

  $dbL  = cms_conn($conn); // or cms_db('conn', $conn)
  $conn = [
    'eng'  => 'mysql', // pgsql | sqlite | cubrid
    'host' => 'localhost',
    'user' => 'user',
    'pass' => 'password',
    'db'   => 'database',
    'port' => 3306,   // 5432 etc optional
    'char' => 'utf8', // mysql optional
    'enc'  => 0, // if 1 pass = cms_dec(pass, 1) and need cms_dec plugin
  ];

2. Show database debug message

  cms_debug($show = 'all'); // or cms_db('debug', $show)
  $show = 'all'; // show all SQL msg -- DEFAULT
  $show = 'err'; // show only msg when there is an error
  $show = -1;    // do not show any msg

3. Return last error msg

  $err = cms_err($msg = 0, $dbL = null); // or cms_db('err', $msg, $dbL)
  $msg = 0; // show errNo only
  $msg = 1; // show errMsg
  $dbL = $dbL ?: $pin['dbL']; // use $dbL or global $pin['dbL']

4. Return last inserted auto-increment id or serial

  cms_db('insert', 'tab', $data, 'last_id:id'); // pgsql
  cms_db('insert', 'tab', $data); // mysql
  $id = cms_last_id(); // or cms_db('last_id')

5. Run any SQL

  $res = cms_run($sql = null, $para = [], $pg_insert_id = '');
  // or cms_db('run', $sql, $para, $pg)
  $sql = 'SQL string';
  $para= [':bind' => 'value', ...];
  $sql = [
    'select' => 'col, ...',
    'from'   => 'tab',
    ...
  ]; // see cms_select() for more examples

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

6. INSERT into $tab values $data

  cms_insert($tab, $data, $pg_insert_id);
  // or cms_db('insert', $tab, $data, $pg)
  $data = ['col' => 'value', ...]
  $pg_insert_id = 'last_id:user_id'; // for pgsql only
  $id = cms_last_id();

7. Replace into $tab with $data

  cms_replace($tab, $data); // or cms_db('replace', $tab, $data)

8. DELETE $tab $where

  cms_delete($tab, NULL, $where, $wherePara);
  // or cms_db('delete', $tab, NULL, $where, $para)
  cms_delete($tab, $where, $wherePara);
  // or cms_db('delete', $tab, $where, $para)

9. UPDATE $tab set $data $where

  cms_update($tab, $data, $where, $wherePara);
  // or cms_db('update', $tab, $data, $where, $para)

10. Save

  cms_save($tab, $data, $where, $wherePara);
  // or cms_db('save', $tab, $data, $where, $para)
  // if $where found, UPDATE, else INSERT

C - SELECT:

Any $sql here can be a string of SQL, or array used for cms_select()

11. Get a value

  $val = cms_val($sql = '', $para = ''); // or cms_db('val', $sql, $para)
  eg $name = cms_val('SELECT name FROM user WHERE id=1');
  eg $name = cms_val(['select'=>'name', 'from'=>'user', 'where'=>'id=1']);
  // please see cms_select for more info on $sql is array

12. Get an array of enum

  $enum = cms_enum($sql, $para); // or cms_db('enum', $sql, $para)
  eg $ids = cms_enum('SELECT id FROM user');

13. Get an array of list used for a selection option

  $list = cms_list($sql, $para); // or cms_db('list', $sql, $para)
  eg $list = cms_list('SELECT id, name FROM user');

14. Get one row

  $row = cms_row($sql, $para, $mode = 'ASSOC');
  // or cms_db('row', $sql, $para, $mode)
  $mode = 'ASSOC'; // eg ['key' => 'val', ...]
  $mode = 'NUM';   // eg ['a', 'b', 'c',  ...]
  $mode = 'BOTH';  // get both of above
  $mode = { KEY }  // same as cms_rows($sql, $para, $key)

15. Get all rows

  $rows = cms_rows($sql, $para, $mode = 'ASSOC');
  // or cms_all($sql, $para, $mode)
  // or cms_db('rows', $sql, $para, $mode)
  // or cms_db('all',  $sql, $para, $mode)

  eg $rows = cms_rows('SELECT id,name FROM user');
  $rows = [
    0 => ['id' => 168, 'name' => 'Topnew'],
    1 => ['id' => 258, 'name' => 'Geo']
  ];

  when $mode = {KEY}, $rows will be assoc array of row
  eg $rows = cms_rows('SELECT id,name FROM user', '', 'id');
  $rows = [
    168 => ['id' => 168, 'name' => 'Topnew'],
    258 => ['id' => 258, 'name' => 'Geo']
  ];

16. Get nested array

  $arr = cms_arr($sql, $para, $arrLevel = 0, $all_record = 0);
  // or cms_db('arr{n}', $sql, $para, 0,   $all_record)
  // or cms_db('arr',    $sql, $para, {n}, $all_record)

  eg if $row = [a, b, c, d]
  if level = 3+, $arr: [a][b][c] = d
  if level = 2,  $arr: [a][b] = [c, d]
  if level = 1,  $arr: [a] = [b, c, d]
  if level < 1,  $arr: [a, b, c, d]

  $all_record = 0; // last value will be final on same keys
  // This is useful when SQL group by is hard in some situation.
  $all_record = 1; // if same keys found, value will be in an array

  yyyymm user sale (sample data)
  ----------------
  201701  168 1234
  201701  168 2000
  201702  168 5678
  201702  258 8888

  $sql = 'SELECT yyyymm,user,sale FROM report';
  eg $arr = cms_arr($sql, '', 2);
  // or cms_db('arr2', $sql) or cms_db('arr', $sql, 2)
  $arr[201701][168] = 2000;
  $arr[201702][168] = 5678;
  $arr[201702][258] = 8888;

  eg $arr = cms_arr($sql, '', 2, 1);
  // or cms_db('arr2', $sql, '', 1) or cms_db('arr', $sql, 2, 1)
  $arr[201701][168] = [1234, 2000];
  $arr[201702][168] = [5678];
  $arr[201702][258] = [8888];

D - More SELECT with $sql = [ ]

In section C $sql is string of raw SQL with bind $para. However all of section C functions can have $sql as an array, and no need of $para. Let's start with an example

  1. $sql = [
  2. 'select'=> ['ymd' => 'date(created)', 'team', 'sales' => 'sum(amt)'],
  3. 'from' => 'orders',
  4. 'where' => ['team' => ['US', 'CN'], 'product' => 'iphone6'],
  5. 'group' => ['ymd', 'team'],
  6. 'having'=> [['sales', '>', 10000]],
  7. 'order' => ['ymd', 'sales' => 'desc'],
  8. 'limit' => [10, 'page' => 20],
  9. ]

will return the following SQL:

  1. SELECT date(created) AS ymd, team, sum(amt) AS sales
  2. FROM orders
  3. WHERE team IN (:0, :1)
  4. AND product = :2
  5. GROUP BY ymd, team
  6. HAVING sales > 10000
  7. ORDER BY ymd, sales DESC
  8. LIMIT 10 OFFSET 190
  9. $bind = array(
  10. :0 => US
  11. :1 => CN
  12. :2 => iphone6
  13. );

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' => cms_select(...), 'tab3' => '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', -1 ] ],
    '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',
]

will produce the following SQL:

AND a1 = 1.23
AND a2 = :0
AND a3 = '45.6'
AND a4 IN (1, '2', NULL, :1)
AND a5 IS NULL
AND a6 = :2

Please note that only string + non-numeric value will be binded -- please check latest build for rules, might have changed rules.

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

eg where => [
    ['b1', 1.23],
    ['b2', 'string'],
    ['b3', '45.6'],
    ['b4', [1, '2', null, 'abc']],
    ['b5', null],
    ['b6', 'null'],
]

will produce the following SQL:

AND b1 = 1.23
AND b2 = :3
AND b3 = '45.6'
AND b4 IN (1, '2', NULL, :4)
AND b5 IS NULL
AND b6 = :5

8.4 where => [ [ key, NOT, val, quote ], ... ]

eg where => [
    ['c1', 'not', 1.23],
    ['c2', 'not', 'string'],
    ['c3', 'not', '45.6'],
    ['c4', 'not', [1, '2', null, 'abc']],
    ['c5', 'not', null],
    ['c6', 'not', 'null'],
    ['c7', 'not'],
    ['c8', 'not', 'now()', -1],
]

will produce the following SQL:

AND c1 != 1.23
AND c2 != :6
AND c3 != '45.6'
AND c4 NOT IN (1, '2', NULL, :7)
AND c5 IS NOT NULL
AND c6 != :8
AND c7 IS NOT NULL
AND c8 != now()

not case insensitive, the last parameter -1 means no quotes

8.5 where => [ [ key, (NOT) between, val, val, quote ], ... ]

eg where => [
    ['d1', 'between', 89, 'between-aaa'],
    ['d2', 'not between', 89, 'between-aaa'],
    ['d3', 'not between', 89],
    ['d4', 'between', 'col1', 'col2', -1],
]

will produce the following SQL:

AND d1 BETWEEN 89 AND :9
AND d2 NOT BETWEEN 89 AND :10
AND d3 NOT BETWEEN 89 AND NULL
AND d4 BETWEEN col1 AND col2

8.6 where => [ [ key, operand, val, quote ], ... ]

eg where => [
    ['e1', 'like', 'abc%'],
    ['e2', 'not like', 'abc%'],

    ['f1', '=', 1.23],
    ['f2', '=', 'string'],
    ['f3', '=', '45.6'],
    ['f4', '=', [1, '2', null, 'abc']],
    ['f5', '=', null],
    ['f6', '=', 'null'],
    ['f7', '='],
    ['f8', '=', 'now()', -1],

    ['g1', 'in', 'sub-sql'],
    ['g2', 'in', [1, '2', null, 'abc']],
]

will produce the following SQL:

AND e1 like :11
AND e2 not like :12

AND f1 = 1.23
AND f2 = :13
AND f3 = '45.6'
AND f4 IN (1, '2', NULL, :14)
AND f5 IS NULL
AND f6 = :15
AND f7 IS NULL
AND f8 = now()

AND g1 IN (sub-sql)
AND g2 IN (1, '2', NULL, :16)

8.7 where => [ [ '(not )exist', 'sql' ], [ 'or', where], ... ]

eg where => [
    'exist' => 'sub-sql',
    'not exist' => 'sub-sql',

    ['or', 'or-where'],
    ['or', ['x' => 1, ['y', '>', 2], 'c']],
]

will produce the following SQL:

AND EXISTS (sub-sql)
AND NOT EXISTS (sub-sql)
AND (1 OR or-where)
AND (x = 1 OR y > 2 OR c)

8.8 where => [ more examples ]

eg where => [
    ['a', '>', 'abc', -1],
    ['b', '!=', '456'],
    'some raw-sql'
]

will produce the following SQL:

AND a > abc
AND b != '456'
AND some raw-sql

bank BenSon Bank
Cash Manager

blog Blog Forum
Blog BBS Ticket

chart SVG Chart
PHP SVG Chart

save Page Maker
Page Maker

topnew SIDU DB GUI
Database tool