Querying with WDB

// Name the columns. "*" hands your callers whatever the table grows into later.
public static function select($arg = "*");              // a falsy $arg becomes "*"
public static function from($arg = '');                 // "users_products" | "users_products AS t"
public static function join($type, $table, $where);     // $type: "LEFT" | "INNER" | "RIGHT" - pasted before " JOIN "
public static function where($column, $mark = '', $value = '', $logical = '');
public static function group_by($arg = '');             // raw expression, not bound
public static function order_by($arg);                  // raw expression, not bound
public static function limit($arg1, $arg2 = null);      // limit(25) -> LIMIT 0,25 · limit(50, 25) -> LIMIT 50,25
public static function build($isthis = false);          // bool: matched rows > 0 · $isthis = true returns the Database object

// Fetchers read the statement build() left behind, or the one you hand them.
public static function fetch_assoc($statement = false); // list of rows, each column => value; [] when none matched
public static function getAssoc($statement = false);    // one row as column => value; false when there is no row
public static function fetch_object($statement = false);// the same list as stdClass objects
public static function getObject($statement = false);   // one row as stdClass; false when there is no row
public static function rowCounter($statement = false);  // int: rows the last statement touched

// $logical accepts exactly 'AND', '&&', 'OR', '||'. It joins THIS condition to the NEXT
// one; a condition left without it is given AND automatically once the next one arrives.
public static function where($column, $mark = '', $value = '', $logical = '');

// Parentheses: no static wrapper, so it is called on the object the chain returns.
// $logical joins the whole group to the condition that FOLLOWS it, exactly like the
// fourth argument of where(). Leaving it out is safe: the group is recorded as a
// joinable predicate, so the next condition supplies AND by itself. Pass it for OR.
public function whereGroup(callable $callback, string $logical = ''): self;

public static function insert($table, $data);             // int affected rows; throws on an SQL error
public static function lastID();                          // int identifier the insert produced; 0 with no connection
public static function update($table = '', $data = []);   // a non-empty $data calls set() for you
public static function set($data = [], $special = false); // $special = true pastes the value RAW: ['uses' => 'uses+1']
public static function save($isthis = false);             // bool: no error - TRUE also when zero rows matched
public static function delete($arg = '', $arg2 = '');     // delete("t") | delete("a", "a INNER JOIN b ON ...")
public static function run($isthis = false);              // bool: deleted rows > 0

// $data for insert() and update()/set() is column => value. A null value is bound as
// SQL NULL, an int as an integer, everything else as a string.
// limit() applies to save() and run() too, as a row count with no offset: limit(5000).
// order_by() picks which rows a limited write takes. Two chains throw instead of
// running, because MySQL rejects them: update()->join(), and a multi-table
// delete("t", "table t") carrying order_by() or limit().

public static function query($statement, $isthis = false); // PDOStatement, or false - the error is swallowed
public static function exec($arg = '');                    // int affected rows; 0 on error - the error is swallowed
public static function hasTable($table = '');              // bool, via SHOW TABLES LIKE - the name is pasted, no prefix
public static function getPrefix(): string;                // the schema prefix from the database configuration
public static function getErrorMessage();                  // text of the last error, including the swallowed ones

// A page of rows.
WDB::select('t.id, t.name, t.status, u.full_name')
    ->from('users_products AS t')
    ->join('LEFT', 'users AS u', 'u.id = t.owner_id')
    ->where('t.status', 'IN', ['active', 'inprocess'])
    ->where('t.type', '=', 'hosting')
    ->order_by('t.id DESC')
    ->limit(0, 25)
    ->build();

$rows = WDB::fetch_assoc();                 // [] when nothing matched

// One row. build() is false when nothing matched, which is the guard to write.
$stmt = WDB::select('id, name, status')->from('users_products')->where('id', '=', $id)->limit(1);
$row  = $stmt->build() ? $stmt->getAssoc() : [];

// A bound OR group, isolated from the conditions around it.
$search = WDB::select('id')->from('users_products');
$search->where('owner_id', '=', $userId);
$search->whereGroup(function ($q) use ($word) {
    $q->where('name', 'LIKE', '%' . $word . '%', '||');
    $q->where('status', 'LIKE', '%' . $word . '%');
}, '&&');
$search->where('type', '=', 'hosting');
$found = $search->build() ? $search->fetch_assoc() : [];

// Insert returns the number of affected rows, and the identifier is read afterwards.
$affected = WDB::insert('users_products', [
    'owner_id'   => $userId,
    'product_id' => $productId,
    'name'       => $name,
    'status'     => 'waiting',
    'notes'      => null,                   // bound as SQL NULL
    'cdate'      => DateManager::Now(),
]);
$newId = $affected ? (int) WDB::lastID() : 0;

// Update: the chain does nothing until save().
WDB::update('users_products', ['status' => 'active'])->where('id', '=', $newId)->save();

// The one value that is deliberately not bound: an expression evaluated by the server.
WDB::update('coupons')->set(['uses' => 'uses+1'], true)->where('id', '=', $couponId)->save();

// Read the write back, from the other entry point - it is the same connection.
$check = WDB::select('status')->from('users_products')->where('id', '=', $newId);
$saved = $check->build() ? ($check->getAssoc()['status'] ?? '') : '';

// Delete: run(), not save().
WDB::delete('users_products')->where('id', '=', $newId)->run();