Db - Data Fetching and Conversion

Get started with data fetching and conversion in PowerLite PDO. Our comprehensive guide covers all the essentials for manipulating data effectively.

This page provides a comprehensive guide on how to use the Db class for data fetching and conversion. The following methods 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);

Db::fetch()

Fetches the next row from a result set and returns it according to the $fetchParameters format.

Method Signature

public function fetch(int $fetchParameters = PDO::FETCH_OBJ): mixed

Arguments Summary

Argument NameArgument TypeDescriptionExamples
$fetchParameters?intThe PDO Fetch mode
PDO::FETCH_OBJ
PDO::FETCH_ASSOC

Examples

Simple Use Case Db::fetch()

$db->query("SELECT * FROM users");
while ($row = $db->fetch(PDO::FETCH_ASSOC)) {
    echo $row['name'];
}

In this example, we are fetching rows from the "users" table as associative arrays.

Using Default Fetch Mode Db::fetch()

$db->query("SELECT * FROM users");
while ($row = $db->fetch()) {
    echo $row->name;
}

In this example, we are fetching rows from the "products" table using the default fetch mode (PDO::FETCH_OBJ), which returns rows as objects.


Db::fetchAll()

Fetches all rows from a result set and return them according to the $fetchParameters format.

Method Signature

public function fetchAll(int $fetchParameters = PDO::FETCH_OBJ) : mixed

Arguments Summary

Argument NameArgument TypeDescriptionExamples
$fetchParameters?intThe PDO fetch style record options
PDO::FETCH_OBJ
PDO::FETCH_ASSOC

Examples

fetchAll Db::fetchAll()

$db->query("SELECT * FROM users");

// fetch all rows from the result set and return them as objects.
$result = $db->fetchAll(PDO::FETCH_OBJ);

// fetch all rows from the result set and return them as associative arrays.
$result = $db->fetchAll(PDO::FETCH_ASSOC);

Db::convertToSimpleArray()

public function convertToSimpleArray(mixed $array, string $value_field, ?string $key_field = null): array<string|int, mixed>

The Db::convertToSimpleArray() method converts a Query() or Select() array of records into a simple array using only one column or an associative array using another column as a key.

Method Signature

ArgumentTypeDescriptionExamples
$arraymixedAn array of records from a Query() or Select() operation.
[['id' => 1, 'name' => 'John'], ['id' => 2, 'name' => 'Jane']]
[['id' => 3, 'name' => 'Doe']]
$value_fieldstringThe name of the column to use for the values of the resulting array.
'name'
$key_field?stringThe name of the column to use for the keys of the resulting array. If not provided, the resulting array will be indexed by integers.
'id'

Examples

Convert to a simple array Db::convertToSimpleArray()

$db->select('users', ['id', 'name']);

// fetch all rows from the result set and return them as associative arrays.
$result = $db->fetchAll(PDO::FETCH_ASSOC);

// convert the result to an indexed array. E.g.: ["name,", "name2", ...]
$result = $db->convertToSimpleArray($result, 'name');

var_dump($result);

/* will output:
array (size=1000)
    0 => string 'Royall'
    1 => string 'Dur'
    2 => string 'Darsie'
    // ...
*/

Convert to an associative array Db::convertToSimpleArray()

$query = $db->select('users', ['name', 'email']);

// fetch all rows from the result set and return them as associative arrays.
$result = $db->fetchAll(PDO::FETCH_ASSOC);

// convert the result to an associative array. E.g.: ["name" => "email", ...]
// note that the arguments order is 'value', 'key', because the key is optional
$result = $db->convertToSimpleArray($result, 'email', 'name');

var_dump($result);

/* will output:
array
    'Royall' => string 'rcostanza0@etsy.com'
    'Dur' => string 'dsprouls3y@clickbank.net'
    'Darsie' => string 'dohogertie2@pcworld.com'
    // ...
*/

Db::getHTML()

The Db::getHTML method is used to convert a records set into an HTML table.

Method Signature

public function getHTML(
    array $records,
    bool $showCount = true,
    ?string $tableAttr = null,
    ?string $thAttr = null,
    ?string $tdAttr = null
): string

Arguments Summary

ArgumentTypeDescriptionExamples
$recordsarrayThe records set - can be an array or array of objects according to the fetch parameters.
[['name' => 'John', 'age' => 30], ['name' => 'Jane', 'age' => 25]]
[new User('John', 30), new User('Jane', 25)]
$showCount?boolTrue if you want to show the row count, false if you do not want to show the count. Default is true.
true
false
$tableAttr?stringComma separated attributes for the table.
'class=my-class, style=color:#222'
$thAttr?stringComma separated attributes for the header row.
'class=my-class, style=font-weight:bold'
$tdAttr?stringComma separated attributes for the cells.
'class=my-class, style=font-weight:normal'

The method returns a string containing an HTML table with all records listed.

Examples

Basic example Db::getHTML()

$db->select('users', ['id', 'name', 'email'], ['status' => 'active'], ['limit' => 4]);

echo $db->getHTML($records);
Output example 1
idnameemail
2Durdstiggers1@icio.us
3Darsiedohogertie2@pcworld.com
4Rutterrdonoghue3@house.gov
5Carlotactipper4@jigsy.com

Add styling Db::getHTML()

$db->select('users', ['id', 'name', 'email'], ['status' => 'active'], ['limit' => 4]);

echo $db->getHTML($result, false, 'class=table table-striped, id=my-table', 'class=p-4 text-bg-dark', 'class=p-2');
Output example 2
idnameemail
2Durdstiggers1@icio.us
3Darsiedohogertie2@pcworld.com
4Rutterrdonoghue3@house.gov
5Carlotactipper4@jigsy.com