usernane / phmysql

PHP library for building MySQL database schema and quires.

phMysql

Object oriented PHP library for building MySQL database schema and quires.

API Docs

This library is a part of WebFiori Framework. To access API docs of the library, you can visid the following link: https://webfiori.com/docs/phMysql .

Features

• Ability to create MySQL database table structure using PHP.
• Creating MySQL queries in a simple manner.
• Connect to MySQL database engine (require mysqli extension).
• Execute MySQL queries and map the result of a query to an object (Like an ORM).
• Validating the data before sending it to the DBMS.

Supported PHP Versions

The library support all versions starting from version 5.6 up to version 7.4.

Installation

The easy option is to download the latest release manually from Release.

The Idea

The overall idea of the library is as follows, every table in the database is represented as an instance of the class 'MySQLTable'. The instance is associated with an instance of the class 'MySQLQuery'. The main aim of the class 'MySQLQuery' is to construct different types of queries which can be executed and get data from the table.

The class 'MySQLink' is used to connect to MySQL database and execute any instance of the class 'MySQLQuery'. In addition to that, it is used to access the data which can be the result of executing a 'select' query.

Creating Database Tables

The first step in using the library is to create your database tables. As we have said before, every table is represented as an instance of the class MySQLTable. Also, we have said that an instance of this class is linked to the class MySQLQuery.

Let's assume that we want to create a database table with following structure:

• Table name: users_information
• Table columns:
• Column name: user_id. Type: int. Size: 4. Primary key.
• Column name: username. Type: varchar. Size 25.
• Column name: password. Type: varchar. Size: 64.
• Column name: created_on. Type: timestamp Default to current_timestamp.
• Column name: last_updated. Type: datetime. Can be null.

Such table can be created as follows:

$query = new MySQLQuery('users_information');
$query->getTable()->addColumns([
   'user-id'=>[
       'datatype'=>'int',
       'size'=>4,
       'is-primary'=>true
   ],
   'username'=>[
       'datatype'=>'varchar',
       'size'=>25
   ],
   'password'=>[
       'datatype'=>'varchar',
       'size'=>64
   ],
   'created-on'=>[
       'datatype'=>'timestamp',
       'default'=>'current_timestamp'
   ],
   'last-updated'=>[
       'datatype'=>'datetime',
       'is-null'=>true
   ]
]);

This will build the basic structure of the table. To get SQL query which can be used to create the table, we simply do as follows:

$query->createTable();
// display the constructed query.
print_r('<pre>'.$query.'</pre>);

Building Queries

The main aim of the class MySQLQuery is to build SQL queries. The class has many pre-made methods which can be used to construct diffrent types of queries to perform diffrent operations on the database. The most important operations are:

• Insert.
• Update.
• Delete.
• Read (or select). For each operation, there exist a method in the class that corresponds to it.

Insert

The method MySQLQuery::insertRecord() is used to create an insert query. The method accepts an associative array. The keys of the array are columns keys and the values of the keys are the values that will be inserted.

$query->insertRecord([
  'user-id'=>99,
  'username'=>'MySuperHeroIsYou',
  'password'=>'f5d44b6d4a7d91821d602d03c096280e86888fa16cf9c27c540bbc2fd4e73932',
  'created-on'=>date('Y-m-d H:i:s')
]);

Update

The method MySQLQuery::updateRecord() is used to create an update query. The method accepts 4 parameters. Two of them are optional. The first parameter is an associative array. The keys of the array are columns keys and the values of the keys are the new values. The second parameter is also an associative array that has update condition columns (the where part).

$query->updateRecord([
  'username'=>'MySuperHeroIsYou',
  'password'=>'f5d44b6d4a7d91821d602d03c096280e86888fa16cf9c27c540bbc2fd4e73932',
],
[
  'user-id'=>99
]);

Delete

The method MySQLQuery::deleteRecord() is used to create a delete query. The method accepts an associative array that has delete condition columns (the where part of the delete query).

$query->deleteRecord([
  'user-id'=>99
]);

Connecting to MySQL Database

The class MySQLLink is used to connect to MySQL database. It acts as a wrapper for the extension mysqli. It also adds extra features to it like the ability to map query result to a class object. To connect to a database, we have to create new instance of the class. The constructor of the class accepts 4 parameters:

• Database host address.
• Database username.
• A password.
• Port number. The first parameter in most cases is localhost unless the database is hosted in another place. The username is the user which have a privilege to access the database. The port number is optional. If it is not provided, 3306 is used as a default value. If the connection to the database is established, we must select the database using the method MySQLLink::setDB()

The following code shows how to connect to the database. It also checks for connection errors.

use phMysql\MySQLLink;

$conn = new MySQLLink('localhost', 'root', '123456');

if($conn->getErrorCode() != 0) {
  //connection error. Show error message
  echo $conn->getErrorMessage();
} else {
  //connected. Select database now.
  
  if($conn->setDB('my_database')) {
  
    //connected. Now can execute quires.
  
  } else {
    //unable to set database
    echo $conn->getErrorMessage();
  }
}

Executing MySQL Query

After connecting to the database, we can start running queries on it. As we have said before, the class MySQLQuery is used to construct our queries. In order to execute them, we have to use the class MySQLLink. To be specific, the method MySQLLink::executeQuery(). The method will return a boolean. If the query is successfully executed, the method will return true. If it fails, the method will return false.

Lets assume that we have a connection to a database and we have our query class that has the table users_information. The following code sample shows how to execute an insert query.

$query->insertRecord([
  'user-id'=>99,
  'username'=>'MySuperHeroIsYou',
  'password'=>'f5d44b6d4a7d91821d602d03c096280e86888fa16cf9c27c540bbc2fd4e73932',
  'created-on'=>date('Y-m-d H:i:s')
]);
if($conn->executeQuery($query)) {
  //query executed without errors
} else {
  //something went wrong. Show error message
  echo $conn->getErrorMessage();
}

Fetching Raw Data

Mapping Query Result to Class Object

Joining Two Tables

phmysql's Issues

Mapping Custom Query Result to An Entity

If I created a custom select SQL query and used the method MySQLQuery::setQuery(), I cannot use the mapper which is used to map query result to an entity class.

It would be great to have a way to set a mapping between the entity class and the custom query result.

Support for Multi-Column Value in 'Select'

Currently, the library only support one condition per column in 'where' clause. The library should
be apple to handle multiple conditions (e.g : select * from my_table where my_col = 'val_1' or my_col = 'val_2).

This supported only if the type of index in 'condition-cols-and-vals' is a value. It should be supported in case of given index is a column index.

Unique on columns which are composite key

Problem

When using 2 or more columns as a composite key (using isPrimary function). All the columns receive the unique property which is wrong and leads to errors.

Purposed solution

Remove the unique property from the columns upon receiving the primary property if there are 2 or more columns unless specified otherwise which is unlikely.

Error on Check for table name validity.

Even if the name of the table is valid, The name of the table won't change from 'table' if we used class constructor.

Automation of Entity Class Creation

Problem

Usually, every table in the database is associated with an entity class. The entity class will mostly have all table columns as an attributes. For each attribute, there must be a public function to set and get the value of the attribute. Currently, the library has a way to map a select result to an entity class. The issue is that the entity class must be created manually by writing the whole entity class's code.

Suggested Solution

As I have said, each database table is usually mapped to an entity. If this is the case, why don't we have a method in the class MySQLTable Which can be used to create the entity class automatically? This would reduce the development effort a lot.

The method signature would be as follows:

• Method name: createEntityClass
• Method parameters:
• Name: $options. Type: array
• Returns: boolean

The options array should be an associative array. It can have the following indices:

• path: The location at which the entity class will be created inside.
• name: The name of the entity class.
• namespace: An optional namespace that the entity class can be added to.

More options can be added in the future to customize the created entity class.

No Support for prepared queries

See https://en.wikipedia.org/wiki/Prepared_statement for more info in prepared queries.
Advantages:

• SQL Injection protection.
• Efficient in case of executing same query multiple times (reduce load on database server).

Support For Boolean Type

Add support for creating a column which has type boolean. The idea is that the column will be created as a varchar or an int. The value of the column can be something like 1 or Y for true and 0 or N for false.

Currently, I'm doing it using varchar and I use Y for true and N for false. Other people might have other implementations.

Table Name Does not allow numbers

Same things apply to column names and FKs

Support For Array-Like Datatype

This is a proposal for a new feature which is to add support for storing PHP arrays as values in the database. The Idea is very simple, the array should be stored as a varchar and the values of the array should be comma separated. When retrieved from the database, the values should be presented as an array.

Issue with default values

If the default value for varchar is set to string, it is not quoted.

[Bug] Incorrect Query Structure In Case of Datetime For Multi-Value Select

Facilitate creation of database tables

Make tables creation within a database schema more easier.
Optionally, allow for migrations and rolling back changes. Reference: Laravel migrations.

Error on Check for column name validity.

Even if the name of the column is valid, The name of the column won't change from 'col' if we used class constructor