The Db class

Index > Database > The Db Class

---

The SnooPHP\Model\Db class provides a very light interface with the underlying DBMS, unlike the Model class.

Querying

Db::query(string $queryString, array $queryParams = [], string $dbName = "master", bool $fetchResults = true) is a static method that allows us to easily perform queries using safe parameter binding over the specified database connection:

$query		= $request->input("q", "");
$results	= Db::query("select * from users where username like ?", ["%$query%"], "local");

return Response::json($results[0]);

By default results are fetched, using PDOStatement::fetchAll() with PDO::FETCH_ASSOC parameter.

This may throw a PDOException for some types of queries (e.g. CREATE TABLE, DROP TABLE). In that case, provide a fourth parameter $fetchResults = false and the method will only return true if execution was successfull.

If for any reason the query fails, false is returned.

The query() method uses PDOStatement::bindValue() to safely bind parameters to the query. You can use both available binding styles: with named placeholders or with question mark placeholders. In the first case you must provide an associative array:

// We can bind a single parameter to two named placeholders
$results = Db::query("select * from users where username = :query or email = :query", ["query" => $query]);

Managing transactions

The Db class provides methods to begin a transaction and commit or rollback over the designed database connection:

• Db::beginTransaction(string $dbName = "master") begin transaction on the specified database connection;
• Db::commit(string $dbName = "master") commit changes and end transaction;
• Db::rollBack(string $dbName = "master") discard changes and end transaction.

Accessing the PDO instance

You can retrieve the PDO object in any moment using Db::instance(string $dbName = "master"):

$query = Db::instance("local")->prepare("select * from users");

---

The Model class >