select-query.rst

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;

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;

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 )

)

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");