Browse Documentation

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()

$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()

$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.

selectValue()

The Db::SelectValue() method prepares and executes the SQL SELECT statement in a single function call and returns a single value from the first column of the first row of the result set.

Method Signature

public function selectValue(
    string $from,
    string $field,
    ?array $where = [],
    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'
$fieldstring|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%']
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

Basic Example Db::SelectValue()

$from = 'users'; // The table name
$field = ['email']; // The columns you want to select
$where = ['status' => 'active']; // The conditions for the WHERE clause

$userEmail = $db->selectValue($from, $field, $where);
echo $userEmail; // Outputs the email of the first active user

In this example, we are selecting the 'email' field from the 'users' table where the 'status' is 'active'. The method returns the email of the first active user found in the database.

selectCount()

The Db::SelectCount() method prepares and executes the SQL SELECT COUNT statement in a single function call and returns the count of records that match the specified conditions.

Method Signature

public function selectCount(
    string $from,
    string|array $fields = ['*' => 'rowsCount'],
    mixed $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

Basic Example Db::SelectCount()

$from = 'users'; // The table name
$fields = ['*' => 'rowsCount']; // The columns you want to select
$where = ['status' => 'active']; // The conditions for the WHERE clause

$total = $db->selectCount($from, $fields, $where);
echo $total; // Outputs the count of active users

In this example, we are counting the number of records in the 'users' table where the 'status' is 'active'. The method returns the total count of active users.

numRows()

The Db::numRows() method returns the number of rows affected by the last executed SQL statement.

Method Signature

public function numRows(): int|false

Examples

Basic Example Db::numRows()

$db->select('users', '*', ['status' => 'active']);
$rowCount = $db->numRows();

echo $rowCount; // Outputs the number of active users retrieved

In this example, after executing a SELECT statement to retrieve all active users from the 'users' table, we use the numRows() method to get the number of rows returned by the query.

Introduction
SQL Query Execution