Simplifying Database Interactions with PowerLite PDO

A lightweight, object-oriented library that simplifies database interaction in PHP applications. Learn more and download on Github

Overview

PowerLite PDO is a powerful and lightweight PHP library designed to simplify the process of interacting with databases using PHP Data Objects (PDO). The source code is available on Github here: https://github.com/migliori/power-lite-pdo.

This library is built on top of PDO and provides a set of tools and utilities to make working with databases easier, more secure, and more efficient. It offers a simple and intuitive API for executing queries, building complex SQL statements, and paginating results.

PowerLite PDO is designed to be flexible and extensible, allowing you to customize and extend its functionality to suit your needs.

Key Features

PowerLite PDO offers a wide range of features to help you work with databases more effectively. Some of the key features include:

• Simplified Database Interactions: PowerLite PDO provides a simple and intuitive API for executing queries and interacting with databases.
• Secure Query Building: The QueryBuilder class helps you build complex SQL queries in a secure and programmatic way.
• Pagination Support: The Pagination class makes it easy to paginate query results and handle large datasets.
• Database Driver Management: The DriverManager class manages database drivers and provides a consistent way to connect to different databases.
• Dependency Injection Container: PowerLite PDO uses a Dependency Injection (DI) container to manage dependencies between different parts of the system.
• Customizable and Extensible: PowerLite PDO is designed to be flexible and extensible, allowing you to customize and extend its functionality to suit your needs.

Installation

You can install PowerLite PDO via Composer by running the following command:

composer require migliori/power-lite-pdo

Alternatively, you can download the library from the GitHub repository and include it in your project manually.

Architecture Overview

PowerLite PDO is built around a few core components that work together to provide a seamless experience for interacting with databases. The main components are:

• Db: The core class that manages the database connection and provides an interface for executing queries.
• QueryBuilder: A powerful tool for building SQL queries in a programmatic and secure way. It abstracts the underlying SQL syntax and provides a fluent, chainable interface for constructing queries.
• Pagination: A class for handling pagination of query results.

PowerLite PDO also includes a DriverManager class that manages the database drivers. The DriverManager is used by the Db and QueryBuilder classes to establish a connection to the database using the specified driver.

Getting Started

Enter your connection settings

To connect to your database and start using PowerLite PDO, you need to enter your connection parameters in the dedicated file (src/connection.php)

Connect Using the DI Container

PowerLite PDO uses Dependency Injection (DI) to manage dependencies between different parts of the system. The DI container is configured in a bootstrap file, and the separate connection file is used to manage database connections.

Load the DI container and the class you want to use in your project:

use PowerLite\Db;

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

This example illustrates how to load the Db class using the DI container. You can also load other classes, such as QueryBuilder or Pagination, in a similar way.

Start using PowerLite PDO to interact with your database:

$results = $db->query('SELECT * FROM users'); foreach ($results as $row) { echo $row['name'] . '<br>'; }

$results = $db->query('SELECT * FROM users');
foreach ($results as $row) {
    echo $row['name'] . '<br>';
}

The example above demonstrates how to execute a simple query using the Db class and iterate over the results.

Now, let’s delve deeper into the three main components — Db, QueryBuilder, and Pagination — and explore their features in detail.

The Db Class

The Db class uses a procedural style for database interactions, which is in contrast to the QueryBuilder that employs a fluent interface.

SELECT Query

Let’s now build a more complex SELECT query with joins and conditions using some placeholders for the values:

// Define the tables and columns to select from
$from = 'users u INNER JOIN posts p ON u.id = p.user_id';
$where = 'u.status = :status AND p.published = :published';
$params = ['status' => 'active', 'published' => 1];

// Execute the query
$db->select($from, $where, $params);

// Iterate over the results
foreach ($db->results() as $row) {
    echo $row['name'] . ' - ' . $row['title'] . '<br>';
}

In this example, we use the select method of the Db class to execute a SELECT query with joins and conditions. We pass the table names, columns, and conditions as arguments to the method, along with an array of parameters to bind to the placeholders in the query, ensuring that the query is secure and free from SQL injection vulnerabilities.

INSERT Query

Now, let’s insert a new record into the users table:

// Define the table and columns to insert into
$table = 'users';
$data = ['name' => 'John Doe', 'email' => 'john.doe@email.com'];

// Execute the query
$db->insert($table, $data);

// Get the ID of the inserted record
$id = $db->lastInsertId();

In this example, we use the insert method of the Db class to insert a new record into the users table. We pass the table name and an associative array of column names and values to insert into the table. The method returns the ID of the inserted record, which we can use to perform further operations.

UPDATE Query

Next, let’s update an existing record in the users table:

// Define the table, columns, and conditions to update
$table = 'users';
$data = ['status' => 'inactive'];
$where = 'id = :id';
$params = ['id' => 1];

// Execute the query
$db->update($table, $data, $where, $params);

// Get the number of affected rows
$affectedRows = $db->rowCount();

In this example, we use the update method of the Db class to update an existing record in the users table. We pass the table name, an associative array of column names and values to update, and the conditions to apply the update to. We also provide an array of parameters to bind to the placeholders in the query. The method returns the number of affected rows, which we can use to determine the success of the update operation.

The Query Builder

The QueryBuilder is a powerful component of PowerLite PDO that provides a fluent interface for building SQL queries. Unlike the procedural style of the Db class, the QueryBuilder allows you to chain methods together to construct your queries in a more readable and expressive manner.

Here’s an example of how you can use the QueryBuilder to select and fetch records from a database:

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

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

In this example, we’re selecting the name and email columns from the users table where the status is active, and ordering the results by name in ascending order. The get method is then used to execute the query and return the results.

This is just a simple example, but the QueryBuilder is also able to deal with more complex queries with parameters:

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

The QueryBuilder also supports a wide range of SQL operations, including insert, update, delete, and complex join operations, making it a versatile tool for interacting with your database.

The Pagination

The Pagination component of PowerLite PDO is designed to simplify the process of dividing your query results into manageable chunks, also known as pagination. This is particularly useful when dealing with large datasets that would be impractical to display all at once.

Here’s an example of how you can use the Pagination component:

$from = 'users'; // The SELECT FROM clause
$fields = ['id', 'name', 'email']; // The columns you want to select
$where = ['status' => 'active']; // The conditions for the WHERE clause
$parameters = ['orderBy' => 'name'];

$pagination->select($from, $fields, $where, $parameters);

// After selecting the records, you can fetch and display them using a while loop
$records = [];
while ($record = $pagination->fetch()) {
    $records[] = $record->id . ', ' . $record->username . ', ' . $record->email;
}
echo implode('<br>', $records);

// show the pagination links
$url = '/examples/pagination-examples.php'; // The URL for the pagination links
echo $pagination->pagine($url);

In this example, we’re selecting the name and email columns from the users table. We then create a new Paginator instance, passing in the query and the number of items we want per page (10 in this case). The getResults method is used to retrieve the current page of results, and the getLinks method is used to generate the pagination links.

The Pagination component takes care of all the heavy lifting for you, calculating the total number of pages, the current page, and generating the appropriate SQL to retrieve the current page of results. This allows you to focus on displaying the results and not on the underlying pagination logic.

Support and Contribution

If you have any questions, suggestions, or feedback, please feel free to open an issue on GitHub. You can also contribute to the project by forking the repository and submitting a pull request.

License

This project is open-source, licensed under the GNU General Public License v3.0

Projects

PowerLite PDO is the base tool used for the PHP CRUD Generator project, which has to date about 1,500 users.

CRUD Admin Dashboard — PHP CRUD Generator

The PHP CRUD Generator is a powerful and easy-to-use tool that allows you to quickly and easily generate a complete PHP CRUD for managing your database. It uses the PHP PDO Database class to provide a simple, intuitive, and flexible interface for performing common database operations. With its user-friendly interface, you can quickly create, read, update, and delete (CRUD) operations for your database, without having to write any code.

Discover More

PowerLite PDO is not just a tool, it’s a comprehensive solution designed to make your database operations efficient and secure. It’s user-friendly, yet robust and versatile, providing a solid base for all your database needs. It’s capable of handling everything from simple tasks to complex queries and operations.

Whether you’re working on a small personal project or a large enterprise application, PowerLite PDO is equipped with the features you need. But don’t just take our word for it, explore more about PowerLite PDO in our official documentation and on our GitHub repository.