Oihana Php System

MysqlModel extends PDOModel uses MysqlDatabaseTrait, MysqlPrivilegeTrait, MysqlTableTrait, MysqlUserTrait

MysqlModel provides high-level MySQL administrative operations using PDO.

It allows you to:

Create and drop MySQL databases and users.
Grant or revoke privileges.
Inspect privilege assignments (grants).
Validate identifiers and host syntax.

Requires a properly connected PDO instance with sufficient privileges.

Tags

example

$model = new MysqlModel();

$model->setPDO( $pdoAdmin ) ; // Connect as root or admin user

$model->createDatabase('my_app');
$model->createUser('myuser', 'localhost', 'securepass');
$model->grantPrivileges('myuser', 'localhost', 'my_app');
$model->flushPrivileges();

// Rename the user
$model->renameUser('myuser', 'localhost', 'user', 'localhost');

// Revoke the privilege of the database.
$model->revokePrivileges('user', 'localhost', 'myapp');

// Export the database informations.
print_r( $model->toArray() ) ;

if (!$model->databaseExists('myapp'))
{
   $model->createDatabase('myapp');
}

if ( !$model->userExists('admin', 'localhost') )
{
     $model->createUser('admin', 'localhost', 'strongpass');
}

author

Marc Alcaraz (ekameleon)

since

1.0.0

Constants

DEBUG = 'debug': The 'debug' parameter constant.
DEFER_ASSIGNMENT = 'deferAssignment': The 'deferAssignment' parameter constant.
MOCK = 'mock': The 'mock' parameter constant.
PDO = 'pdo': The 'pdo' parameter constant.
SCHEMA = 'schema': The 'schema' parameter constant.

Properties

$container : Container: The DI container reference.
$debug : bool: Indicates if use the debug mode.
$deferAssignment : bool|null: Indicates if the the constructor is called before setting properties.
$mock : bool: The mock flag to test the model.
$pdo : PDO|null: The PDO reference.
$schema : string|mixed|null: The internal schema to use in the PDO fetch processes.

Methods

__construct() : mixed: Creates a new PDOModel instance.
__toString() : string: Returns a String representation of the object.
bindValues() : void: Bind named parameters to a prepared PDO statement.
createDatabase() : bool: Creates a new MySQL database with given charset and collation.
createUser() : bool: Creates a new MySQL user with the given username, host, and password.
databaseExists() : bool: Checks if a MySQL database exists.
dropDatabase() : bool: Drops a database if it exists.
dropTable() : bool: Drops a table in the current database.
dropUser() : bool: Drops a MySQL user if it exists.
fetch() : mixed
fetchAll() : array<string|int, mixed>
fetchColumn() : mixed
fetchColumnArray() : array<int, string>: Fetch a list of single-column results.
flushPrivileges() : bool: Flushes MySQL privileges.
getDatabaseCharset() : array{Charset: string, Collation: string}|null: Returns the default character set and collation of a database.
getDatabaseSize() : int: Returns the size of a database in bytes.
getGrants() : array<int, string>: Retrieves all GRANT statements for a given user.
getPrivilegesSummary() : string: Returns a human-readable summary of privileges granted to a user.
getTableSize() : int: Returns the size of a table in bytes.
grantAllPrivileges() : bool: Grants all privileges on a database to a user.
grantPrivilege() : bool: Grants specific privileges on a database or table to a user.
hasAllPrivileges() : bool: Checks whether the given user has ALL PRIVILEGES on *.* (global privileges).
hasAllPrivilegesOnDatabase() : bool: Checks if the given user has ALL PRIVILEGES on a specific database.
hasAnyPrivilege() : bool: Checks whether the user has at least one privilege on a given database or table.
hasPrivilege() : bool: Checks whether a user has a specific privilege on a database or table.
initializeDefaultFetchMode() : void: Set the default fetch mode on the statement.
initializeMock() : bool: Initialize the mock flag.
initPDO() : PDO|null: Initialize the PDO instance from a config array or dependency injection container.
isConnected() : bool: Indicates if the PDO is connected.
isMock() : bool: Indicates if the document use the mock mode.
listCurrentTables() : array<int, string>: Lists all tables in the current database.
listDatabases() : array<int, string>: Lists all available databases.
listDatabasesWithPrivileges() : array<int, string>: Lists all databases on which a user has at least one privilege.
listPrivileges() : array<string, array<string|int, string>>: Lists parsed privileges granted to a user.
listUsers() : array<string|int, mixed>: Returns a list of MySQL users with their associated hosts.
optimizeDatabase() : bool: Optimizes all tables in a database.
optimizeTable() : bool: Optimizes a table.
renameTable() : bool: Renames a table in the current database.
renameUser() : bool: Renames an existing MySQL user.
repairDatabase() : bool: Repairs all tables in a database.
repairTable() : bool: Repairs a table.
revokeAllPrivileges() : bool: Revokes all privileges from a user at the global level (*.*).
revokePrivilege() : bool: Revokes specific privileges from a user on a database or table.
revokePrivileges() : bool: Revokes all privileges from a user on a specific database.
tableExists() : bool: Checks if a table exists in the current database.
toArray() : array<string, mixed>: Dumps current users and databases into a structured array.
userExists() : bool: Checks if a MySQL user exists.
getRecommendedCollation() : string: Returns the recommended collation for the given charset and server version.

DEBUG

The 'debug' parameter constant.


    public
        mixed
    DEBUG
    = 'debug'

DEFER_ASSIGNMENT

The 'deferAssignment' parameter constant.


    public
        mixed
    DEFER_ASSIGNMENT
    = 'deferAssignment'

MOCK

The 'mock' parameter constant.


    public
        mixed
    MOCK
    = 'mock'

PDO

The 'pdo' parameter constant.


    public
        mixed
    PDO
    = 'pdo'

SCHEMA

The 'schema' parameter constant.


    public
        mixed
    SCHEMA
    = 'schema'

$container

The DI container reference.


        public
            Container
    $container

The dependency injection container instance.

$debug

Indicates if use the debug mode.


        public
            bool
    $debug
     = false

$deferAssignment

Indicates if the the constructor is called before setting properties.


        public
            bool|null
    $deferAssignment
     = false

Only if the schema property is defined.

$mock

The mock flag to test the model.


        public
            bool
    $mock
     = false

$pdo

The PDO reference.


        public
            PDO|null
    $pdo
     = null

$schema

The internal schema to use in the PDO fetch processes.


        public
            string|mixed|null
    $schema
     = null

__construct()

Creates a new PDOModel instance.


    public
                    __construct(Container $container[, PDO|string|null} $init = [] ]) : mixed

Sets internal properties from the provided configuration array and initializes logger, mock, and PDO.

Parameters

$container : Container

The DI container to retrieve services like PDO and logger.

$init : PDO|string|null} = []

Optional initialization array with keys:

Param::ALTERS: array of alterations to apply
Param::BINDS: array of binds for queries
Param::DEFER_ASSIGNMENT: bool whether to defer property assignment on fetch
Param::SCHEMA: string class name of schema for fetch mode
Param::PDO: PDO instance or service name in container

__toString()

Returns a String representation of the object.


    public
                    __toString() : string

Return values

string —

A string representation of the object.

bindValues()

Bind named parameters to a prepared PDO statement.


    public
                    bindValues(PDOStatement $statement[, array<string|int, mixed> $bindVars = [] ]) : void

Parameters

$statement : PDOStatement

The PDO statement.

$bindVars : array<string|int, mixed> = []

Associative array of bindings. Supports:

['id' => 5]
['id' => [5, PDO::PARAM_INT]]

createDatabase()

Creates a new MySQL database with given charset and collation.


    public
                    createDatabase(string $name[, string|null $charset = null ][, string|null $collation = null ]) : bool

Parameters

$name : string: The name of the database.
$charset : string|null = null: The character set to use (default: 'utf8mb4').
$collation : string|null = null: The collation to use (auto-selected if null).

Return values

bool —

True on success, false otherwise.

createUser()

Creates a new MySQL user with the given username, host, and password.


    public
                    createUser(string $username[, string $host = 'localhost' ][, string $password = '' ]) : bool

If the user already exists, the operation has no effect.

Parameters

$username : string: The username to create.
$host : string = 'localhost': The host from which the user connects (default: 'localhost').
$password : string = '': The password for the user.

Return values

bool —

True on success, false otherwise.

databaseExists()

Checks if a MySQL database exists.


    public
                    databaseExists(string $name) : bool

Parameters

$name : string: Database name.

Return values

bool —

True if the database exists.

dropDatabase()

Drops a database if it exists.


    public
                    dropDatabase(string $name) : bool

Parameters

$name : string: The name of the database to drop.

Return values

bool —

True on success, false otherwise.

dropTable()

Drops a table in the current database.


    public
                    dropTable(string $table) : bool

Parameters

$table : string: Table name.

Return values

bool —

True on success.

dropUser()

Drops a MySQL user if it exists.


    public
                    dropUser(string $username[, string $host = 'localhost' ]) : bool

Parameters

$username : string: The username to drop.
$host : string = 'localhost': The host (default: 'localhost').

Return values

bool —

True on success, false otherwise.

fetch()


    public
                    fetch(string $query[, array<string|int, mixed> $bindVars =  = '[]' ]) : mixed

Fetch a single record from the database.

Parameters

$query : string
$bindVars : array<string|int, mixed> = = '[]'

fetchAll()


    public
                    fetchAll(string $query[, array<string|int, mixed> $bindVars =  = '[]' ]) : array<string|int, mixed>

Fetch all matching records from the database.

Parameters

$query : string
$bindVars : array<string|int, mixed> = = '[]'

Return values

array<string|int, mixed>

fetchColumn()


    public
                    fetchColumn(string $query[, array<string|int, mixed> $bindVars =  = '[]' ][, int $column =  = '0' ]) : mixed

Fetch a single column from the first row.

Parameters

$query : string
$bindVars : array<string|int, mixed> = = '[]'
$column : int = = '0'

fetchColumnArray()

Fetch a list of single-column results.


    public
                    fetchColumnArray(string $query[, array<string|int, mixed> $bindVars = [] ]) : array<int, string>

Parameters

$query : string: The SQL query to execute.
$bindVars : array<string|int, mixed> = []: Optional bindings for the query.

Return values

array<int, string>

flushPrivileges()

Flushes MySQL privileges.


    public
                    flushPrivileges() : bool

This reloads the grant tables to apply recent changes (like CREATE USER, GRANT, etc.).

Return values

bool —

True on success, false otherwise.

getDatabaseCharset()

Returns the default character set and collation of a database.


    public
                    getDatabaseCharset(string $dbname) : array{Charset: string, Collation: string}|null

Parameters

$dbname : string

Return values

array{Charset: string, Collation: string}|null

getDatabaseSize()

Returns the size of a database in bytes.


    public
                    getDatabaseSize(string $dbname) : int

Parameters

$dbname : string

Return values

int —

Size in bytes.

getGrants()

Retrieves all GRANT statements for a given user.


    public
                    getGrants(string $username[, string $host = 'localhost' ]) : array<int, string>

Parameters

$username : string: The MySQL username.
$host : string = 'localhost': The associated host (default: 'localhost').

Return values

array<int, string> —

Array of GRANT statements (or empty if none/failure).

getPrivilegesSummary()

Returns a human-readable summary of privileges granted to a user.


    public
                    getPrivilegesSummary(string $username[, string $host = 'localhost' ]) : string

This method parses and formats the privileges returned by SHOW GRANTS into a simple multi-line string, with each line showing the scope (database.table or ALL) and the list of privileges.

Parameters

$username : string: The MySQL username.
$host : string = 'localhost': The host associated with the user (default: 'localhost').

Tags

example

echo $model->getPrivilegesSummary('user1');
// Output:
// mydb.*: SELECT, INSERT
// mydb.products: SELECT
// ALL: USAGE

Return values

string —

A formatted summary string.

getTableSize()

Returns the size of a table in bytes.


    public
                    getTableSize(string $table) : int

Parameters

$table : string: Table name.

Return values

int —

Table size in bytes.

grantAllPrivileges()

Grants all privileges on a database to a user.


    public
                    grantAllPrivileges(string $username, string $host, string $dbname) : bool

Parameters

$username : string: The user to grant privileges to.
$host : string: The user's host (usually 'localhost').
$dbname : string: The database to grant access to.

Return values

bool —

True on success, false otherwise.

grantPrivilege()

Grants specific privileges on a database or table to a user.


    public
                    grantPrivilege(string $privileges, string $dbname, string $username[, string $host = 'localhost' ][, string|null $table = null ]) : bool

Parameters

$privileges : string: A comma-separated list of privileges (e.g. 'SELECT, INSERT').
$dbname : string: Database name.
$username : string: The MySQL user.
$host : string = 'localhost': Host (default: 'localhost').
$table : string|null = null: Optional table name. If null, grants privileges on the entire database.

Tags

example

$model->grantPrivilege('SELECT, INSERT', 'mydb', 'user1');
$model->grantPrivilege('UPDATE', 'mydb', 'user1', 'localhost', 'products');

Return values

bool —

True if the grant statement executed successfully.

hasAllPrivileges()

Checks whether the given user has ALL PRIVILEGES on *.* (global privileges).


    public
                    hasAllPrivileges(string $username[, string $host = 'localhost' ]) : bool

Parameters

$username : string: The MySQL username.
$host : string = 'localhost': The associated host (default: 'localhost').

Return values

bool —

True if the user has ALL PRIVILEGES globally.

hasAllPrivilegesOnDatabase()

Checks if the given user has ALL PRIVILEGES on a specific database.


    public
                    hasAllPrivilegesOnDatabase(string $username, string $database[, string $host = 'localhost' ]) : bool

This method parses the user's GRANT statements and looks for a "GRANT ALL PRIVILEGES ON database.* TO ..." entry.

Parameters

$username : string: The MySQL username to check.
$database : string: The database name to check privileges for.
$host : string = 'localhost': The host part of the MySQL user (default: 'localhost').

Return values

bool —

True if the user has ALL PRIVILEGES on the database, false otherwise.

hasAnyPrivilege()

Checks whether the user has at least one privilege on a given database or table.


    public
                    hasAnyPrivilege(string $username, string $dbname[, string|null $table = null ][, string $host = 'localhost' ]) : bool

Parameters

$username : string: The MySQL user.
$dbname : string: The database name.
$table : string|null = null: Optional table name. If null, checks privileges on the database.
$host : string = 'localhost': The host (default: 'localhost').

Tags

example

if ($model->hasAnyPrivilege('user1', 'mydb')) {
    echo "User has privileges on the database.";
}

Return values

bool —

True if the user has any privilege on the specified scope.

hasPrivilege()

Checks whether a user has a specific privilege on a database or table.


    public
                    hasPrivilege(string $username, string $privilege, string $dbname[, string|null $table = null ][, string $host = 'localhost' ]) : bool

Parameters

$username : string: MySQL username.
$privilege : string: Privilege to check (e.g. SELECT, INSERT, ALL PRIVILEGES).
$dbname : string: Database name.
$table : string|null = null: Optional table name. If null, check DB-level privileges.
$host : string = 'localhost': Host (default: 'localhost').

Return values

bool —

True if the privilege is granted.

initializeDefaultFetchMode()

Set the default fetch mode on the statement.


    public
                    initializeDefaultFetchMode(PDOStatement $statement) : void

Uses FETCH_ASSOC by default or FETCH_CLASS (with optional FETCH_PROPS_LATE) if a schema class is defined and exists.

Parameters

$statement : PDOStatement: The PDO statement to configure.

initializeMock()

Initialize the mock flag.


    public
                    initializeMock([array<string|int, mixed> $init = [] ]) : bool

Parameters

$init : array<string|int, mixed> = []

Return values

bool

initPDO()

Initialize the PDO instance from a config array or dependency injection container.


    public
                    initPDO([array<string|int, mixed> $init = [] ][, Container|null $container = null ]) : PDO|null

Parameters

$init : array<string|int, mixed> = []: Configuration array. Expects Param::PDO as key.
$container : Container|null = null: Optional DI container to resolve the PDO service.

Return values

PDO|null —

The resolved PDO instance or null.

isConnected()

Indicates if the PDO is connected.


    public
                    isConnected() : bool

Return values

bool

isMock()

Indicates if the document use the mock mode.


    public
                    isMock([array<string|int, mixed> $init = [] ]) : bool

Parameters

$init : array<string|int, mixed> = []

Return values

bool

listCurrentTables()

Lists all tables in the current database.


    public
                    listCurrentTables([bool $throwable = false ]) : array<int, string>

Parameters

$throwable : bool = false

Return values

array<int, string> —

Array of table names.

listDatabases()

Lists all available databases.


    public
                    listDatabases([bool $excludeSystem = true ]) : array<int, string>

Parameters

$excludeSystem : bool = true: Exclude system databases like 'information_schema', 'mysql', etc.

Return values

array<int, string> —

List of database names.

listDatabasesWithPrivileges()

Lists all databases on which a user has at least one privilege.


    public
                    listDatabasesWithPrivileges(string $username[, string $host = 'localhost' ]) : array<int, string>

This method analyzes the output of SHOW GRANTS and extracts database-level or table-level scopes from the privilege definitions. It returns a unique list of database names, including * if the user has global privileges.

Parameters

$username : string: The MySQL username.
$host : string = 'localhost': The host associated with the user (default: 'localhost').

Tags

example

$databases = $model->listDatabasesWithPrivileges('user1');
// Result: ['mydb', 'test'] or ['*'] if global privileges

Return values

array<int, string> —

List of database names (e.g. ['mydb', 'test', '*']).

listPrivileges()

Lists parsed privileges granted to a user.


    public
                    listPrivileges(string $username[, string $host = 'localhost' ]) : array<string, array<string|int, string>>

Parameters

$username : string: The MySQL user.
$host : string = 'localhost': The associated host (default: 'localhost').

Tags

example

$list = $model->listPrivileges('user1');

foreach ( $list as $scope => $rights)
{
    echo "Privileges on {$scope}:\n - " . implode(', ', $rights) . PHP_EOL ;
}

// Privileges on mydb.*:
// - SELECT, INSERT, UPDATE

// Privileges on mydb.products:
// - SELECT

// Privileges on ALL:
// - USAGE

Return values

array<string, array<string|int, string>> —

[ 'database.table' => [privileges...] ]

listUsers()

Returns a list of MySQL users with their associated hosts.


    public
                    listUsers([string|null $like = null ][, bool $grouped = false ][, bool $throwable = false ]) : array<string|int, mixed>

Parameters

$like : string|null = null: Optional SQL pattern to filter users (e.g. 'wp%').
$grouped : bool = false: Whether to group hosts under each username.
$throwable : bool = false: Indicates if the method is throwable.

Return values

array<string|int, mixed> —

If grouped, returns array<string, string[]> (user => [hosts]). Otherwise, returns array<int, array{user: string, host: string}>.

optimizeDatabase()

Optimizes all tables in a database.


    public
                    optimizeDatabase(string $dbname) : bool

Parameters

$dbname : string

Return values

bool —

True if all tables optimized successfully.

optimizeTable()

Optimizes a table.


    public
                    optimizeTable(string $table) : bool

Parameters

$table : string: Table name.

Return values

bool —

True on success.

renameTable()

Renames a table in the current database.


    public
                    renameTable(string $from, string $to) : bool

Parameters

$from : string: Current table name.
$to : string: New table name.

Return values

bool —

True if renamed successfully.

renameUser()

Renames an existing MySQL user.


    public
                    renameUser(string $fromUser, string $fromHost, string $toUser, string $toHost) : bool

Parameters

$fromUser : string: Current username.
$fromHost : string: Current host.
$toUser : string: New username.
$toHost : string: New host.

Return values

bool —

True if the rename operation was successful, false otherwise.

repairDatabase()

Repairs all tables in a database.


    public
                    repairDatabase(string $dbname) : bool

Parameters

$dbname : string

Return values

bool —

True if all tables repaired successfully.

repairTable()

Repairs a table.


    public
                    repairTable(string $table) : bool

Parameters

$table : string: Table name.

Return values

bool —

True on success.

revokeAllPrivileges()

Revokes all privileges from a user at the global level (*.*).


    public
                    revokeAllPrivileges(string $username[, string $host = 'localhost' ]) : bool

Parameters

$username : string: The MySQL username.
$host : string = 'localhost': The user's host (default: 'localhost').

Tags

example

$model->revokeAllPrivileges('user1');

Return values

bool —

True on success, false otherwise.

revokePrivilege()

Revokes specific privileges from a user on a database or table.


    public
                    revokePrivilege(string $privileges, string $dbname, string $username[, string $host = 'localhost' ][, string|null $table = null ]) : bool

Parameters

$privileges : string: Comma-separated privileges (e.g. 'SELECT, INSERT').
$dbname : string: Database name.
$username : string: MySQL user.
$host : string = 'localhost': Host (default: 'localhost').
$table : string|null = null: Optional table name (null = whole DB).

Tags

example

Remove all modification rights:

$model->revokePrivilege('INSERT, UPDATE, DELETE', 'mydb', 'user1');

Only remove SELECT from a specific table

$model->revokePrivilege('SELECT', 'mydb', 'user1', 'localhost', 'products');

Return values

bool —

True if the statement was executed successfully.

revokePrivileges()

Revokes all privileges from a user on a specific database.


    public
                    revokePrivileges(string $username, string $host, string $dbname) : bool

Parameters

$username : string: The user to revoke privileges from.
$host : string: The user's host (usually 'localhost').
$dbname : string: The database to revoke access from.

Return values

bool —

True on success, false otherwise.

tableExists()

Checks if a table exists in the current database.


    public
                    tableExists(string $table) : bool

Parameters

$table : string: Table name.

Return values

bool —

True if the table exists.

toArray()

Dumps current users and databases into a structured array.


    public
                    toArray() : array<string, mixed>

Return values

array<string, mixed>

userExists()

Checks if a MySQL user exists.


    public
                    userExists(string $username[, string $host = 'localhost' ]) : bool

Parameters

$username : string: Username to check.
$host : string = 'localhost': Host (default: 'localhost').

Return values

bool —

True if the user exists.

getRecommendedCollation()

Returns the recommended collation for the given charset and server version.


    protected
                    getRecommendedCollation(string $charset) : string

Parameters

$charset : string

Return values

string

MysqlModel extends PDOModel uses MysqlDatabaseTrait, MysqlPrivilegeTrait, MysqlTableTrait, MysqlUserTrait

Tags

Table of Contents

Constants

Properties

Methods

Constants

DEBUG

DEFER_ASSIGNMENT

MOCK

PDO

SCHEMA

Properties

$container

$debug

$deferAssignment

$mock

$pdo

$schema

Methods

__construct()

Parameters

Tags

__toString()

Tags

Return values

bindValues()

Parameters

createDatabase()

Parameters

Return values

createUser()

Parameters

Return values

databaseExists()

Parameters

Return values

dropDatabase()

Parameters

Return values

dropTable()

Parameters

Return values

dropUser()

Parameters

Return values

fetch()

Parameters

fetchAll()

Parameters

Return values

fetchColumn()

Parameters

fetchColumnArray()

Parameters

Return values

flushPrivileges()

Return values

getDatabaseCharset()

Parameters

Tags

Return values

getDatabaseSize()

Parameters

Return values

getGrants()

Parameters

Return values

getPrivilegesSummary()

Parameters

Tags

Return values

getTableSize()

Parameters

Return values

grantAllPrivileges()

Parameters

Return values

grantPrivilege()

Parameters