Browser docs

Query Builder - Selecting and Fetching Records

The QueryBuilder provides fluent methods for record selection, with added flexibility for joins, conditions, and parameters. Enhance your database operations with its comprehensive selection capabilities.

On this page

This page provides a comprehensive guide on how to select records from a database and fetch the results with the Query Builder.

Table of Contents

  1. Loading the QueryBuilder Class
  2. Selecting Records
    1. Basics of Record Selection
    2. Retrieving the Results
    3. Adding Parameters

Loading the QueryBuilder Class

Before you can use the QueryBuilder class, you need to load it within the Dependency Injection (DI) container.

use Migliori\PowerLitePdo\Query\QueryBuilder;

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

Selecting Records

This article will guide you through the process of selecting records using the QueryBuilder class in the PowerLitePdo library. We will cover the basics of record selection, retrieving the results, and adding parameters.

Basics of Record Selection

QueryBuilder::from

The from method is used to specify the table from which to select records. It can also include MySQL joins.

public function from(string $from) : self
Argument NameArgument TypeDescriptionExamples
$fromstringThe SQL FROM clause
'users'
'users INNER JOIN profiles ON users.id = profiles.user_id'

QueryBuilder::where

The where method is used to add conditions to the query.

public function where(array $conditions) : self
Argument NameArgument TypeDescriptionExamples
$where?arrayAn array of SQL WHERE conditions
['status' => 'active']
['users.id' => 'profiles.user_id']
['users.id >' => 10]
['users.id >' => 10, 'users.username LIKE' => '%me%']

QueryBuilder::execute

The execute method is used to execute the query.

public function execute() : self

Examples QueryBuilder::Select()

$queryBuilder->select(['id', 'name', 'email'])->from('users')->where(['status' => 'active'])->execute();

$queryBuilder->select(['users.id', 'users.name', 'orders.order_date'])->from('users INNER JOIN orders ON users.id = orders.user_id')->where(['users.status' => 'active'])->execute();

Retrieving the Results

QueryBuilder::fetch

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

public function fetch(int $fetchParameters = PDO::FETCH_OBJ): mixed
Argument NameArgument TypeDescriptionExamples
$fetchParameters?intThe PDO Fetch mode
PDO::FETCH_OBJ
PDO::FETCH_ASSOC

QueryBuilder fetch() example QueryBuilder::fetch()

$queryBuilder->select(['id', 'name', 'email'])->from('users')->where(['status' => 'active'])->execute();

while ($record = $queryBuilder->fetch()) {
    echo $record->id . ', ' . $record->name . ', ' . $record->email;
}

QueryBuilder::fetchAll

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

public function fetchAll(int $fetchParameters = PDO::FETCH_OBJ) : mixed
Argument NameArgument TypeDescriptionExamples
$fetchParameters?intThe PDO fetch style record options
PDO::FETCH_OBJ
PDO::FETCH_ASSOC

QueryBuilder fetchAll() example QueryBuilder::fetchAll()

$queryBuilder->select(['id', 'name', 'email'])->from('users')->where(['status' => 'active'])->execute();

$records = $queryBuilder->fetchAll();

Converting a records set into an HTML table

QueryBuilder::getHTML()

The QueryBuilder::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.

Basic example QueryBuilder::getHTML()

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

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

Add styling Db::getHTML()

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

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

Adding Parameters

QueryBuilder::distinct

The distinct method is used to specify that the query should return distinct results. It requires no argument.

public function distinct() : self

QueryBuilder::groupBy

The groupBy method is used to group the results by one or more columns.

public function groupBy(string $column) : self
Argument NameArgument TypeDescriptionExamples
$columnstringThe GROUP BY clause.
'name'
'name, status'

QueryBuilder::limit

The limit method is used to limit the number of results returned by the query.

public function limit(int $limit) : self
Argument NameArgument TypeDescriptionExamples
$limitintThe LIMIT clause.
 10
 '10, 20'

QueryBuilder::orderBy

The orderBy method is used to sort the results by one or more columns.

public function orderBy(string $orderBy) : self
Argument NameArgument TypeDescriptionExamples
$orderBystringThe ORDER BY clause.
 'name'
 'email DESC'

QueryBuilder::parameters

The parameters method is an alternative to the other parameter methods. It allows you to pass several parameters in a single method call.

public function parameters(array $parameters) : self
Argument NameArgument TypeDescriptionExamples
$parametersarrayAn associative array of parameters.
 ['distinct' => true, 'orderBy' => 'name ASC']
 ['groupBy' => 'role', 'limit' => 20]

QueryBuilder::select() examples with parameters QueryBuilder::select()

// select distinct
$queryBuilder->select(['name'])->distinct()->from('users')->execute();

// orderBy + limit
$queryBuilder->select(['id, name'])->from('users')->orderBy('name')->limit(20)->execute();

// orderBy direction + limit start/end
$queryBuilder->select(['id, name'])->from('users')->orderBy('name DESC')->limit('20, 40')->execute();

// using the parameters method to group the parameters in a single method call
$queryBuilder->select(['id', 'name', 'email'])->from('users')->where(['status' => 'active'])->parameters(['limit' => 10, 'orderBy' => 'name ASC'])->execute();

// fetch the results
while ($record = $queryBuilder->fetch()) {
    echo $record->id . ', ' . $record->name . ', ' . $record->email;
}