Browser docs

Db - Selecting and Fetching Records

The Db class offers a range of methods for selecting records, specific rows, values, or the count of records. The ability to apply joins, conditions, and parameters across all these methods enhances the flexibility of your database operations.

On this page

This page provides a practical guide on how to use the Db class for selecting and fetching records from a database. The following methods of the Db class are covered:

Loading the Db class

To use the Db class, you first need to load it within the Dependency Injection (DI) container.

use Migliori\PowerLitePdo\Db;

$container = require_once __DIR__ . '/../src/bootstrap.php';
$db = $container->get(Db::class);

Select()

The Db::Select() method prepares and executes the SQL SELECT statement in a single function call.

Method Signature

public function select(
    string $from,
    string|array $fields,
    ?array $where = [],
    ?array $parameters = [],
    bool|string $debug = false
): mixed

Arguments Summary

Argument NameArgument TypeDescriptionExamples
$fromstringThe SQL FROM clause
'users'
'users INNER JOIN profiles ON users.id = profiles.user_id'
$fieldsstring|arrayThe fields to select
'id, username, email'
['id', 'username', 'email']
$where?arrayAn array of SQL WHERE conditions
['status' => 'active']
['users.id' => 'profiles.user_id']
['users.id >' => 10]
['users.id >' => 10, 'users.username LIKE' => '%me%']
$parameters?arrayAn array with the followings optional key/value pairs:
[
    'selectDistinct' => bool,
    'orderBy' => string,
    'groupBy' => string,
    'limit' => int|string
]
['limit' => 10]
['orderBy' => 'username ASC']
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

Minimal example Db::Select()

$db->select('users', 'name');
while ($row = $db->fetch()) {
    echo $row->name;
}

Selecting multiple fields Db::Select()

$db->select('users', ['id', 'name', 'email']);
while ($row = $db->fetch()) {
    echo $row->id . ', ' . $row->name . ', ' . $row->email;
}

Adding a WHERE clause Db::Select()

$db->select('users', ['id', 'name', 'email'], ['status' => 'active']);
while ($row = $db->fetch()) {
    echo $row->id . ', ' . $row->name . ', ' . $row->email;
}

Adding a more complex WHERE clause Db::Select()

$db->select('users', ['id', 'name', 'email'], ['users.id >' => 10, 'users.name LIKE' => '%me%']);
while ($row = $db->fetch()) {
    echo $row->id . ', ' . $row->name . ', ' . $row->email;
}

Adding parameters (selectDistinct, orderBy, groupBy, limit) Db::Select()

$db->select('users', ['id', 'name', 'email'], ['status' => 'active'], ['limit' => 10, 'orderBy' => 'name ASC']);
while ($row = $db->fetch()) {
    echo $row->id . ', ' . $row->name . ', ' . $row->email;
}

Using a JOIN in the $from argument Db::Select()

$db->select('users INNER JOIN profiles ON users.id = profiles.user_id', ['users.id', 'users.name', 'users.email', 'profiles.name AS profilesName'], ['status' => 'active']);
while ($row = $db->fetch()) {
    echo $row->id . ', ' . $row->name . ', ' . $row->email . ', ' . $row->profilesName;
}

Using the fetchAll method Db::Select()

$db->select('users', ['id', 'name', 'email'], ['status' => 'active']);
$rows = $db->fetchAll();
foreach ($rows as $row) {
    echo $row->id . ', ' . $row->name . ', ' . $row->email;
}

selectRow()

The Db::SelectRow() method combines the preparation and execution of the SQL SELECT statement into a single function call. It returns an object or array containing the fetched record based on the specified $fetchParameters.

Method Signature

public function selectRow(
    string $from,
    mixed $fields = '*',
    mixed $where = [],
    int $fetchParameters = PDO::FETCH_OBJ,
    bool|string $debug = false
): mixed

Arguments Summary

Argument NameArgument TypeDescriptionExamples
$fromstringThe SQL FROM clause
'users'
'users INNER JOIN profiles ON users.id = profiles.user_id'
$fieldsstring|arrayThe fields to select
'id, username, email'
['id', 'username', 'email']
$where?arrayAn array of SQL WHERE conditions
['status' => 'active']
['users.id' => 'profiles.user_id']
['users.id >' => 10]
['users.id >' => 10, 'users.username LIKE' => '%me%']
$fetchParameters?intThe PDO Fetch modePDO::FETCH_OBJ OR PDO::FETCH_ASSOC
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

Simple Use Case Db::selectRow()

$db = new Db();
$result = $db->selectRow('users', '*', ['id' => 1]);
echo $result->name;

In this example, we are selecting all fields from the 'users' table where the 'id' is 1. The result is fetched as an object.

Complex Use Case with Joins and Specific Fields Db::selectRow()

$db = new Db();
$result = $db->selectRow('users INNER JOIN profiles ON users.id = profiles.user_id', ['users.id', 'users.name', 'profiles.name AS profilesName'], 'users.id = 1', PDO::FETCH_ASSOC);
echo $result['name'] . ' - ' . $result['profilesName'];

In this example, we are selecting specific fields from a join between the 'users' and 'profiles' tables where the 'id' in 'users' table is 1. The result is fetched as an associative array. The 'debug' mode is also enabled.