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
Argument | Type | Description | Examples |
---|
$sql | string | The SQL query to execute. | SELECT * FROM users WHERE id = :id INSERT INTO users (name, email) VALUES (:name, :email) |
$placeholders | ?array | An indexed or associative array to store the placeholders used in the query. | ['id' => 1] ['name' => 'John', 'email' => 'john@example.com'] ['John', 'john@example.com'] |
$debug | bool|string | The Debug mode | false , 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
Argument | Type | Description | Examples |
---|
$sql | string | The SQL query to execute. | SELECT FROM customers WHERE country = :country SELECT FROM customers WHERE country = ? |
$placeholders | ?array | An associative array of placeholders to substitute in the SQL query. | ['country' => 'Indonesia'] ['Indonesia'] |
$fetchParameters | ?int | The PDO fetch style. | PDO::FETCH_OBJ PDO::FETCH_ASSOC |
$debug | bool|string | The Debug mode | false , 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
Argument | Type | Description | Example |
---|
$query | string | The SQL query to execute. | SELECT name FROM users WHERE id = :id |
$params | ?array | An optional array of parameters to bind in the SQL query. | ['id' => 1] |
$debug | bool|string | The Debug mode | false , 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