Skip to content
This repository was archived by the owner on Oct 25, 2026. It is now read-only.

Latest commit

 

History

History
215 lines (159 loc) · 6.65 KB

doc.md

File metadata and controls

215 lines (159 loc) · 6.65 KB

php-mysql-cli - Documentation

Last updated: 2026-10-25

Back to README

Prerequisites

  • PHP 8.0 or higher
  • The PHP pdo_mysql extension
  • Composer
  • MySQL 5.7 or higher; total() requires MySQL 8.0+ (window functions)

Installation

Using Composer

composer require pardnchiu/mysql-cli

From Source

git clone https://github.com/pardnio/php-mysql-cli.git
cd php-mysql-cli
composer install

The package maps pardnchiu\ to src/ via PSR-4.

Configuration

Environment Variables

<TARGET> is READ or WRITE; every target you use needs all six variables, because the defaults in the source never apply due to cast precedence.

Variable Required Description
DB_<TARGET>_HOST Yes Host
DB_<TARGET>_PORT Yes Port
DB_<TARGET>_USER Yes Username
DB_<TARGET>_PASSWORD Yes Password
DB_<TARGET>_DATABASE Yes Database name
DB_<TARGET>_CHARSET Yes Charset, utf8mb4 recommended
DB_READ_HOST=127.0.0.1
DB_READ_PORT=3306
DB_READ_USER=read_user
DB_READ_PASSWORD=read_password
DB_READ_DATABASE=app
DB_READ_CHARSET=utf8mb4

DB_WRITE_HOST=127.0.0.1
DB_WRITE_PORT=3306
DB_WRITE_USER=write_user
DB_WRITE_PASSWORD=write_password
DB_WRITE_DATABASE=app
DB_WRITE_CHARSET=utf8mb4

Connection Behavior

Item Value
Retry Up to 3 attempts with 100 ms and 200 ms gaps; the last failure rethrows the original exception
Session timeout wait_timeout and interactive_timeout set to 600 seconds
PDO options ERRMODE_EXCEPTION, FETCH_ASSOC
Slow query Queries over 20 ms log [Info] PD\SQL: [Slow Query: <ms>ms] [<sql>]
Connection sharing The connection is a static property created for the first target used and shared for the whole request; call SQL::close() before switching targets

Usage

Basic Query

<?php

require __DIR__ . '/vendor/autoload.php';

use pardnchiu\SQL;

$users = SQL::table("users")
    ->where("status", "active")
    ->where("age", ">", 18)
    ->orderBy("created_at", "DESC")
    ->limit(20)
    ->get();

JOIN, Aggregation, and Pagination Total

<?php

require __DIR__ . '/vendor/autoload.php';

use pardnchiu\SQL;

$page = 2;
$rows = SQL::table("orders")
    ->total()
    ->leftJoin("users", "orders.user_id", "users.id")
    ->select("orders.user_id", "COUNT(orders.id) AS order_count", "SUM(orders.amount) AS amount")
    ->where("orders.created_at", ">=", "2026-01-01")
    ->groupBy("orders.user_id")
    ->orderBy("amount", "DESC")
    ->limit(10)
    ->offset(($page - 1) * 10)
    ->get();

$total = $rows[0]["total"] ?? 0;

Writes

<?php

require __DIR__ . '/vendor/autoload.php';

use pardnchiu\SQL;

$id = SQL::table("users", "WRITE")->insertGetId([
    "name"       => "Pardn",
    "email"      => "dev@example.com",
    "created_at" => "2026-10-25 12:00:00",
]);

$result = SQL::table("users", "WRITE")
    ->where("id", $id)
    ->update([
        "last_login" => "NOW()",
        "status"     => "active",
    ]);

echo $result["affected_rows"];

Advanced: Raw SQL and Error Handling

<?php

require __DIR__ . '/vendor/autoload.php';

use pardnchiu\SQL;

try {
    $rows = SQL::read(
        "SELECT u.id, COUNT(o.id) AS orders FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > ? GROUP BY u.id",
        ["2026-01-01"]
    );

    SQL::close();

    SQL::write("UPDATE users SET status = ? WHERE id = ?", ["inactive", 42]);
} catch (\InvalidArgumentException $e) {
    error_log("[Invalid] sql: " . $e->getMessage());
} catch (\Throwable $e) {
    error_log("[Upstream] sql: " . $e->getMessage());
}

API Reference

Entry Points and Connection

Method Signature Description
table table(string $table, string $target = "READ"): static Connects if needed and resets builder state
read read(string $query, array $params = []): array Runs raw SQL on the READ target
write write(string $query, array $params = []): array Runs raw SQL on the WRITE target
query query(string $query, array $params = []): array Runs on the current connection; throws \Exception when not connected
close close(): void Drops the connection and clears state

Builder

Method Signature Description
select select(string ...$fields) or select(array $fields) Defaults to *; fields without . are wrapped in backticks whole, so expressions need a . (e.g. COUNT(orders.id)) to stay unquoted
total total(): static Wraps the query in SELECT COUNT(*) OVER() AS total, data.*
join / innerJoin join($table, $first, $operator, $second = null) INNER JOIN; omitting $second makes the operator =
leftJoin Same as above LEFT JOIN
rightJoin Same as above RIGHT JOIN
where where($column, $operator, $value = null) Omitting $value means =; LIKE wraps the value as %value%; conditions join with AND
orderBy / order orderBy($column, $direction = "DESC") Accepts only ASC / DESC, otherwise throws \InvalidArgumentException
groupBy groupBy(string ...$columns) or groupBy(array $columns) GROUP BY
limit limit(int $limit) LIMIT
offset offset(int $offset) OFFSET

Execution

Method Returns Description
get() array Result rows as associative arrays
insert(array $data) / insertGetId(array $data) string|null Inserts one row and returns lastInsertId
update(array $data) array ["info" => string, "insert_id" => string, "affected_rows" => int]; without where() the whole table is updated

In update(), values matching these function names (case-insensitive) are written verbatim instead of bound: NOW(), CURRENT_TIMESTAMP, UUID(), RAND(), CURDATE(), CURTIME(), UNIX_TIMESTAMP(), UTC_TIMESTAMP(), SYSDATE(), LOCALTIME(), LOCALTIMESTAMP(), PI(), DATABASE(), USER(), VERSION().

Exceptions

Case Exception
Executing before table() \Exception("Invalid: Table (null).")
Empty query passed to read() / write() \InvalidArgumentException("Invalid: Query (empty).")
Non-array params passed to read() / write() \InvalidArgumentException("Invalid: Params (not an array).")
Invalid sort direction \InvalidArgumentException("Invalid: Direction (<dir>).")
Connection retries exhausted The original \PDOException

©️ 2024 邱敬幃 Pardn Chiu