Download the PHP package theodorejb/peachy-sql without Composer
On this page you can find all versions of the php package theodorejb/peachy-sql. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download theodorejb/peachy-sql
More information about theodorejb/peachy-sql
Files in theodorejb/peachy-sql
Package peachy-sql
Short Description A small, speedy database abstraction layer for PHP
License MIT
FAQ
Example:
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 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.
Informations about the package peachy-sql
PeachySQL
PeachySQL is a speedy database abstraction layer which makes it easy to execute prepared statements and work with large amounts of data. It supports both MySQL and SQL Server, and runs on PHP 7.4+.
Install via Composer
composer require theodorejb/peachy-sql
Usage
Start by instantiating the Mysql or SqlServer class with a database connection,
which should be an existing mysqli object
or SQLSRV connection resource:
or
After instantiation, arbitrary statements can be prepared by passing a
SQL string and array of bound parameters to the prepare method:
Most of the time prepared statements only need to be executed a single time.
To make this easier, PeachySQL provides a query method which automatically
prepares, executes, and closes a statement after results are retrieved:
Both prepare and query return a Statement object with the following methods:
| Method | Behavior |
|---|---|
execute |
Executes the prepared statement (automatically called when using query). |
getIterator |
Returns a Generator object which can be used to iterate over large result sets without caching them in memory. |
getAll |
Returns all selected rows as an array of associative arrays. |
getFirst |
Returns the first selected row as an associative array (or null if no rows were selected). |
getAffected |
Returns the number of rows affected by the query. |
close |
Closes the prepared statement and frees its resources (automatically called when using query). |
If using MySQL, the Mysql\Statement object additionally includes a getInsertId method.
Internally, getAll and getFirst are implemented using getIterator.
As such they can only be called once for a given statement.
Shorthand methods
PeachySQL comes with five shorthand methods for selecting, inserting, updating, and deleting records.
Note: to prevent SQL injection, the queries PeachySQL generates for these methods always use bound parameters for values, and column names are automatically escaped.
select / selectFrom
The selectFrom method takes a single string argument containing a SQL SELECT query.
It returns an object with three chainable methods:
whereorderByoffset
Additionally, the object has a getSqlParams method which builds the select query,
and a query method which executes the query and returns a Statement object.
The select method works the same as selectFrom, but takes a SqlParams object
rather than a string and supports bound params in the select query:
Where clause generation
In addition to passing basic column => value arrays to the where method, you can
specify more complex conditions by using arrays as values. For example, passing
['col' => ['lt' => 15, 'gt' => 5]] would generate the condition WHERE col < 15 AND col > 5.
Full list of recognized operators:
| Operator | SQL condition |
|---|---|
| eq | = |
| ne | <> |
| lt | < |
| le | <= |
| gt | > |
| ge | >= |
| lk | LIKE |
| nl | NOT LIKE |
| nu | IS NULL |
| nn | IS NOT NULL |
If a list of values is passed with the eq or ne operator, it will generate an
IN(...) or NOT IN(...) condition, respectively. Passing a list with the lk, nl,
nu, or nn operator will generate an AND condition for each value. The lt, le,
gt, and ge operators cannot be used with a list of values.
insertRow
The insertRow method allows a single row to be inserted from an associative array.
It returns an InsertResult object with getId and getAffected methods.
insertRows
The insertRows method makes it possible to bulk-insert multiple rows from an array.
It returns a BulkInsertResult object with getIds, getAffected, and getQueryCount methods.
An optional third parameter can be passed to insertRows to override the default
identity increment value:
Note: SQL Server allows a maximum of 1,000 rows to be inserted at a time, and limits
individual queries to 2,099 or fewer bound parameters. MySQL supports a maximum of
65,536 bound parameters per query. These limits can be easily reached when attempting
to bulk-insert hundreds or thousands of rows at a time. To avoid these limits, the
insertRows method automatically splits large queries into batches to efficiently
handle any number of rows (getQueryCount returns the number of required batches).
updateRows and deleteFrom
The updateRows method takes three arguments: a table name, an associative array of
columns/values to update, and a WHERE array to filter which rows are updated.
The deleteFrom method takes a table name and a WHERE array to filter the rows to delete.
Both methods return the number of affected rows.
Transactions
Call the begin method to start a transaction. prepare, execute, query
and any of the shorthand methods can then be called as needed, before committing
or rolling back the transaction with commit or rollback.
Author
Theodore Brown
http://theodorejb.me
License
MIT
