Download the PHP package ramphor/sql without Composer

On this page you can find all versions of the php package ramphor/sql. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.

FAQ

After the download, you have to make one include require_once('vendor/autoload.php');. After that you have to import the classes with use statements.

Example:
If you use only one package a project is not needed. But if you use more then one package, without a project it is not possible to import the classes with use statements.

In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
Some PHP packages are not free to download and because of that hosted in private repositories. In this case some credentials are needed to access such packages. Please use the auth.json textarea to insert credentials, if a package is coming from a private repository. You can look here for more information.

  • Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
  • To use Composer is sometimes complicated. Especially for beginners.
  • Composer needs much resources. Sometimes they are not available on a simple webspace.
  • If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
  • Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Please rate this library. Is it a good library?

Informations about the package sql

Raw SQL Query Builder

Raw SQL Query Builder ~ the Swiss-army knife of raw SQL queries

Introduction

We already have some great tools when working with managed or abstracted database layers like ORM's and Doctrine DBAL. And most ORM's allow you to write and execute raw SQL queries when you require greater/custom flexibility or functionality they don't provide.

However, what tools do you have when working with the plain text strings of raw/native SQL queries? You have lots of string concatenations, implode(), PDO::prepare, PDO::quote, sprintf (for the brave) and mysqli::real_escape_string (because the first version wasn't real enough or the name long enough).

The One Ring to rule them all, One Ring to bind them

Introducing the 'Raw SQL Query Builder' (SQLQB); combining all the functionality of having placeholders like ?, :id, %s, %d; with an ORM style 'fluent interface' (methods return $this for method-chaining) and much more.

It's the glue that sits between $sql = '...'; and $db->query($sql). The part where you have to concatenate, 'escape', 'quote', 'prepare' and 'bind' values in a raw SQL query string.

This is not an ORM or replacement for an ORM, it's the tool you use when you need to create a raw SQL query string with the convenience of placeholders. It doesn't 'prepare' or 'execute' your queries exactly like PDO::prepare does; but it does support the familiar syntax of using ? or :id as placeholders. It also supports a subset of sprintf's %s / %d syntax.

In addition, it supports inserting 'raw' strings (without quotes or escapes) with @; eg. sql('dated = @', 'NOW()'), even replacing column or table names as well as auto-implode()ing arrays with [] eg. sql('WHERE id IN ([])', $array')

No need for escaping, no quotes, no array handling and no concatenations ...

Output:

Description

SQLQB is essentially just a glorified string wrapper targeting SQL query strings with multiple ways to do the same thing, depending on your personal preference or coding style (supports multiple naming conventions, has camelCase and snake_case function names, or you can write statements in the constructor). Designed to be 100% Multibyte-capable (UTF-8, depending on your mb_internal_encoding() setting, all functions use mb_* internally), supports ANY database (database connection is optional, it's just a string concatenator, write the query for your database/driver your own way) and supports ANY framework (no framework required or external dependencies), light-weight (one variable) but feature rich, stateless (doesn't know anything about the query, doesn't parse or validate the query), write in native SQL language with zero learning curve (only knowledge of SQL syntax) and functionality that is targeted to rapidly write, design, test, build, develop and prototype raw/native SQL query strings. You can build entire SQL queries or partial SQL fragments or even non-SQL strings.

History

I got the initial inspiration for this code when reading about the MyBatis SQL Builder Class; and it's dedicated to the few; but proud developers that love the power and flexibility of writing native SQL queries! With great power ...

It was originally designed to bridge the gap between ORM query builders and native SQL queries; by making use of a familiar ORM-style 'fluent interface', but keeping the syntax as close to SQL as possible.

Speed and Safety

This library is not designed for speed of execution or to be 100000% safe from SQL injections, it WILL however do a better job than manually escaping strings yourself; but only real 'prepared statements' offer protection from SQL injections; however they add a lot more complexity and many restrictions. In reality, it's almost impossible to write an entire website using only real/true prepared statements, so you'll invariably have to write many 'unprepared' statements; and that is where this class can help you; by writing safer 'unprepared' statements! It will 'quote' and 'escape' strings, detect the correct data type to use; but it doesn't do syntax checking, syntax parsing, query/syntax validation etc. The main task is to replace your placeholders with the corresponding data, with the ability to auto-detect the data type.

To simplify the complex

This class isn't particularly useful or necessary for small/static queries like 'SELECT * FROM users WHERE id = ' . $id;

But it really starts to shine when your SQL query gets larger and more complex; really shining on INSERT and UPDATE queries. The larger the query, the greater the benefit; that is what it was designed to do. All the complexity and tedious work of 'escaping', 'quoting' and concatenating strings is eliminated by simply putting ? where you want the variable, this library takes care of the rest.

So when you find yourself dealing with 'object-relational impedance mismatch'; because you have a database of 400+ tables, 6000+ columns/fields, one table with 156 data fields, 10 tables with over 100 fields, 24 tables with over 50 fields, 1000+ varchar/char fields as I have; just remember this library was designed to help reduce some of that complexity! Especially still having (semi-)readable queries when you come back to them in a few months or years makes it a joy to use.

Limitations of 'real' prepared statements

One of the limitations is that you cannot do this: WHERE ? = ? which you can in this class, another limitation is that you basically cannot use NULL values (there are workarounds). Also, you cannot use dynamic column/table/field names, such as SELECT ? FROM ?, all of which you can with this class; anything you can do in your $db->query($sql) you can do here!

Install

Composer

manually

or from GIT

Requirements (similar to Laravel):

Hello World

Hello SQL World

Note: 'numeric' values (like the "2" above) are not quoted (even when they are string values). PHP null values become SQL NULL values.

More Examples

"NOW()" is an SQL function that will not be executed, use @ for raw output strings

"Trevor's" is not escaped with @ and will produce an SQL error

Fluent Style

Queries include additional whitespace for formatting and display purposes, which can be removed by calling Sql::singleLineStatements(). SQL keywords can be made lower-case by calling Sql::lowerCaseStatements()

Other features

Arrays:

Range:

Text filters:

eg. trim, pack (merge internal whitespace) & crop to 20 characters

Beginners guide

There are two main ways to write your queries; either use the constructor like an sprintf function (eg. sql('?', $value)), or use the 'fluent interface' (method chaining) by calling sql()->select(...)->from(...)->where(...) etc.

fluent interface

The general idea of is very simple; when you call a function, it essentially just appends the function/statement name (eg. select(...), from(...), where(...)) (with some extra whitespace) to the internal $sql string variable, and returns $this for method chaining.

pseudo-code

Some functions like leftJoin and where support the prepare/sprintf style with variable args, while other like the select and from are more conveniently coded to just implode your values.

Multiple calling conventions

The code supports camelCase, snake_case and UPPER_CASE syntax; as well as a short form syntax:

Constructor

Convenient sql() function

camelCase

snake_case

UPPER_CASE

short syntax

Addendum

Setting the connection

The connection only needs to be set ONCE for ALL the sql() objects you create. You do NOT need a new connection, you just give your normal connection object to the class; and it will extract what it needs and build an internal 'driver'. The connection is stored in a static class variable, so ALL instances of the class share the same connection.

The connection type is automatically detected: either a PDO, MySQLi or SQLite3 object, or PostgreSQL/MySQL resource connection.

It will be necessary to set the connection to take full advantage of all the features offered by the class.

Once the connection is set, the class (and all the sql() instances you create afterwards) will use your connection to 'escape' and 'quote' strings, and you have the ability to execute your queries directly from the class if you want. Executing queries with the class is entirely optional, but very convenient!

query(), exec(), fetchAll(), lookup()

There are 4 very light-weight functions you can call directly from the sql object. All connection types have been unified.

fetchAll()

Returns an array containing all of the result set rows as an associated array.

Based on PDOStatement::fetchAll

PDO code sample

lookup()

lookup() will return a single row of data, or a single value depending on how many columns you select. If you select one column, you will get a value directly in $value or a null. If you selected several columns, they will be returned as an associative array, where the keys are the column names. Only the first row is returned, any other results will be discarded.

This function works similar to SQLite3::querySingle, except the result 'mode' is auto-detected, which corresponds to the $entire_row value in SQLite3::querySingle

query()

query() will execute the SQL query with the query() function of your database connection, returning the same result. This is a very thin wrapper, making it extremely fast and convenient to use; it's much more convenient than getting the connection object from a dependency injection/IoC container like $container->db()->query($sql);, it's just sql(..)->query()

exec()

exec() executes an SQL query which you do not expect a 'query' result. This is usually an INSERT, UPDATE or DELETE statement. The affected rows value is returned on MySQL and SQLite3, and is the same as returned from PDO::exec().

This function calls PDO::exec(), MySQLi->real_query() and SQLite3::exec() internally depending on your connection type.

Literal ? and @

PDO will support ?? as a literal ? in future editions; as proposed by the PDO standard for PHP 7.2 here

This class also supports ?? for a literal ? in your code, as well as @@ and %% for literal @ and %

Features:

What it doesn't do:

Conclusion

My goal is to enable you (and me) to write SQL queries faster, safer and more readable than old-school concatenations.

They might not execute faster, especially when the regular expression engine kicks in, but the amount of time you will save, and coming back to your code in a few months or years later and immediately being able to read and understand it is invaluable! Code readability should come first in most situations, especially large queries/projects.

I believe this code and solution is unique; as I haven't found anything like it before; there simply are NO other libraries out there with the same capabilities and feature set; and very few help you write raw SQL queries faster.

I hope you enjoy this effort, it has taken me many weeks (hundreds of hours) of my free time to write this code and documentation.

I'd love to hear from anyone else making use of the code! Features, suggestions, praise, questions, comments and thanks are welcome!

Trevor out...


All versions of sql with dependencies

PHP Build Version
Package Version
Requires php Version >=5.6.4
ext-mbstring Version *
Composer command for our command line client (download client) This client runs in each environment. You don't need a specific PHP version etc. The first 20 API calls are free. Standard composer command

The package ramphor/sql contains the following files

Loading the files please wait ....