README

A standalone database query builder wrapper using PDO.

Features

Database Connection:

Establishes a database connection using PDO (PHP Data Objects) to interact with MySQL databases.
Supports multiple database drivers including MySQL, PostgreSQL, and SQLite.
Handles database connection errors and exceptions gracefully.

Query Building:

Provides fluent interface methods for building SQL queries, such as select, join, where, orWhere, andWhere, limit, and offset.
Supports selecting specific columns or all columns (*) from a table.
Allows joining tables with different types of joins (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN).
Enables adding WHERE clauses with various operators (e.g., '=', '>', '<', '>=', '<=').

Query Execution:

Executes SQL queries using prepared statements to prevent SQL injection attacks.
Fetches query results as arrays of objects for easy manipulation.
Handles PDO exceptions and provides error handling mechanisms.

Data Manipulation:

Supports inserting data into tables with insert method, providing an associative array of column names and values.
Allows updating data in tables with update method, providing new data as an associative array and conditions as an array of conditions.
Enables deleting data from tables with delete method, providing conditions as an associative array of column names and values.

Pagination:

Implements pagination of query results using paginate method, allowing specification of the number of items per page and the current page number.
Calculates pagination information including total items, total pages, and current page.
Supports seamless integration with existing query builder methods for building paginated queries.

Additional Utilities:

Provides a getLastQuery method to retrieve the last executed query for debugging purposes.
Implements a truncate method to truncate tables, removing all rows.
Offers a raw method for executing raw SQL queries when needed.

Requirements

PHP 8.0 or higher.

Installation

You can install the package via Composer by running the following command:

composer require sentgine/db:^1.0

Sample Usage of DB

Basic Usage

<?php

use Sentgine\Db\QueryBuilder;

// Create a new QueryBuilder instance
$queryBuilder = new QueryBuilder();

// Select all columns from the 'users' table
$queryBuilder->select('users')->get();

Select Specific Columns

// Select specific columns from the 'users' table
$queryBuilder->select('users', ['id', 'name', 'email'])->get();

WHERE Clause

// Select users where 'id' is equal to 1
$queryBuilder->select('users')->where('id', 1)->get();

WHERE Clause with Greater Than or Equal To (>=)

// Select users where 'age' is greater than or equal to 18
$queryBuilder->select('users')->where('age', 18, '>=');

// Equivalent to SQL: SELECT * FROM users WHERE age >= 18

WHERE Clause with Less Than or Equal To (<=)

// Select users where 'points' are less than or equal to 100
$queryBuilder->select('users')->where('points', 100, '<=');

// Equivalent to SQL: SELECT * FROM users WHERE points <= 100

WHERE Clause with Greater Than (>)

// Select users where 'salary' is greater than 50000
$queryBuilder->select('users')->where('salary', 50000, '>');

// Equivalent to SQL: SELECT * FROM users WHERE salary > 50000

WHERE Clause with Less Than (<)

// Select users where 'rating' is less than 4.5
$queryBuilder->select('users')->where('rating', 4.5, '<');

// Equivalent to SQL: SELECT * FROM users WHERE rating < 4.5

AND WHERE Clause

// Select users where 'id' is equal to 1 and 'status' is 'active'
$queryBuilder->select('users')->where('id', 1)->andWhere('status', 'active')->get();

OR WHERE Clause

// Select users where 'id' is equal to 1 or 'status' is 'inactive'
$queryBuilder->select('users')->where('id', 1)->orWhere('status', 'inactive')->get();

LIMIT

// Select only 10 users
$queryBuilder->select('users')->limit(10)->get();

OFFSET

// Select users with pagination, skipping the first 10 records
$queryBuilder->select('users')->limit(10)->offset(10)->get();

JOIN

// Select users along with their corresponding posts
$queryBuilder->select('users')
    ->join('INNER', 'posts', 'users.id = posts.user_id')
    ->get();

INSERT

// Insert a new user into the 'users' table
$queryBuilder->insert('users', [
    'name' => 'John Doe', 
    'email' => 'john@example.com'
]);

UPDATE

// Update user with id 1
$queryBuilder->update('users', 
    ['name' => 'Jane Doe'], 
    ['id' => 1]
);

DELETE

// Delete users with id greater than 10
$queryBuilder->delete('users', [
    'id' => [10, 20]
], '>');

Raw Query

// Execute a raw SQL query
$queryBuilder->raw('SELECT * FROM users WHERE id = 1')->get();

Truncate Table

// Truncate the 'users' table
$queryBuilder->truncate('users');

Paginate

use Sentgine\Db\QueryBuilder;

// Instantiate the QueryBuilder class
$queryBuilder = new QueryBuilder();

// Set the number of items per page
$perPage = 10;

// Get the current page number from the request, default to 1 if not provided
$current_page = $_GET['page'] ?? 1;

// Perform select statement
$queryBuilder->select('users');
$queryBuilder->where('age', 30, '>');


// Paginate the query results
$paginationData = $queryBuilder->paginate($perPage, $current_page);

// Retrieve the paginated data and pagination information
$data = $paginationData['data'];
$pagination = $paginationData['pagination'];

// Display the paginated data
foreach ($data as $row) {
    // Output each row of data
    echo $row->id . ' - ' . $row->name . '<br>';
}

// Display pagination links
echo '<br>Pagination:';
for ($page = 1; $page <= $pagination['total_pages']; $page++) {
    // Output pagination links
    echo '<a href="?page=' . $page . '">' . $page . '</a> ';
}

Advanced Scenarios

The SQL builder provided is intended solely for constructing SQL queries and ensuring proper formatting. While efforts have been made to mitigate security risks, it should not be solely relied upon as a security feature. Please be aware that complex SQL queries can increase the likelihood of SQL injection vulnerabilities. It is imperative to code thoroughly and conduct thorough reviews before deploying to production environments.

Complex Nesting on WHERE CLAUSE (logic driven search)

Utilize nesting in the WHERE clause as expression, particularly for strict conditions and business logic decisions. Nesting expressions can be applied at any desired level, but excessive nesting may lead to complications. Note that keywords {{nest1}} through {{nest20}} are reserved. No other keywords or characters are allowed to nest in the nestWhereExpression method. This ensures that unwanted syntax is not welcomed in this area.

$db->select('targetTable1', array("*"));
$db->nestWhereExpression(' 
    ( 
        {{nest1}}    
        OR
        {{nest2}}    
    )
    AND
    (  {{nest3}}    )
');

$db->where('target_colum1', 235,"=", expression: 'nest1');
$db->orWhere('target_colum1', 543,"=", expression: 'nest1'); // same area as nest1 with OR operator
$db->orWhere('target_colum1', 111,"=", expression: 'nest1'); // same area as nest1 with OR operator

$db->where('target_colum1', 111,"=", expression: 'nest2');

$db->where('target_colum1', 111,"=", expression: 'nest3');
$db->andWhere('target_colum1', 111,"=", expression: 'nest3'); // same area as nest3 with AND operator
$sql = $db->buildSQL();; // Build the SQL query
$db->raw($sql)->execute(); // Execute the query

Changelog

Please see the CHANGELOG file for details on what has changed.

Security

If you discover any security-related issues, please email sentgine@gmail.com instead of using the issue tracker.

Credits

DB is built and maintained by Adrian Navaja.

Check out some cool tutorials and stuff on YouTube!
Catch my latest tweets and updates on Twitter (formerly X)!
Let's connect on a more professional note over on LinkedIn!
For more information about me and my work, visit my website: sentgine.com.
A special acknowledgment goes to Dodie Batoctoy, the esteemed Database Specialist Consultant, for significantly enhancing the capabilities of the query builder to support complex edge cases. Dodie's expertise has been invaluable to this project. For more expert insights, connect with him on (LinkedIn).

License

The MIT License (MIT). Please see the LICENSE file for more information.

sentgine / db

Maintainers

Details