joejhoward/php-sql-builder

Release dev-main

PHP SQL Builder provides simple yet surprisingly robust handler for managing database connections and queries.

Homepage Packagist PHP

Keywords
database, sql, pdo
License
MIT

Documentation

PHP SQL Builder

PHP SQL builder provides a complete 100% safe and secure solution for managing SQL connections through a set of handy PHP utility classes. Never write SQL in your PHP project again!

Report Bug · Request Feature

Table Of Contents

About The Project

PHP SQL Builder provides simple yet surprisingly robust handler for managing database connections and queries.

All interactions with your database are done through a single Database class. The Database class automatically prepares all your statements and queries for you, so you don't need to worry about SQL injection.

Getting Started

Follow the steps below to start using the SQL builder

Prerequisites

  • PHP >= 7.2
  • PDO PHP Extension
  • Composer (optional)
  • Web Server With SQL

Installation

The preferred way to install is via composer:

composer require joejhoward/php-sql-builder

Access

All access is provided through the Database class. A configuration array must be provided when constructing.

$database = new Database($config);

Configuration

Configuration options are provided as an array. You can can also define multiple configurations for different runtime environments e.g localhost, sandbox production etc..

/*
 * ---------------------------------------------------------
 * Configurations
 * ---------------------------------------------------------
 *
 * You can define as many database configurations as you want.
 *
 * dsn        : PDO dsn string (will override other options if provided)
 * name       : Database name
 * username   : (optional) Username of the database server
 * password   : (optional) Password of the database server
 * persistent : (optional) Set to true to make the connection persistent
 * log_queries: (optional) Enable query logging?
 * options    : (optional) An array of PDO options
 */
$config =
[
	'default'        => 'sandbox',
	'configurations' =>
	[
		'sandbox' =>
		[
			'name'     => 'Kanso',
			'host' 	   => 'localhost',
			'username' => 'root',
			'password' => 'root',
			'table_prefix' => 'kanso_',
			'options'  =>
			[
				'MYSQL_ATTR_INIT_COMMAND' => 'SET NAMES utf8',
				'ATTR_DEFAULT_FETCH_MODE' => \PDO::FETCH_ASSOC,
			],
		],
	],
	[
		'production' =>
		[
			#...
		],
	],
];

Connections & Queries

Creating a database connection is done using the connection method:

# Returns connection object using the "default" database configuration defined in the config file
$connection = $database->connection();

# Returns connection object using the "mydb" database configuration defined in the config file
$connection = $database->connection('mydb');

The connections method returns an array of connection objects for all active connections:

$connections = $database->connections();

The connect method attempts to connect to the database and throws a PDOException if it fails. If successful, a PDO extension instance is returned:

# You may need to try catch this if you don't want the exception to be thrown
try
{
    $pdo = $connection->connect();
}
catch(PDOException $e)
{
    # Do something else here
}

The isConnected method check if the connection is connected to the database and returns a boolean:

if ($connection->isConnected())
{
}

The reconnect method attempts to reconnect to the database and throws a PDOException if it fails. If successful, a PDO extension instance is returned:

if ($connection->isConnected())
{
    try
    {
        $pdo = $connection->reconnect();
    }
    catch(PDOException $e)
    {
        # Do something else here
    }
}

The pdo method is similar to the reconnect method and attempts to connect to the database and throws a PDOException if it fails. If successful, a PDO extension instance is returned:

try
{
    $pdo = $connection->pdo();
}
catch(PDOException $e)
{
    # Do something else here
}

The isAlive method is checks if the current connection is alive and returns a boolean:

if ($connection->isAlive())
{
}

The close method closes the connection and destructs the PDO extension instance.

$connection->close();

The tablePrefix method returns the string prefix of the database table or an empty string if one is not set.

$prefix = $connection->tablePrefix();

Connection Handler

Connections come with a handy connectionHandler for interacting with the database and executing queries. When running queries through any database the connectionHandler should always be used.

You can call the handler method to get a connection's handler instance.

$handler = $connection->handler();

The getLog method returns an array of all queries executed by the handler.

$log = $handler->getLog();

$lastQuery = array_pop($handler->getLog());

Queries

Database queries should be run through a connectionHandler instance.

The query method executes a given query:

$users = $handler->query('SELECT * FROM kanso_users');

The row method returns a single row or an empty array if the results are empty:

$users = $handler->row('SELECT * FROM kanso_users WHERE email = :email', ['email' => 'email@example.com']);

The single method always returns a single value of a record:

$name = $handler->single('SELECT name FROM kanso_users WHERE id = :id', ['id' => 1]);

The column method always returns a single column:

$names = $handler->column('SELECT name FROM kanso_users');

Bindings

Binding parameters is the best way to prevent SQL injection. The class prepares your SQL query and binds the parameters afterwards. There are three different ways to bind parameters:

# 1. Read friendly method  
$handler->bind('id', 1);
$handler->bind('name','John');
$user = $handler->query('SELECT * FROM kanso_users WHERE name = :name AND id = :id');

# 2. Bind more parameters
$handler->bindMore(['name'=>'John','id'=>'1']);
$user = $handler->query('SELECT * FROM kanso_users WHERE name = :name AND id = :id');

# 3. Or just give the parameters to the method
$user = $handler->query('SELECT * FROM kanso_users WHERE name = :name', ['name'=>'John','id'=>'1']);

Delete / Update / Insert

When executing the delete, update, or insert statements via the query method the affected rows will be returned:

# Delete
$delete = $handler->query('DELETE FROM kanso_users WHERE Id = :id', ['id'=>'1']);
# Update
$update = $handler->query('UPDATE kanso_users SET name = :f WHERE Id = :id', ['f'=>'Jan','id'=>'32']);
# Insert
$insert = $handler->query('INSERT INTO kanso_users(name,Age) VALUES(:f,:age)', ['f'=>'Vivek','age'=>'20']);

# Do something with the data 
if($insert > 0 )
{
    return 'Succesfully created a new person !';
}

The lastInsertId method returns the last inserted id:

if ($handler->query('INSERT INTO kanso_users(name,Age) VALUES(:f,:age)', ['f'=>'Vivek','age'=>'20']))
{
    $id = $handler()->lastInsertId();
}

Method Params

The row and the query method have a third optional parameter which is the fetch style.

The default fetch style is PDO::FETCH_ASSOC which returns an associative array. You can change this behavior by providing a valid PHP PDO fetch_style as the third parameter.

# Fetch style as third parameter
$authorNum = $connection->row('SELECT * FROM kanso_users WHERE id = :id', ['id' => 1 ], PDO::FETCH_NUM);

print_r($person_num);
# [ [0] => 1 [1] => Johny [2] => Doe [3] => M [4] => 19 ]

Cache

The ConnectionHandler comes with a very basic cache implementation for caching SELECT query results across a single request.

When enabled, the cache will check to see if the same query has already been run during the request and attempt to load it from the cache.

If you execute an UPDATE or DELETE query, the results will be cleared from the cache.

To access the cache, use the cache method on a ConnectionHandler instance:

$cache = $handler->cache();

The disable method disables the cache:

$cache->disable();

The enable method enables the cache:

$cache->enable();

The enabled method returns a boolean on the cache status:

if ($cache->enabled())
{   
}

Query Builder

PHP SQL Builder Query Builder allows you to programmatically build SQL queries without having to write giant SQL statements.

Essentially, the Builder class is a chainable wrapper around the SQL syntax. All queries executed by the builder use prepared statements, thus mitigating the risk of SQL injections.

When chaining methods, the chaining order follows the same syntax as if you were to write an SQL query statement.

Access

You can access the Builder class directly through the IoC container via the Database object:

$builder = $kanso->Database->builder();

Alternatively if you have a reference to an existing database connection, you can access the builder directly through the connection.

$builder = $kanso->Database->connection()->builder();

Table Management

The Builder class provides various methods to manipulate and interact with database tables. All the table management will return the Builder instance at hand, making them chainable.

The CREATE_TABLE method is used to create a table:

$customPosts =
[
    'id'          => 'INTEGER | UNSIGNED | PRIMARY KEY | UNIQUE | AUTO INCREMENT',
    'created'     => 'INTEGER | UNSIGNED',
    'modified'    => 'INTEGER | UNSIGNED',
    'type'        => 'VARCHAR(255)',
];
$builder->CREATE_TABLE('custom_posts' $customPosts);

The DROP_TABLE method drops a table:

$builder->DROP_TABLE('custom_posts');

The TRUNCATE_TABLE method truncates a table's columns:

$builder->TRUNCATE_TABLE('custom_posts');

Alter

To start altering a table, use the ALTER_TABLE method:

$table = $builder->ALTER_TABLE('custom_posts');

The Alter class provides a number of helper methods to interact with the table at hand. The alter methods all return the working instance of the Alter class, making them chainable.

The ADD_COLUMN method adds a column to an existing table:

$table->ADD_COLUMN('author_id', 'INTEGER | UNSIGNED');

The DROP_COLUMN method drops an existing column on an existing table:

$table->DROP_COLUMN('author_id');

The MODIFY_COLUMN method can be used to set a column's data-type by providing a second parameter:

$table->MODIFY_COLUMN('author_id', 'INTEGER | UNSIGNED | UNIQUE');

Or to set the working column for other methods by omitting it.

$column = $table->MODIFY_COLUMN('author_id');

Once you have called the MODIFY_COLUMN method, the following column modification methods are made available:

$column->ADD_PRIMARY_KEY();
$column->DROP_PRIMARY_KEY();
$column->ADD_NOT_NULL();
$column->DROP_NOT_NULL();
$column->ADD_UNSIGNED();
$column->DROP_UNSIGNED();
$column->SET_AUTO_INCREMENT();
$column->DROP_AUTO_INCREMENT();
$column->SET_DEFAULT($value = null);
$column->DROP_DEFAULT();
$column->ADD_UNIQUE();
$column->DROP_UNIQUE();
$column->ADD_FOREIGN_KEY($referenceTable, $referenceKey, $constraint = null);
$column->DROP_FOREIGN_KEY($referenceTable, $referenceKey, $constraint = null);

Foreign Keys

To set a foreign key constraint, use the ADD_FOREIGN_KEY method. The first parameter is the reference table, the second is the reference table's column name. The third parameter is optional and is used to set the name of the constraint. If omitted, a constraint name will be generated for you.

$column->ADD_FOREIGN_KEY('users', 'id');

Dropping a foreign key constraint follows the same rules as the ADD_FOREIGN_KEY method.

$column->DROP_FOREIGN_KEY('users', 'id');

Alter Chains

A simple chain starting from a Builder instance might look like this:

$builder->ALTER_TABLE('custom_posts')->ADD_COLUMN('author_id', 'INTEGER | UNSIGNED');

A more complicated chain starting from a Builder instance might look like this:

$builder->ALTER_TABLE('custom_posts')->ADD_COLUMN('author_id', 'INTEGER | UNSIGNED')->MODIFY_COLUMN('author_id')->ADD_FOREIGN_KEY('users', 'id');

Query Building

The Builder class provides almost all SQL query statements by providing a wrapper around the Query class. The methods can be placed into three logical sections:

  • Query types
  • Query filters
  • Query organizers
  • Query executions

Types

Query types are where you define the type of query you are going to execute INSERT, DELETE, UPDATE, SET; as well the table upon which the query will run.

The following methods can be used to set the query type:

# Set the query to query a given table
$builder->FROM($tablename);

# Set the query type to UPDATE on a given table
$builder->UPDATE($tablename);

# Set the query type to INSERT INTO on a given table
$builder->INSERT_INTO($tablename);

# Set the query type to DELETE on a given table
$builder->DELETE_FROM($tablename);

# Set the query type to SELECT on the current 
# table and set the columns to select
$builder->SELECT($tablename);

# Set the query type to INSERT INTO on the current 
# table and set the values to insert
$builder->VALUES($rows);

# Set the query type to SET on the current 
# table and set the values to set
$builder->SET($rows);

Filters

Query filters are where you build your query to filter the table results. The following methods can be used to filter the query:

# Add a WHERE clause
$builder->WHERE($column, $operator, $value);

# Add an AND WHERE clause
$builder->AND_WHERE($column, $operator, $value);

# Add an OR WHERE clause
$builder->OR_WHERE($column, $operator, $value);

# Nested OR WHERE clause
$builder->OR_WHERE($column, $operator, ['foo', 'bar', 'baz']);

# Add a JOIN and ON clause
$builder->JOIN_ON($tablename, $operation);

# Add an INNER JOIN and ON clause
$builder->INNER_JOIN_ON($tablename, $operation);

# Add an LEFT JOIN and ON clause
$builder->LEFT_JOIN_ON($tablename, $operation);

# Add an RIGHT JOIN and ON clause
$builder->RIGHT_JOIN_ON($tablename, $operation);

# Add an OUTER JOIN and ON clause
$builder->OUTER_JOIN_ON($tablename, $operation);

Organisers

Query organizers are where you define how your results should be formatted. The following methods can be used to organize the query results:

# Set the ORDER BY keyword
$builder->ORDER_BY($columnName, $direction = 'DESC');

# Add an GROUP BY keyword
$builder->GROUP_BY($columnName);

# Add a GROUP_CONCAT function
$builder->GROUP_CONCAT($column, $operator, $value);

# Set the limit
$builder->LIMIT($offset, $number);

Executions

Query executions are the final method to call and will immediately execute the query and return the results.

# Execute the query and limit the results to single row
$builder->ROW();

# Execute the query, limit the results to single row
# if an id is provided add (id = $id) AND WHERE clause
$builder->FIND($id = null);

# Execute the query
$builder->FIND_ALL();

# Execute an INSERT, DELETE, UPDATE or SET query
$builder->QUERY();

Query Chains

Query chaining is where it all comes together and allows you to execute a query in the same syntax as if you were to write an SQL query statement. Here are some examples:

# Check if an author is registered under a given email address
$email = $builder->SELECT('*')
         ->export();>FROM('users')
         ->WHERE('email', '=', 'example@email.com')
         ->FIND();

# Check if an author is registered under a given email address and username
$author = $builder->SELECT('*')
          ->export();>FROM('users')
          ->WHERE('username', '=', 'johndoe')
          ->AND_WHERE('email', '=', 'example@email.com')
          ->ROW();

# Check if an author is registered under a multiple email addresses using nested
# Or statements
$authors = $builder->SELECT('*')
           ->FROM('users')
           ->WHERE('email', '=', ['foo@bar.com', 'bar@foo.com'])
           ->FIND_ALL();

# Get all autors that are confirmed
$users = $builder->SELECT('*')
         ->FROM('users')
         ->WHERE('status', '=', 'confirmed')
         ->FIND_ALL();

# Get all of a post's tags
$tags = $builder->SELECT('tags.*')
        ->FROM('tags_to_posts')
        ->LEFT_JOIN_ON('tags', 'tags.id = tags_to_posts.tag_id')
        ->WHERE('tags_to_posts.post_id', '=', 2)
        ->FIND_ALL();

# Insert a row into the categories table
$insert = $builder->INSERT_INTO('categories')
        ->VALUES([
            'name' => 'JavaScript',
            'slug' => 'javascript'
        ])
        ->QUERY();

# Update a post status
$update = $builder->UPDATE('posts')
        ->SET(['status' => 'published'])
        ->WHERE('id', '=', 5)
        ->QUERY();

# Delete some posts
$delete = $builder->DELETE_FROM('posts')
        ->WHERE('created', '<', strtotime('-5 months'))
        ->OR_WHERE('modified', '<', strtotime('-1 year'))
        ->QUERY();

Stats

Dependencies
0
Dependent packages
0
Dependent repositories
0
Total releases
1
Latest release
First release
Stars
1
Forks
0
Watchers
1
Contributors
1
Repository size
85.9 KB
SourceRank
5

Releases

dev-main

Contributors

Joe Howard


See all contributors

Login to resync this project