Install
Download library:
wget https://mysqly.com/mysqly.php
and use it in your app:
<?php
|
# Include Mysqly lib
|
Quick start
<?php
require "mysqly.php"; mysqly::auth("usr", "pwd", "db", "127.0.0.1"); print_r( mysqly::fetch("SELECT NOW()") );
|
# load Mysqly lib # connect to "db" # print Mysql server date/time
|
Array
(
[0] => Array
(
[NOW()] => 2024-09-08 16:35:39
)
)
|
Insert
Insert single row from associative array
The mysqly::insert() method will securely insert given data and return last inserted id (if there is id auto_increment column):
<?php
$last_id = mysqly::insert( 'users', ['age' => 46, 'gender' => 'x'] );
|
# returns last ID (if available) # name of the table to insert data # associative column/value array
|
INSERT INTO users
SET age = 46, gender = 'x'
|
Ignore duplicates on insert
Setting 3rd argument to true will ignore duplicates:
<?php
mysqly::insert( 'users', ['id' => 1, 'gender' => 'f'], true );
|
# insert data into specified table # name of the table to insert to # associative column/value array # will ignore duplicates if "true"
|
INSERT IGNORE INTO users
SET id = 1, gender = 'f'
|
Insert or update on duplicate keys
The mysqly::insert_update() methods allows to execute INSERT...ON DUPLICATE KEY UPDATE query based on a given row:
<?php
mysqly::insert_update( 'users', ['id' => 1, 'gender' => 'm'] );
|
# insert or update data if duplicate # name of the table to insert/update # associative column/value array
|
INSERT INTO users
SET id = 1, gender = 'm'
ON DUPLICATE KEY UPDATE
SET id = 1, gender = 'm'
|
Bulk multiple rows insert
The mysqly::multi_insert() method will execute optimized bulk insert of multiple rows:
<?php
mysqly::multi_insert( 'users', [ ['age' => 25, 'gender' => 'm'], ['age' => 26, 'gender' => 'f'], # ... ] );
|
# single query multiple rows insert # name of the table to insert/update # first row to insert # second row to insert # ...
|
INSERT INTO users(age, gender)
VALUES(25, 'm'), (26, 'f')
|
Data retrieval using parametric queries
Simple filtering based on column values
The mysqly::fetch() method gets data from Mysql based on specified filters and returns list of associative arrays.
Given parameters (column values) are automatically escaped based on secure binding and stored procedures.
<?php
$rows = mysqly::fetch( 'users', ['age' => 25, 'gender' => 'm'] );
|
# retrieves data from Mysql # table to select from # column value filters
|
SELECT * FROM users
WHERE age = 25 AND gender = 'm'
|
Sorting with parametric queries
Use 'order_by' parameter to specify sort expression:
<?php
$rows = mysqly::fetch( 'users', [ 'age' => 25, 'order_by' => 'age DESC' ] );
|
# retrieves data from Mysql # table to select from # # filter parameter # sorting parameter
|
SELECT * FROM users
WHERE age = 25
ORDER BY age DESC
|
Multiple-values parameters
Use arrays as parameter values to filter based on multiple values:
<?php
$rows = mysqly::fetch( 'users', ['id' => [2, 3, 4]] );
|
# retrieves data from Mysql # table to select from # array of multiple values to filter on
|
SELECT * FROM users
WHERE id IN (2, 3, 4)
|
Select data using native SQL
Plain SQL queries
The mysqly::fetch() method can also accept native SQL queries.
<?php
$rows = mysqly::fetch( 'SELECT id, age FROM users WHERE id > 5' ); print_r( $rows );
|
# retrieves data from Mysql # SQL query
|
Array
(
[0] => Array
(
[id] => 6
[age] => 40
)
[1] => Array
(
[id] => 7
[age] => 41
)
)
|
Binding values and stored procedures
Use values binding to secure SQL queries:
<?php
$rows = mysqly::fetch( 'SELECT * FROM users WHERE id = :id' [':id' => 5] ); print_r( $rows );
|
# retrieves data from Mysql # SQL query with values placeholders # values bindings
|
Array
(
[0] => Array
(
[id] => 5
[age] => 35
[gender] => f
)
)
|
"IN" queries binding support
Array values in bindings are supported for SELECT ... IN ... queries:
<?php
$rows = mysqly::fetch( 'SELECT id, age FROM users WHERE id IN (:ids)', ['ids' => [6, 7]] ); print_r( $rows );
|
# retrieves data from Mysql # SQL query with values placeholders # array value binding
|
Array
(
[0] => Array
(
[id] => 6
[age] => 40
)
[1] => Array
(
[id] => 7
[age] => 41
)
)
|
Single rows and scalar values
Use mysqly::row() to retrieve single table row or mysqly::one() to get single scalar value:
<?php
$user = mysqly::row('users', 25); $avg_age = mysqly::one('select avg(age) from users')
|
# returns single user with id = 25 # returns single value of average users age
|
Using cursors for large data volumes
Use mysqly::fetch_cursor() method to work directly with PDO cursor:
<?php
$st = mysqly::fetch_cursor('users', ['age' => 25]); while ( $r = $st->fetch() ) { echo $r['id']; }
|
# returns PDOStatement object # iterate through each row of result set
|
Native SQL queries are also supported:
<?php
$st = mysqly::fetch_cursor('SELECT * FROM users'); while ( $r = $st->fetch() ) { echo $r['id']; }
|
# returns PDOStatement object # iterate through each row of result set
|
Lists and key-values retrieval
Retrieve lists
The mysqly::array() method will return list of first column values from a result set.
<?php
$list = mysqly::array( 'SELECT id FROM users WHERE age = :age', [':age' => 25] ); print_r( $list );
|
# gets single column list from Mysql # SQL query # value bindings
|
Array
(
[0] => 2
[1] => 3
[2] => 4
[3] => 5
[4] => 6
)
|
Key-value pairs retrieval
The mysqly::key_vals() method will return associative (key-value) array of 1st and 2nd columns from result set respectively:
<?php
$key_vals = mysqly::key_vals( 'SELECT id, age FROM users WHERE id > 5', ); print_r( $key_vals );
|
# gets single key-value pairs from Mysql # SQL query
|
Array
(
[6] => 40
[7] => 41
)
|
Data retrieval based on dynamic methods
Get rows from table by parametric filters
Use mysqly::{table}($filters) method to get rows based on filters:
<?php
$users = mysqly::users( ['age' => 25] );
|
# get rows from "users" table # filters to select rows
|
SELECT * FROM users
WHERE age = 25
|
Get single row from table by "ID" column
Use mysqly::{table}_($id) method to get row based on specified value of ID column (should be numeric value):
<?php
$user = mysqly::users_(6);
|
# get row from "users" table by ID
|
SELECT * FROM users
WHERE id = 6
|
Get single column value by paremetric filter
Use mysqly::{table}_{column}($filters) method to get single column value:
<?php
$gender = mysqly::users_gender( ['age' => 43] );
|
# get "gender" column from "users" table # parametric filter
|
SELECT gender FROM users
WHERE age = 43
LIMIT 1
|
If you pass numeric value, this will be treated as id filter:
<?php
$gender = mysqly::users_gender(6);
|
# get "gender" column from "users" table # of a row with id = 6
|
SELECT gender FROM users
WHERE id = 6
LIMIT 1
|
Aggregations and random data
Count by parametric filters
Use mysqly::count() method to count rows by filters:
<?php
$total = mysqly::count( 'users', ['age' => 25] );
|
# count rows # table to count rows from # filters to count by
|
SELECT count(*)
FROM users
WHERE age = 25
|
Min/max/avg column values
Use mysqly::min_{column}(), mysqly::max_{column}() and mysqly::avg_{column}() methods
to get corresponding aggregate column value based on filters:
<?php
$avg = mysqly::avg_age( 'users', ['gender' => 'f'] );
|
# get average value of "age" column # table to get data from # filters to use
|
SELECT avg(age)
FROM users
WHERE gender = 'f'
|
Random row based on filters:
Use mysqly::random() method to get single random row from a table based on filters:
<?php
$random_user = mysqly::random( 'users', ['gender' => 'f'] );
|
# get random row from table # table to get data from # filters to use
|
SELECT * FROM users
WHERE gender = 'f'
ORDER BY RAND()
LIMIT 1
|
Atomic increment/decrement
Use mysqly::increment() method to atomically increment specified column values based on filters:
<?php
mysqly::increment( 'age', 'users', ['id' => 6] );
|
# atomically increase value by 1 # column to increment # table name # filters
|
UPDATE users
SET age = age + 1
WHERE id = 6
|
And mysqly::decrement() method to atomically decrease value:
<?php
mysqly::decrement( 'age', 'users', ['id' => 6] );
|
# atomically decrease value by 1 # column to increment # table name # filters
|
UPDATE users
SET age = age - 1
WHERE id = 6
|
Update data
Use mysqly::update() method to update rows with specified data based on filters:
<?php
mysqly::update( 'users', ['age' => 25] ['gender' => 'f'] );
|
# updates data in specified table # table to update data in # filter to update data based on # actual data to update
|
UPDATE users
SET gender = 'f'
WHERE age = 25
|
Remove data
Use mysqly::remove() method to remove rows from table based on filters:
<?php
mysqly::remove( 'users', ['age' => 25] );
|
# remove data from table # table to remove data from # filters to remove data based on
|
DELETE FROM users
WHERE age = 25
|
Custom queries execution
Use mysqly::exec() to execute custom queries that don't return values:
<?php
mysqly::exec( 'UPDATE counters SET val = val * 2 WHERE val <= 3 AND name = :name' [':name' => 'visits'] );
|
# execute SQL query directly # SQL to execute # value bindings
|
Transactions
Use mysqly::transaction() method to execute transactions:
<?php
mysqly::transaction(function() { mysqly::remove('test', ['age' => 25]); // other queries... return false; });
|
# execute callback in a transaction # queries within transaction # # return "false" to rollback transaction
|
START TRANSACTION
DELETE FROM users WHERE age = 25
-- other queries
ROLLBACK
|
Return true from callback to commit transaction:
<?php
mysqly::transaction(function() { mysqly::remove('test', ['age' => 25]); // other queries... return true; });
|
# execute callback in a transaction # queries within transaction # # return "true" to commit transaction
|
START TRANSACTION
DELETE FROM users WHERE age = 25
-- other queries
COMMIT
|
Advanced authentication
Basic Mysql server authentication:
<?php
mysqly::auth( "usr", "pwd", "db", "127.0.0.1" );
|
# connnects to Mysql server # username # password # database name # Mysql host IP address
|
System-wide credentials
You can create /var/lib/mysqly/.auth.php
file with credentials that will be automatically used to authenticate library:
<?php
return [
'user' => 'user',
'pwd' => 'pwd',
'db' => 'db',
'host' => '127.0.0.1'
];
# # username # password # database name # Mysql host IP address
|
Multiple DB/server connections
In order to use multiple connections, add child class for every connection:
<?php
class db2 extends mysqly {} mysqly::auth('usr1', 'pwd1', 'db1'); db2::auth('usr2', 'pwd2', 'db2');
|
# extend "db2" from "mysqly" class to use multiple connections # connect mysqly to one Mysql server # connect db2 to another Mysql server
|
Working with JSON
Automatically retrieve JSON columns as associative arrays
To automatically retrieve JSON data as associative arrays, append _json postfix to column names (or aliases):
<?php
$rows = mysqly::fetch( 'SELECT id, settings as data_json FROM table' ); print_r($rows);
|
# SQL query # # use "_json" postfix for column alias # table to select data from
|
Array
(
[0] => Array
(
[id] => 1
[settings] => Array
(
[color] => red
[background] => black
)
)
)
|
Updating JSON attributes
Use dots (.) in column names to automatically treat them as JSON:
<?php
mysqly::update( 'table' [id => 1], ['settings.color' => 'red'] );
|
# update specified table data # table name to update # filter to update rows based on # update "color" key of "settings" JSON
|
UPDATE table
SET settings = JSON_SET(settings, '$.color', 'red')
WHERE id = 1
|
Auto table/field creation mode
Mysqly supports automatic table creation/alter mode (useful on early stages of prototyping).
Enable table/field automatic creation
Use mysqly::auto_create() method to enable or disable automatic creation/alter:
<?php
mysqly::auto_create(true); mysqly::auto_create(false);
|
# Enable automatic table/field creation # Disable automatic table/field creation
|
Using automatic table/field creation
When auto_create mode is on,
mysqly::insert(), mysqly::update() and mysqly::insert_update()
methods will automatically create absent tables or fields.
Columns named id will be created as BIGINT PRIMARY KEY while
all other columns will be created with TEXT type.
<?php
mysqly::auto_create(true); mysqly::insert('t1', ['id' => 1]); mysqly::update('t1', ['name' => 'Donald']);
|
# Enable automatic table/field creation # "t1" table will be created with "id" column if absent # "name" column will be added to "t1" table if abset
|
Key-value storage
Mysqly includes set of methods to work with built-in key/value storage on top of Mysql Engine.
Key-value storage is an InnoDB table, which is automatically created upon first request.
Settings values
Use mysqly::set() method to set values by keys:
<?php
mysqly::set( 'some_key' 'some_value' ); echo mysqly::get('some_key');
|
# set value for the specified key # key name # key value # # print value of "some_key"
|
some_value
|
Getting values
Use mysqly::get() method to get values by keys:
<?php
echo mysqly::get( 'some_key' );
|
# get value of specified key # key name to get value for
|
some_value
|
Using multiple key-value storages
Specify space name as last argument for mysqly::get() and mysqly::set() methods to use multiple separate key-value storages:
<?php
mysqly::set('some_key', 'some_value', 'space1'); mysqly::set('some_key', 'other_value', 'space2'); echo mysqly::get('some_key', 'space1'); echo mysqly::get('some_key', 'space2');
|
# set key value for "space1" space # set key value for "space2" space # print "some_key" from "space1" # print "some_key" from "space2"
|
some_value
other_value
|
Read more about implementing key-value storage on top of Mysql.
Job queue
Job queue component allows queuing events with additonal data and then reading from this queue in a FIFO order.
Queue is implemented on top of InnoDB table (named _queue, automatically created upon first request) and uses locks to allow atomicity.
Writing job to queue
Use mysqly::write() method to add event to queue:
<?php
mysqly::write( 'event1' ['some' => 'data'] );
|
# adds event to queue # type of the event (any string, 32 chars max) # job data to send to queue
|
Reading jobs from queue
Use mysqly::read() method to read next job from queue by event type:
<?php
$job_data = mysqly::read( 'event1' );
|
# fetch next job from queue # type of the event to fetch data from queue
|
Subscribing to queue events
Use mysqly::on() method to subscribe to queue by event type.
Warning.
This method will lock code execution and wait for new events from queue (useful for background scripts):
<?php
mysqly::on( 'event1', function($job_data) { // do somethind with $job_data } );
|
# subscribe to queue event # type of the event to subscribe to # callback will be called when next event arrives from queue # implemenent job handler
|
Read about implementation of job queue on top of Mysql InnoDB engine.
Cache storage with TTL support
Cache component allows using cache storage on top of InnoDB table.
_cache table will be atomatically created upon first cache request.
Warning! Cache table is not cleared automatically, so it is recommended to delete
old data from _cache table periodically (by cron).
Caching and getting cached data
Unlike usual implementation (get/set methods), Mysqly implements single mysqly::cache() method,
that will automatically return cached value or get it from source (through callback):
<?php
$value = mysqly::cache( 'key1' function() { return 'hi'; } 90 );
|
# return cached data or update cache # name of the cache key # callback is called if nothing cached or expired # "slow" code that needs to be cached # # cache TTL in seconds
|
Remove cached data
Use mysqly::uncache() method to remove cached data by key:
<?php
mysqly::uncache( 'key1' );
|
# removes cached value by key # name of the key to remove data from
|
Read more about implementing cache storage on top of Mysql.
Exporting data to CSV/TSV
Use mysqly::export_csv() or mysqly::export_tsv() to export data to CSV/TSV files:
<?php
mysqly::export_csv( '/tmp/file.csv' 'users' ['age' => 25], 'id, age, gender' );
|
# exports table data to CSV file # path to CSV file # name of the table # filters # list/order of columns to export
|
> cat /tmp/file.csv
2,25,"m"
3,30,"f"
...
|
Atomically toggle column value
Use mysqly::toggle() to atomically update specific column value to opposite one from given pair:
<?php
mysqly::toggle( 'products' ['id' => 123], 'is_enabled' 1, 0 );
|
# toggle column value # name of the table # condition to update table on # name of the column to toggle # pair of values to toggle between
|
UPDATE products
SET is_enabled = IF(is_enabled = 1, 0, 1)
WHERE id = 123
|