PHP code example of hiblaphp / mysql

1. Go to this page and download the library: Download hiblaphp/mysql library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

hiblaphp / mysql example snippets


use Hibla\Mysql\MysqlClient;
use function Hibla\await;

// The client is lazy by default and no connections are opened until the first query.
$client = new MysqlClient('mysql://test_user:[email protected]/test');

// Simple query — short, stays on one line
$users = await($client->query('SELECT * FROM users WHERE active = ?', [true]));
echo $users->rowCount;

// Named parameters — args are long, so wrap the call
$user = await(
    $client->query(
        'SELECT * FROM users WHERE email = :email AND status = :status',
        ['email' => '[email protected]', 'status' => 'active']
    )
);

// Prepared statement (recommended for repeated execution)
$stmt = await($client->prepare('SELECT * FROM users WHERE email = :email'));
$result = await($stmt->execute(['email' => '[email protected]']));
$stmt->close();

// Streaming large result sets
$stream = await($client->stream('SELECT * FROM logs ORDER BY id DESC'));
foreach ($stream as $row) {
    processLog($row);
}


use function Hibla\await;
use Hibla\Promise\Promise;

// Three queries run concurrently. Connections are borrowed from the pool
// (and created on demand) only as each query starts.
[$users, $orders, $stats] = await(Promise::all([
    $client->query('SELECT * FROM users'),
    $client->query('SELECT * FROM orders'),
    $client->query('SELECT COUNT(*) FROM stats'),
]));


use Hibla\Sql\SqlClientInterface;
use Hibla\Sql\Transaction;

// Your service depends on the contract, not the MySQL-specific implementation.
class UserRepository
{
    public function __construct(private readonly SqlClientInterface $db) {}
}


use Hibla\Mysql\ValueObjects\MysqlConfig;

$config = new MysqlConfig(
    host: '127.0.0.1',
    port: 3306,
    username: 'app_user',
    password: 'secret',
    database: 'production',
    charset: 'utf8mb4',
    ssl: true,
    sslCa: '/etc/ssl/certs/ca-bundle.crt',
    sslVerify: true,
    enableServerSideCancellation: true,
    resetConnection: true,
    castPreparedTypes: true,
);

$client = new MysqlClient($config, maxConnections: 20);


$config = new MysqlConfig(
    host: '127.0.0.1',
    username: 'app_user',
    password: 'secret',
    database: 'mydb',
);


$config = MysqlConfig::fromArray([
    'host'                            => '127.0.0.1',
    'port'                            => 3306,
    'username'                        => 'app_user',
    'password'                        => 'secret',
    'database'                        => 'mydb',
    'charset'                         => 'utf8mb4',
    'connect_timeout'                 => 10,
    'ssl'                             => true,
    'ssl_ca'                          => '/path/to/ca.pem',
    'ssl_cert'                        => '/path/to/client-cert.pem',
    'ssl_key'                         => '/path/to/client-key.pem',
    'ssl_verify'                      => true,
    'compress'                        => false,
    'reset_connection'                => true,
    'multi_statements'                => false,
    'enable_server_side_cancellation' => false,
    'kill_timeout_seconds'            => 3.0,
    'cast_prepared_types'             => true,
]);


$config = MysqlConfig::fromUri(
    'mysql://app_user:[email protected]:3306/mydb'
    . '?charset=utf8mb4'
    . '&ssl=true'
    . '&ssl_verify=true'
    . '&ssl_ca=/path/to/ca.pem'
    . '&reset_connection=true'
    . '&enable_server_side_cancellation=true'
    . '&kill_timeout_seconds=5'
    . '&cast_prepared_types=true'
);


$base = MysqlConfig::fromArray([
    'host'     => '127.0.0.1',
    'username' => 'app_user',
    'password' => 'secret',
    'database' => 'mydb',
]);

// Regular client — cancellation off (default)
$readClient = new MysqlClient($base);

// Long-running report client — cancellation on so queries can be interrupted
$reportClient = new MysqlClient(
    $base->withQueryCancellation(true),
    maxConnections: 2,
);


$config = new MysqlConfig(host: '127.0.0.1', username: 'app', password: 'secret', database: 'mydb');
echo $config->toSafeUri();
// mysql://app:***@127.0.0.1/mydb


$base = new MysqlConfig(
    host: 'db.internal',
    username: 'app',
    password: 'secret',
    charset: 'utf8mb4',
    ssl: true,
    sslVerify: true,
    sslCa: '/etc/ssl/ca.pem',
);

// One client per database, sharing all connection-level settings
$userDb   = new MysqlClient(MysqlConfig::fromArray([...(array) $base, 'database' => 'users']), maxConnections: 10);
$reportDb = new MysqlClient($base->withQueryCancellation(true), maxConnections: 2);


use Hibla\Mysql\MysqlClient;

// From DSN string — lazy, no connections opened yet
$client = new MysqlClient('mysql://user:pass@localhost:3306/mydb');

// From array
$client = new MysqlClient([
    'host'     => '127.0.0.1',
    'port'     => 3306,
    'username' => 'test_user',
    'password' => 'test_password',
    'database' => 'test',
    'charset'  => 'utf8mb4',
]);

// With explicit pool settings
$client = new MysqlClient(
    config: 'mysql://...',
    minConnections: 0,
    maxConnections: 20,
    idleTimeout: 300,
    maxLifetime: 3600,
    statementCacheSize: 512,
    enableStatementCache: true,
    maxWaiters: 100,
    acquireTimeout: 10.0,
    enableServerSideCancellation: true,
    resetConnection: true,
    multiStatements: false,
    onConnect: function (ConnectionSetup $setup) {
        await($setup->execute("SET SESSION time_zone = '+00:00'"));
    },
);


$result = await($client->query('SELECT * FROM users LIMIT 10'));


$result = await(
    $client->query(
        'SELECT id, name, email FROM users WHERE created_at > ? AND status = ?',
        [$since, 'active']
    )
);


// Named params in query()
$result = await(
    $client->query(
        'SELECT * FROM users WHERE status = :status AND created_at > :since',
        ['status' => 'active', 'since' => $since]
    )
);

// Order of keys does not matter
$result = await(
    $client->query(
        'INSERT INTO orders (user_id, total, status) VALUES (:userId, :total, :status)',
        ['status' => 'pending', 'total' => 99.99, 'userId' => 42]
    )
);

// Named params via prepare() — most useful when executing the same statement repeatedly
$stmt = await(
    $client->prepare(
        'SELECT * FROM products WHERE category_id = :categoryId AND price > :minPrice'
    )
);

$electronics = await($stmt->execute(['categoryId' => 1, 'minPrice' => 50.00]));
$clothing    = await($stmt->execute(['categoryId' => 2, 'minPrice' => 25.00]));

$stmt->close();


// Returns affected row count
$count = await(
    $client->execute(
        'UPDATE users SET last_login = NOW() WHERE id = :id',
        ['id' => $userId]
    )
);

// Returns last insert ID
$lastId = await(
    $client->executeGetId(
        'INSERT INTO users (name, email) VALUES (:name, :email)',
        ['name' => 'Alice', 'email' => '[email protected]']
    )
);

// Returns first row as associative array, or null
$user = await($client->fetchOne('SELECT * FROM users WHERE id = :id', ['id' => $userId]));

// Returns value of first column (or named column) from first row
$name = await($client->fetchValue('SELECT name FROM users WHERE id = :id', ['id' => $userId]));


// Positional placeholders
$stmt = await($client->prepare('SELECT * FROM products WHERE category_id = ? AND price > ?'));

$result1 = await($stmt->execute([1, 50.00]));
$result2 = await($stmt->execute([2, 100.00]));
$stmt->close();

// Named placeholders — order of keys in execute() does not matter
$stmt = await(
    $client->prepare(
        'SELECT * FROM products WHERE category_id = :categoryId AND price > :minPrice'
    )
);

$result1 = await($stmt->execute(['categoryId' => 1, 'minPrice' => 50.00]));
$result2 = await($stmt->execute(['minPrice' => 100.00, 'categoryId' => 2]));
$stmt->close();


$stream = await($client->stream('SELECT * FROM large_table ORDER BY id', bufferSize: 200));

// Inspect stream metadata before iterating
echo $stream->columnCount;
print_r($stream->columns);

foreach ($stream as $row) {
    processRow($row);
}


$stmt = await(
    $client->prepare(
        'SELECT * FROM logs WHERE created_at > :since AND level = :level'
    )
);

$stream = await($stmt->executeStream(['since' => $since, 'level' => 'error']));

echo $stream->columnCount;
print_r($stream->columns);

foreach ($stream as $row) {
    processRow($row);
}


> await(async(function () use ($client) {
>     $stream = await($client->stream($sql));
>     foreach ($stream as $row) { ... }
> }));
>


$result = await(
    $client->transaction(function (TransactionInterface $tx) use ($from, $to) {
        await($tx->execute(
            'UPDATE accounts SET balance = balance - :amount WHERE id = :id',
            ['amount' => 100, 'id' => $from]
        ));
        await($tx->execute(
            'UPDATE accounts SET balance = balance + :amount WHERE id = :id',
            ['amount' => 100, 'id' => $to]
        ));

        return 'Transfer completed';
    })
);


await(
    $client->transaction(
        function (TransactionInterface $tx) use ($from, $to) {
            await($tx->execute(
                'UPDATE accounts SET balance = balance - :amount WHERE id = :id',
                ['amount' => 100, 'id' => $from]
            ));
            await($tx->execute(
                'UPDATE accounts SET balance = balance + :amount WHERE id = :id',
                ['amount' => 100, 'id' => $to]
            ));
        },
        TransactionOptions::default()
            ->withAttempts(3)
            ->withIsolationLevel(IsolationLevel::REPEATABLE_READ)
    )
);


use Hibla\Sql\TransactionOptions;
use Hibla\Sql\IsolationLevel;

$options = TransactionOptions::default()
    ->withAttempts(5)
    ->withIsolationLevel(IsolationLevel::SERIALIZABLE)
    ->withRetryableExceptions([MyOptimisticLockException::class]);


    class MyOptimisticLockException extends \RuntimeException
        implements \Hibla\Sql\Exceptions\RetryableException {}


    // Retry by class list
    $options = TransactionOptions::default()
        ->withAttempts(3)
        ->withRetryableExceptions([ThirdPartyConflictException::class]);

    // Retry by predicate
    $options = TransactionOptions::default()
        ->withAttempts(3)
        ->withRetryableExceptions(
            fn(\Throwable $e) => $e instanceof ThirdPartyConflictException && $e->getCode() === 409
        );


$tx = await($client->beginTransaction());
try {
    await($tx->execute(
        'UPDATE accounts SET balance = balance - :amount WHERE id = :id',
        ['amount' => 100, 'id' => $from]
    ));
    await($tx->execute(
        'UPDATE accounts SET balance = balance + :amount WHERE id = :id',
        ['amount' => 100, 'id' => $to]
    ));
    await($tx->commit());
} catch (\Throwable $e) {
    await($tx->rollback());
    throw $e;
}


$tx = await($client->beginTransaction());

try {
    await($tx->savepoint('before_risky'));

    try {
        await($tx->execute(
            'INSERT INTO external_refs (id) VALUES (:id)',
            ['id' => $externalId]
        ));
    } catch (\Throwable $e) {
        // Rolling back to the savepoint also clears the tainted state,
        // so queries after this point are allowed to continue.
        await($tx->rollbackTo('before_risky'));
    }

    await($tx->releaseSavepoint('before_risky'));
    await($tx->commit());
} catch (\Throwable $e) {
    await($tx->rollback());
    throw $e;
}


$promise = $client->transaction(function (TransactionInterface $tx) {
    await($tx->execute('UPDATE ...'));
    await($tx->execute('UPDATE ...')); // still running when cancelled
});

Loop::addTimer(2.0, fn() => $promise->cancel());
// → running query is interrupted, ROLLBACK is issued, connection returned to pool


await(
    $client->transaction(function (TransactionInterface $tx) use ($externalId) {
        await($tx->execute(
            'INSERT INTO audit_log (event) VALUES (:event)',
            ['event' => 'attempt']
        ));

        await($tx->savepoint('before_risky_op'));

        try {
            await($tx->execute(
                'INSERT INTO external_refs (id) VALUES (:id)',
                ['id' => $externalId]
            ));
        } catch (\Throwable $e) {
            // Rolls back to the savepoint and clears the tainted state.
            await($tx->rollbackTo('before_risky_op'));
        }

        await($tx->releaseSavepoint('before_risky_op'));
    })
);


await(
    $client->transaction(function (TransactionInterface $tx) use ($user) {
        await($tx->execute(
            'INSERT INTO users (name, email) VALUES (:name, :email)',
            ['name' => $user->name, 'email' => $user->email]
        ));

        // Fires only if the COMMIT succeeds
        $tx->onCommit(function () use ($user) {
            EventDispatcher::dispatch(new UserCreated($user));
        });

        // Fires if the transaction rolls back (e.g., constraint violation, cancellation)
        $tx->onRollback(function () use ($user) {
            Logger::warning("Failed to persist user: {$user->email}");
        });
    })
);


$result = await($client->query('CALL get_user_with_orders(?)', [$userId]));

foreach ($result as $row) {
    echo $row['name'];
}

$orders = $result->nextResult();
if ($orders !== null) {
    foreach ($orders as $order) {
        echo $order['total'];
    }
}


$client = new MysqlClient([
    'host'             => 'localhost',
    'username'         => 'root',
    'password'         => '',
    'database'         => 'app',
    'multi_statements' => true,
]);

$result = await(
    $client->query('SELECT * FROM users; SELECT * FROM orders; SELECT COUNT(*) FROM stats')
);

foreach ($result as $row) { ... }                // users
foreach ($result->nextResult() as $row) { ... }  // orders
$count = $result->nextResult()->nextResult()->fetchOne();


$client = new MysqlClient(
    config: $config,
    minConnections: 0,
    maxConnections: 50,
    idleTimeout: 600,
    maxLifetime: 3600,
    acquireTimeout: 10.0,
    resetConnection: true,
);


// Graceful — stops new work, waits for active queries to finish, then closes
await($client->closeAsync(timeout: 30.0));

// Force — closes everything immediately, rejects pending waiters
$client->close();


$result = await($client->healthCheck());
// e.g. ['checked' => 5, 'failed' => 1, 'evicted' => 1]


$stats = $client->stats;
// e.g. ['total' => 8, 'idle' => 5, 'active' => 3, 'waiting' => 0]


// Require SSL with full server certificate verification
$client = new MysqlClient([
    'host'       => 'db.example.com',
    'username'   => 'app',
    'password'   => 'secret',
    'database'   => 'production',
    'ssl'        => true,
    'ssl_ca'     => '/etc/ssl/certs/ca-bundle.crt',
    'ssl_verify' => true,
]);

// Mutual TLS — client certificate and key
$client = new MysqlClient([
    'host'       => 'db.example.com',
    'username'   => 'app',
    'password'   => 'secret',
    'database'   => 'production',
    'ssl'        => true,
    'ssl_ca'     => '/path/to/ca.pem',
    'ssl_cert'   => '/path/to/client-cert.pem',
    'ssl_key'    => '/path/to/client-key.pem',
    'ssl_verify' => true,
]);


$client = new MysqlClient([
    'host'     => 'db.example.com',
    'username' => 'app',
    'password' => 'secret',
    'database' => 'production',
    'compress' => true,
]);


$client = new MysqlClient(
    config: $config,
    enableServerSideCancellation: true,
);

$promise = $client->query('SELECT * FROM huge_table');
Loop::addTimer(5.0, fn() => $promise->cancel()); // KILL QUERY dispatched


$client = new MysqlClient(
    config: $config,
    onConnect: function (ConnectionSetup $setup) {
        await($setup->execute("SET SESSION time_zone = '+00:00'"));
        await($setup->execute("SET SESSION sql_mode = 'STRICT_TRANS_TABLES'"));
    }
);


$client = new MysqlClient(
    config: $config,
    enableStatementCache: true,
    statementCacheSize: 512
);

$client->clearStatementCache(); // Invalidate all caches (e.g. after schema changes)


$result = await($client->query('SELECT * FROM users'));

echo $result->rowCount;      // int — rows in result set
echo $result->affectedRows;  // int — rows affected by INSERT/UPDATE/DELETE
echo $result->lastInsertId;  // int — last auto-increment ID
echo $result->warningCount;  // int — MySQL warnings generated
echo $result->connectionId;  // int — server thread ID
echo $result->columnCount;   // int — number of columns

foreach ($result->fields as $col) {
    echo $col->name . ': ' . $col->typeName; // e.g. "price: DECIMAL"
}

foreach ($result as $row) {
    echo $row['name'];
}

$row = $result->fetchOne();
$all = $result->fetchAll();
$col = $result->fetchColumn('name');


// Default — native PHP types from the binary protocol
$client = new MysqlClient($config);
$row = await($client->fetchOne('SELECT id, price FROM products WHERE id = ?', [1]));
// $row['id']    => int(1)
// $row['price'] => string("19.99")   — DECIMAL always comes back as string

// Cast disabled — everything is a string
$client = new MysqlClient($config, castPreparedTypes: false);
$row = await($client->fetchOne('SELECT id, price FROM products WHERE id = ?', [1]));
// $row['id']    => string("1")
// $row['price'] => string("19.99")


$result = await(
    $client->query('SELECT price FROM products WHERE id = :id', ['id' => 1])
);

$row = $result->fetchOne();

// Correct — preserve the exact string value from the server
$price = $row['price']; // "19.99" (string)

// Also correct — use bcmath for arithmetic on decimals
$tax   = bcmul($row['price'], '0.20', 2); // "4.00"
$total = bcadd($row['price'], $tax, 2);   // "23.99"

// WRONG — loses precision for large or high-decimal-place values
$price = (float) $row['price']; // may not round-trip exactly


$result = await($client->query('CALL get_user_with_orders(?)', [$userId]));

foreach ($result as $row) { ... }   // first result set

$next = $result->nextResult();
if ($next !== null) {
    foreach ($next as $row) { ... } // second result set
}