Browser docs

Db - SQL Query Execution

Get started with SQL query execution in PowerLite PDO. Our comprehensive guide covers everything you need to know to run SQL queries effectively.

On this page

This page provides a comprehensive guide on how to execute SQL queries using the Db class in your project. We will cover the following methods:

Loading the Db class within the DI container

To use the Db class, you first need to load it within the Dependency Injection (DI) container. Here's how you can do it:

use Migliori\PowerLitePdo\Db;

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

query()

The Db::query() method is used to execute SQL queries. It takes a SQL statement as a string, an optional array of placeholders, and an optional debug mode.

Method Signature

public function query(
    string $sql,
    array $placeholders = [],
    bool|string $debug = false
): bool

Arguments Summary

ArgumentTypeDescriptionExamples
$sqlstringThe SQL query to execute.
SELECT * FROM users WHERE id = :id
INSERT INTO users (name, email) VALUES (:name, :email)
$placeholders?arrayAn indexed or associative array to store the placeholders used in the query.
['id' => 1]
['name' => 'John', 'email' => 'john@example.com']
['John', 'john@example.com']
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

SELECT query Db::query()

// SQL statement using named parameters. You can use this approach to specify values by name.
$sql = "SELECT * FROM users WHERE id = :id";
$placeholders = ['id' => 1];

// SQL statement using positional parameters. This approach allows you to specify values by their position.
$sql = "SELECT * FROM users WHERE id = ?";
$placeholders = [1];

$db->query($sql, $placeholders);

while ($row = $db->fetch()) {
    echo $row->name;
}

INSERT statement Db::query()

// SQL statement using named parameters. You can use this approach to specify values by name.
$sql = "INSERT INTO users (id, name, email, profiles_id, status) VALUES (NULL, :name, :email, :profiles_id, :status)";
$parameters = ['name' => 'John', 'email' => 'john@example.com', 'profiles_id' => 1, 'status' => 'active'];

// SQL statement using positional parameters. This approach allows you to specify values by their position.
$sql = "INSERT INTO users (id, name, email, profiles_id, status) VALUES (NULL, ?, ?, ?, ?)";
$parameters = ['John', 'john@example.com', 1, 'active'];

// Send the query and retrieve the number of affected rows
$affectedRows = $db->query($sql, $placeholders);

// Get the last insert id
$lastInsertId = $db->getLastInsertId();

UPDATE statement Db::query()

// SQL statement using named parameters. You can use this approach to specify values by name.
$sql = "UPDATE users SET name = :name WHERE id = :id";
$placeholders = ['name' => 'Jane', 'id' => $lastInsertId];

// SQL statement using positional parameters. This approach allows you to specify values by their position.
$sql = "UPDATE users SET name = ? WHERE id = ?";
$placeholders = ['Jane', $lastInsertId];

// Send the query and retrieve the number of affected rows
$affectedRows = $db->query($sql, $placeholders);

DELETE statement Db::query()

// SQL statement using named parameters. You can use this approach to specify values by name.
$sql = "DELETE FROM users WHERE id = :id";
$placeholders = ['id' => $lastInsertId];

// SQL statement using positional parameters. This approach allows you to specify values by their position.
$sql = "DELETE FROM users WHERE id = ?";
$placeholders = [$lastInsertId];

// Send the query and retrieve the number of affected rows
$affectedRows = $db->query($sql, $placeholders);

Db::queryRow()

The Db::queryRow method executes a SQL query using PDO and returns one row.

Method Signature

public function queryRow(
    string $sql,
    array $placeholders = [],
    $fetchParameters = PDO::FETCH_OBJ,
    bool|string $debug = false
)

Arguments Summary

ArgumentTypeDescriptionExamples
$sqlstringThe SQL query to execute.
SELECT FROM customers WHERE country = :country
SELECT FROM customers WHERE country = ?
$placeholders?arrayAn associative array of placeholders to substitute in the SQL query.
['country' => 'Indonesia']
['Indonesia']
$fetchParameters?intThe PDO fetch style.
PDO::FETCH_OBJ
PDO::FETCH_ASSOC
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

Query a single ROW Db::queryRow()

// SQL statement using named parameters. You can use this approach to specify values by name.
$sql = "SELECT * FROM customers WHERE country = :country";
$placeholders = ['country' => 'Indonesia'];

// SQL statement using positional parameters. This approach allows you to specify values by their position.
$sql = "SELECT * FROM customers WHERE country = ?";
$placeholders = ['Indonesia'];

// Send the query and get the result
$result = $db->queryRow($sql, $placeholders, PDO::FETCH_OBJ);
if ($result) {
    echo "ID: " . $result->id;
    echo "First Name: " . $result->first_name;
}

In both examples, the queryRow method is used to execute a SQL query that returns a single Row. The $params argument is used to safely bind parameters in the SQL query, preventing SQL injection attacks.

Db::queryValue()

Method Signature

public function queryValue(
    string $sql,
    array $placeholders = [],
    bool|string $debug = false
)

The Db::queryValue method executes a SQL query using PDO and returns a single value only.

Arguments Summary

ArgumentTypeDescriptionExample
$querystringThe SQL query to execute.
SELECT name FROM users WHERE id = :id
$params?arrayAn optional array of parameters to bind in the SQL query.
['id' => 1]
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

Query a value Db::queryValue()

// SQL statement using named parameters. You can use this approach to specify values by name.
$query = "SELECT name FROM users WHERE id = :id";
$params = [':id' => 1];

// SQL statement using positional parameters. This approach allows you to specify values by their position.
$query = "SELECT name FROM users WHERE id = ?";
$params = [1];

// Send the query and get the result
$result = $db->queryValue($query, $params);
echo $result;  // Outputs the name of the user with id 1

In both examples, the queryValue method is used to execute a SQL query that returns a single value. The $params argument is used to safely bind parameters in the SQL query, preventing SQL injection attacks.

numRows()

The Db::numRows method is used to get the number of rows in the result set of the current query.

Method Signature

public function numRows(): int|false

The Db::numRows method does not take any arguments and returns either an integer representing the number of rows, or false on failure.

Examples

Num rows after select Db::numRows()

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

echo $result; // Outputs the number of rows in the "users" table