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

Mr Crypster

Last update: Dec 10, 2022

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]);

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

442 Jan 6, 2023

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

1 Mar 6, 2022

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

49 Jan 4, 2023

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