Home / CMS


    Wiki: Topnew Db Where Docs

    2019-10-11 00:00
    1100   0   9

    Wiki: Howto use Topnew\Db v 2019.10.10

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

    7 - $db->where()

    where() and having() have exactly same behaviour, will accept multiple calls.

    ->where('raw SQL')
    ->where('id', 123)
    ->where('user_id', [123, 456])
    ->where('name', 'Topnew')
    // WHERE raw SQL
    //   AND id = 123
    //   AND user_id IN (123, 456)
    //   AND name = 'Topnew'

    nulls

    ->where(['col', null])
    ->where('col2', null)
    ->where('this is raw SQL')
    ->where('col3', 'null')
    WHERE col IS NULL
      AND col2 IS NULL
      AND this is raw SQL
      AND col3 = 'null'

    IN or = will be auto decided

    ->where('col1', 123)
    ->where('col2', [123, 456])
    ->where('col3', 'IN', '123')
    ->where('col4', 'IN', ['123'])
    ->where('col5', '=', [1, 2])
    ->where('col6', '!=', [3, 4])
    WHERE col1 = 123
      AND col2 IN (123, 456)
      AND col3 = '123'
      AND col4 = '123'
      AND col5 IN (1, 2)
      AND col6 NOT IN (3, 4)

    operators are case IN-sensitive above and below

    ->where('col1', '!=', 123)
    ->where('col2', '!=', [123, 456])
    ->where('col3', '<>', '123')
    ->where('col4', 'NOT', ['123'])
    WHERE col1 != 123
      AND col2 NOT IN (123, 456)
      AND col3 != '123'
      AND col4 != '123'
    ->where('col', 'between', 123, 456)
    ->where('col', 'not between', 1, 2)
    WHERE col BETWEEN 123 AND 456
      AND col NOT BETWEEN 1 AND 2

    like, start, end

    ->where('col', 'like', 'Topnew')
    ->where('col', 'start', 'Topnew')
    ->where('col', 'end', 'Topnew')
    WHERE col LIKE '%Topnew%'
      AND col LIKE 'Topnew%'
      AND col LIKE '%Topnew'

    Other examples

    ->where('exists', 'sub-SQL')
    ->where('exist', 'sub-SQL') // same as above
    ->where('not exists', 'sub-SQL')
    ->where('not exist', 'sub-SQL') // same as above

    OR

    ->where('or', [
        'raw SQL',
        ['col', 123],
        ['col', '!=', [1, 2]],
    ])
    WHERE ...
      AND (
        raw SQL OR
        col = 123 OR
        col NOT IN (1, 2)
      )

    Quotes

    ->where('id', '123')
    ->where('uid', 123)
    ->where('ts', '<', 'now()')
    ->where('name', 'a\'b"')
    ->where('info', 'abc')
    WHERE id = '123' ## quoted
      AND uid = 123 ## not quoted
      AND ts < NOW() ## this is the only one not quoted
      AND name = :name ## binded due to special chars
      AND info = 'abc' ## not binded

    Topnew\Db currently not support nested chained calls, so you need to call ->sql() to return sub-sql BEFORE main sql()

    As each call of sql() will clear the cache while the binded data is kepted until any get method is called eg

    $sub_sql = $db->select('id')->from(users)->where(...)->sql();
    
    $data = $db->select(...)->from(...)->where(...)
      ->where('id', 'in', $sub_sql)
      ->all()

    Alternatively, you can also use ->bind() method

    $data = $db->select(...)->from(...)->where(...)
      ->where('id', 'in', 'SELECT id FROM ... WHERE name = :name')
      ->bind(['name' => 'Topnew']) ## bind a bulk
      ## or ->bindKey('name', 'Topnew') one by one
      ->all()

    Back « Wiki: Topnew DB Docs

    Comments

    Leave a commentEdit comment

    Category