Fetching records

Fetching records from a database is done by using the from and the select methods.

$result = $db->from('users')
             ->select()
             ->all();

SELECT * FROM `users`

The result of calling the all method will be an array that will contain all of the records that were found or an empty array if no records were found.

foreach ($result as $user) {
    echo $user->name;
}

If you only want to retrieve a single record, you can use the first method. If no records are found the method returns false.

$user = $db->from('users')
           ->select()
           ->first();

if ($user) {
    echo $user->name;
} else {
    echo 'No records were found';
}

Retrieving only a column’s value is also possible by using the column method. If no records are found the method returns false.

$name = $db->from('users')
           ->column('name');
           
echo $name === false ? 'No records were found' : $name;

You should always filter your records before handling the results of a query, even if you use the first or the column method, otherwise you may encounter performance issues when querying over large data sets.

Distinct results

Retrieving only the distinct results is done using the distinct method.

$result = $db->from('users')
             ->distinct()
             ->select()
             ->all();

SELECT DISTINCT * FROM `users`

Columns selection

You can specify which columns you want to include in the result set by passing as an argument to the select method an array containing the column names.

$result = $db->from('users')
             ->select(['name' => 'n', 'email', 'age' => 'a'])
             ->all();

foreach ($result as $user) {
    echo $user->n, $user->email, $user->a;
}

SELECT `name` AS `n`, `email`, `age` AS `a` FROM `users`

Table sources

When fetching records from a database you can specify muliple table sources by passing as an argument to the from method an array containing all table names you want to use.

$result = $db->from(['users', 'profiles'])
             ->select()
             ->all();

SELECT * FROM `users`, `profiles`

As in the case of columns, you can alias table names by passing as an argument a key => value mapped array, where the key represents the table’s name and the value represents the table’s alias name. If you want a table name not being aliased, just omit the key for that specific table.

$result = $db->from(['users' => 'u', 'profiles' => 'p'])
             ->select()
             ->all();

SELECT * FROM `users` AS `u`, `profiles` AS `p`