Home / CMS

---

Wiki: Topnew Db Where Docs

---

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
Next » Topnew CSS v 2023

Comments