Home / SIDU


    Wiki: Topnew DB helper

    2018-11-05 00:00
    181862   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

    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',
        '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 != now()',
    ]

    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', 'v1', 'v2'],
    ]

    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 'v1' AND 'v2'

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

    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()'],
    
        ['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'],
        ['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') -- ->from(['o' => 'orders'])
    4. ->leftJoin(['alias' => 'table_name'], 'alias.id = o.id')
    5. ->join(['b' => 'table_name'], ['b.id = o.id', 'b.status' => 'Approved'])
    6. ->where('team', ['US', 'CN'])
    7. ->where('product', 'happy products') // no need in array
    8. ->where('user_id', '!=', [1, 2, 3]) // you can do multiple
    9. ->group('ymd', 'team')
    10. ->having('sales', '>', 10000),
    11. ->order('ymd', 'sales' => 'desc'),
    12. ->limit(10)
    13. ->page(20)
    14. ->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();
    

    Back « SQL row sum
    Next » Localhost SIDU ssh connect remote site localhost

    Comments

    Leave a commentEdit comment

    Category