How-to-use:
$db = new DB($dsn, $username, $passwd);
1. select
$res = Sql::select('a, b')
->from('table')
->leftJoin('table1')->on('table.id=table1.id')
->where('a=?',1)
->groupBy('b')->having('sum(b)=?', 2)
->orderBy('c', Sql::$ORDER_BY_ASC)
->limit(0,1)
->forUpdate()->of('d')
->get($db);
2. update
$rows = Sql::update('table')
->set('a', 1)
->where('b=?', 2)
->orderBy('c', Sql::$ORDER_BY_ASC)
->limit(1)
->exec($db)
->rows
3. insert
$newId = Sql::insertInto('table')
->values(['a'=>1])
->exec($db)
->lastInsertId()
4. delete
$rows = Sql::deleteFrom('table')
->where('b=?', 2)
->orderBy('c', Sql::$ORDER_BY_ASC)
->limit(1)
->exec($db)
->rows
public function deleteUserByAccount($account)
{
$this->db->beginTransaction();
try {
$res = Sql::select('uid')->from('uc_members')->where('username=?', $account)->forUpdate()->get($this->db);
if (count($res) == 0) {
$this->db->rollBack();
return false;
}
$uid = $res[0]['uid'];
Sql::deleteFrom('pre_common_member_profile')->where('uid=?', $uid)->exec($this->db);
Sql::deleteFrom('uc_members')->where('uid=?', $uid)->exec($this->db);
$this->db->commit();
} catch (Exception $e) {
$this->db->rollBack();
throw $e;
}
return true;
}
public function testForReplace1()
{
//REPLACE INTO tab VALUES(1,2,now())
$this->db->setExpected('REPLACE INTO tab VALUES(?,?,now())', 1, 2);
Sql::replaceInto('tab')->values([1, 2, Sql::native('now()')])->exec($this->db);
}
