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 Name | Argument Type | Description | Examples |
|---|
| $fetchParameters | ?int | The 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 Name | Argument Type | Description | Examples |
|---|
| $fetchParameters | ?int | The 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
| Argument | Type | Description | Examples |
|---|
| $array | mixed | An array of records from a Query() or Select() operation. | [['id' => 1, 'name' => 'John'], ['id' => 2, 'name' => 'Jane']]
[['id' => 3, 'name' => 'Doe']] |
| $value_field | string | The name of the column to use for the values of the resulting array. | 'name' |
| $key_field | ?string | The 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
| Argument | Type | Description | Examples |
|---|
| $records | array | The 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 | ?bool | True if you want to show the row count, false if you do not want to show the count. Default is true. | true
false |
| $tableAttr | ?string | Comma separated attributes for the table. | 'class=my-class, style=color:#222' |
| $thAttr | ?string | Comma separated attributes for the header row. | 'class=my-class, style=font-weight:bold' |
| $tdAttr | ?string | Comma 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
| id | name | email |
|---|
| 2 | Dur | dstiggers1@icio.us |
| 3 | Darsie | dohogertie2@pcworld.com |
| 4 | Rutter | rdonoghue3@house.gov |
| 5 | Carlota | ctipper4@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
| id | name | email |
|---|
| 2 | Dur | dstiggers1@icio.us |
| 3 | Darsie | dohogertie2@pcworld.com |
| 4 | Rutter | rdonoghue3@house.gov |
| 5 | Carlota | ctipper4@jigsy.com |