Mysqly / Mysql framework for PHP

Install

Download library:

wget https://mysqly.com/mysqly.php

and use it in your app:

<?php
require "mysqly.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-12-06 03:46:41
         )
 
 )

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

No-shit Mysql framework for PHP