Tiny php mysql lib (PDO-based) with handy fetch/update functionality, supports both SQL and parametric queries

Related tags

Database mysqly
Overview

Micro PHP mysql lib (~ 200 lines of code) with ultra powerful CRUD for faster than ever development:

  • parametric fetch/insert/update/delete (based on associative arrays): fetch('table', ['col' => 10])
  • native SQL queries support
  • values binding for security
  • PDO-based, no extra dependencies
  • near-zero overhead because of static class in a single file
  • builtin IN support: fetch('table', ['id' => [1, 2, 3]])
  • magic methods for even faster development:
    • get column value from a table by id
    • get single row from a table by id
    • get count/avg/sum/max/min from table by parametric filter (implementing in #6)

Installation

Just download latest version of lib:

wget https://raw.githubusercontent.com/mrcrypster/mysqly/main/mysqly.php

Usage

Include:

require 'mysqly.php';

Authenticate:

mysqly::auth('user', 'pwd', 'db', 'localhost');

Fetch something:

$rows = mysqly::fetch('SELECT NOW()');
print_r( $rows );

Better authentication

To make authentication more secure, you can create auth file /var/lib/mysqly/.auth.php with auth data:

<?php return [
  'user' => 'user',
  'pwd' => 'pwd',
  'db' => 'db'
];

Fetch data

Parametric fetch

$users = mysqly::fetch('users', [ 'age' => 45 ]);
# SELECT * FROM users WHERE age = 45

Fetch rows from table by ID

$user = mysqly::fetch('users', 45)[0]; # ! you'll have to select only first row from results
# SELECT * FROM users WHERE id = 45

Parametric sorting

$users = mysqly::fetch('users', [ 'age' => 45, 'order_by' => 'id DESC' ]);
# SELECT * FROM users WHERE age = 45 ORDER BY id DESC

Secure parametric IN support

$users = mysqly::fetch('users', [ 'age' => [45, 46, 47] ]);
# SELECT * FROM users WHERE age IN (45, 46, 47)

Fetch using standard SQL

$users = mysqly::fetch('SELECT * FROM users');

Binding

$users = mysqly::fetch('SELECT * FROM users WHERE age = :age', [ ':age' => $_GET['age'] ]);

Secure IN binding

$users = mysqly::fetch('SELECT * FROM users WHERE age IN (:ages)', [ 'ages' => [45, 46, 47] ]);
# SELECT * FROM users WHERE age IN (45, 46, 47)

Fetch single column list (one-dimensional array)

$ids = mysqly::array('SELECT id FROM users');

Fetch key-value pairs (one-dimensional associative array)

$ages = mysqly::key_vals('SELECT id, age FROM users');
# example resulting array: [ 1 => 45, 2 => 46 ... ]

Fetch random row based on parametric query

$row = mysqly::random('users', ['age' => 25]);
# SELECT * FROM users WHERE age = 25 ORDER BY RAND() LIMIT 1

Magic fetch

Set of magic methods (not directly defined, but dynamically handled) allows quick access in the following cases:

Select single column value from a table by id

$name = mysqly::users_name(45);
# SELECT name FROM users WHERE id = 45

Select single column value from a table by custom parameters

$name = mysqly::users_name(['col' => 'val']);
# SELECT name FROM users WHERE col = 'val' LIMIT 1

Select whole row (all columns) from table by id

$user = mysqly::users_(45);
# SELECT * FROM users WHERE id = 45 LIMIT 1

Select list of rows from table by parameters

$users = mysqly::users(['age' => 35]);
# SELECT * FROM users WHERE age = 35

Insert data

Insert single row as associative array

mysqly::insert('users', ['age' => 46, 'gender' => 'x']);

Insert with ignore (if duplicate primary/unique key)

mysqly::insert('users', ['age' => 46, 'gender' => 'x'], true);

Insert update (update data if duplicate primary/unique key)

mysqly::insert_update('users', ['age' => 46, 'gender' => 'x']);

Update data

To update gender column to "x" for all users with age = 45:

mysqly::update('users', ['age' => 45], ['gender' => 'x']);
#               ▲        ▲              ▲
#               table    filter         update

Remove data

To remove all rows with age = 46:

mysqly::remove('users', ['age' => 46]);
You might also like...
insert batch and update batch in laravel

Laravel BATCH (BULK) Insert and update batch (bulk) in laravel Install composer require mavinoo/laravel-batch Service Provider file app.php in array p

A php securised login system, using Hash, Salt and prevent from SQL Injections

A Basic Secure Login Implementation Hashed & Salted password ( only hashed in ShA-512 for now ) No SQL injection possible Prevent XSS attacks from the

Easily exclude model entities from eloquent queries
Easily exclude model entities from eloquent queries

Laravel Excludable Easily exclude model entities from eloquent queries. This package allows you to define a subset of model entities who should be exc

A Laravel package to output a specific sql to your favourite debugging tool. The supported log output is Laravel Telescope, Laravel Log, Ray, Clockwork, Laravel Debugbar and your browser.
A Laravel package to output a specific sql to your favourite debugging tool. The supported log output is Laravel Telescope, Laravel Log, Ray, Clockwork, Laravel Debugbar and your browser.

Laravel showsql A Laravel package to output a specific sql to your favourite debugging tool, your browser or your log file. Use case You often want to

A minimalistic implementation of asynchronous SQL for PHP.

libSQL A minimalistic implementation of asynchronous SQL for PHP. Installation via DEVirion Install the DEVirion plugin and start your server. This wi

ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).
ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).

ATK Data - Data Model Abstraction for Agile Toolkit Agile Toolkit is a Low Code framework written in PHP. Agile UI implement server side rendering eng

A SQL query builder with zero dependencies

Latitude Query Builder A SQL query builder with zero dependencies. Attempts to be PSR-1, PSR-2, and PSR-4 compliant. Install composer require latitude

SQL to Laravel Query Builder

Marwan - SQL To Laravel Builder SQL to Laravel Query Builder, A Converter written in PHP Features Converts SQL Queries to Laravel Query Builder. Assis

Extract SQL statements from migrations

This is my package MigrationToSql To install: composer require bcleverly/migrationtosql --dev This repo is here to help you extract the SQL queries fr

Releases(1.0)
Owner
Mr Crypster
I do large-scale crypto-trading systems (mostly binance), php frameworks and libraries for ultra-fast small-overhead prototyping
Mr Crypster
SQL database access through PDO.

Aura.Sql Provides an extension to the native PDO along with a profiler and connection locator. Because ExtendedPdo is an extension of the native PDO,

Aura for PHP 533 Dec 30, 2022
Very easy to use PDO MYSQL API. Just Include in PHP file and get it working.

CRUD-MYSQL-API Very easy to use PDO MYSQL API. Just Include in PHP file and get it working. INSTALATION Step 1: git clone https://github.com/arhex-lab

Abdul Raheem 4 Jun 14, 2022
Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Kodols 9 Dec 22, 2022
The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

Mark Armendariz 0 Jan 7, 2022
Independent query builders for MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.

Aura.SqlQuery Provides query builders for MySQL, Postgres, SQLite, and Microsoft SQL Server. These builders are independent of any particular database

Aura for PHP 424 Dec 12, 2022
A validating SQL lexer and parser with a focus on MySQL dialect.

SQL Parser A validating SQL lexer and parser with a focus on MySQL dialect. Code status Installation Please use Composer to install: composer require

phpMyAdmin 368 Dec 27, 2022
Easy-to-use PDO wrapper for PHP projects.

EasyDB - Simple Database Abstraction Layer PDO lacks brevity and simplicity; EasyDB makes separating data from instructions easy (and aesthetically pl

Paragon Initiative Enterprises 705 Dec 9, 2022
You can sync any number of PDO supported databases

Features: Can backup any number of databases. No need to introduce column name only table name The Last id based data backup Support 12 different data

Tharusha Kavishan Udumulla 4 Aug 27, 2021
Just another PDO database library

PDO Just another PDO database library Installation Use Composer $ composer require faapz/pdo Usage Examples selecting, inserting, updating and deletin

Fabian de Laender 313 Oct 11, 2022
A simple library for managing database connections, results pagination and building queries in PHP

PHP lions-software-database-manager This is a simple library for managing database connections, results pagination and building queries in PHP. Esta é

Lions Software 0 Feb 7, 2022