Oihana Php Mysql

MysqlModel extends PDOModel implements ToAssociativeArray 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

Interfaces

ToAssociativeArray

Methods

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.
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.
getUserInfo() : array<string|int, mixed>|null: Retrieves complete information about a MySQL user.
grantAllPrivileges() : bool: Grants all privileges on a database to a user.
grantPrivilege() : bool: Grants specific privileges on a database or table to a user.
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.
hasGlobalAllPrivileges() : bool: Checks whether the given user has ALL PRIVILEGES globally (i.e., ON *.*).
hasPrivilege() : bool: Checks whether a user has a specific privilege on a database or table.
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|int, mixed>: Returns the array representation of the instance.
userExists() : bool: Checks if a MySQL user exists.
getRecommendedCollation() : string: Returns the recommended collation for the given charset and server version.

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.

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.

getUserInfo()

Retrieves complete information about a MySQL user.


    public
                    getUserInfo(string $username[, string $host = 'localhost' ][, bool $throwable = false ]) : array<string|int, mixed>|null

Parameters

$username : string: The username to get information for.
$host : string = 'localhost': The host (default: 'localhost').
$throwable : bool = false: Indicates if the method should throw exceptions.

Return values

array<string|int, mixed>|null —

Returns user information array or null if user doesn't exist. Array contains: user, host, password_expired, account_locked, password_last_changed, password_lifetime, max_connections, max_questions, max_updates, max_user_connections, plugin, authentication_string, ssl_type, ssl_cipher, x509_issuer, x509_subject, and grants.

grantAllPrivileges()

Grants all privileges on a database to a user.


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

Parameters

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

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.

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.

hasGlobalAllPrivileges()

Checks whether the given user has ALL PRIVILEGES globally (i.e., ON *.*).


    public
                    hasGlobalAllPrivileges(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.

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.

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()

Returns the array representation of the instance.


    public
                    toArray([array<string|int, mixed> $options = [] ]) : array<string|int, mixed>

Parameters

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

Return values

array<string|int, 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 implements ToAssociativeArray uses MysqlDatabaseTrait, MysqlPrivilegeTrait, MysqlTableTrait, MysqlUserTrait

Tags

Table of Contents

Interfaces

Methods

Methods

createDatabase()

Parameters

Return values

createUser()

Parameters

Return values

databaseExists()

Parameters

Return values

dropDatabase()

Parameters

Return values

dropTable()

Parameters

Return values

dropUser()

Parameters

Return values

flushPrivileges()

Return values

getDatabaseCharset()

Parameters

Tags

Return values

getDatabaseSize()

Parameters

Tags

Return values

getGrants()

Parameters

Return values

getPrivilegesSummary()

Parameters

Tags

Return values

getTableSize()

Parameters

Tags

Return values

getUserInfo()

Parameters

Return values

grantAllPrivileges()

Parameters

Return values

grantPrivilege()

Parameters

Tags

Return values

hasAllPrivilegesOnDatabase()

Parameters

Return values

hasAnyPrivilege()

Parameters

Tags

Return values

hasGlobalAllPrivileges()

Parameters

Return values

hasPrivilege()

Parameters

Return values

listCurrentTables()

Parameters

Return values

listDatabases()

Parameters

Tags

Return values

listDatabasesWithPrivileges()

Parameters

Tags

Return values

listPrivileges()