5. Select Query¶
After any select/get function calls amount or returned rows is stored in $count variable
$users = $db->get(‘users’); //contains an Array of all users $users = $db->get(‘users’, 10); //contains an Array 10 users
or select with custom columns set. Functions also could be used
$cols = Array (“id”, “name”, “email”); $users = $db->get (“users”, null, $cols); if ($db->count > 0)
- foreach ($users as $user) {
- print_r ($user);
}
or select just one row
$db->where (“id”, 1); $user = $db->getOne (“users”); echo $user[‘id’];
$stats = $db->getOne (“users”, “sum(id), count(*) as cnt”); echo “total ”.$stats[‘cnt’]. “users found”;
or select one column value or function result
$count = $db->getValue (“users”, “count(*)”); echo “{$count} users found”;
select one column value or function result from multiple rows:
$logins = $db->getValue (“users”, “login”, null); // select login from users $logins = $db->getValue (“users”, “login”, 5); // select login from users limit 5 foreach ($logins as $login)
echo $login;
6. Pagination¶
Use paginate() instead of get() to fetch paginated result
$page = 1; // set page limit to 2 results per page. 20 by default $db->pageLimit = 2; $products = $db->arraybuilder()->paginate(“products”, $page); echo “showing $page out of ” . $db->totalPages;
6.1. Result transformation / map¶
Instead of getting an pure array of results its possible to get result in an associative array with a needed key. If only 2 fields to fetch will be set in get(), method will return result in array($k => $v) and array ($k => array ($v, $v)) in rest of the cases.
$user = $db->map (‘login’)->ObjectBuilder()->getOne (‘users’, ‘login, id’); Array (
[user1] => 1)
$user = $db->map (‘login’)->ObjectBuilder()->getOne (‘users’, ‘id,login,createdAt’); Array (
- [user1] => stdClass Object
- (
- [id] => 1 [login] => user1 [createdAt] => 2015-10-22 22:27:53
)
)
6.2. Defining a return type¶
MysqliDb can return result in 3 different formats: Array of Array, Array of Objects and a Json string. To select a return type use ArrayBuilder(), ObjectBuilder() and JsonBuilder() methods. Note that ArrayBuilder() is a default return type
// Array return type $= $db->getOne(“users”); echo $u[‘login’]; // Object return type $u = $db->ObjectBuilder()->getOne(“users”); echo $u->login; // Json return type $json = $db->JsonBuilder()->getOne(“users”);