Wiki: Topnew DB Helper v201808 - 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 = cms_dec(pass, 1) and need cms_dec plugin
  ];
  $db = new \Topnew\Db($conn);

2. Show database debug message

  $show = 'all'; // show all SQL msg
  $show = 'err'; // show only msg when there is an error
  $show = -1;    // do not show any msg
  $db->debug();  // show all SQL msg
  ...
  $db->debug(-1);// turn off debug

3. Run any SQL

  $sql = 'SELECT * FROM tab 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

5. REPLACE INTO $tab with $data

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

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

7. UPDATE $tab SET $data $where

  $db->update($tab, $data, $where);
  $db->update($tab, $data, $where, $param);

8. Save

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

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 users WHERE id = :id';
  $param = ['id' => 158];
  $name = $db->val($sql, $param);
  // OR
  $sql = [
    'select' => 'name',
    'from'   => 'users',
    'where'  => ['id' => 158],
  ];
  $name = $db->val($sql);

12. Get an array of enum

  $sql = 'SELECT id FROM users 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);

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

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

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 users WHERE id = 1');
  // $user = ['id' => 1, 'name' => 'Topnew', 'male' => 1]
  $user = $db->row($sql, '', 'num');
  // $user = [0 => 1, 2 => 'Topnew', 3 => 1];

15. Get all rows

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

  $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);
  // 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

  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. ];
  10. $sql = $db->sql($sql);

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. $param = [
  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' => '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

E - chained methods:

  1. $stats = $db
  2. ->select('ymd' => 'date(created)', 'team', 'sales' => 'sum(amt)'),
  3. ->from('orders AS o')
  4. ->leftJoin(['alias' => 'table_name'], ['alias.id' => 'o.id'])
  5. ->where('team', ['US', 'CN'])
  6. ->where('product', 'happy products') // no need in array
  7. ->where('user_id', '!=', [1, 2, 3]) // you can do multiple
  8. ->group('ymd', 'team')
  9. ->having('sales', '>', 10000),
  10. ->order('ymd', 'sales' => 'desc'),
  11. ->limit(10)
  12. ->page(20)
  13. ->rows();

You can have nested SQL, however each sub-sql have to return before the final, to avoid cached SQL confusing:

$sub_sql = $db
    ->select('id')
    ->from('users')
    ->where('age', '>', 50)
    ->sql();
$rows = $db
    ->select(...)
    ->from(...)
    ->where('id', 'in', $sub_sql)
    ->rows();

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