Download the PHP package alexya-framework/database without Composer

Database

Alexya's database components

Contents

• Connection
  • Connecting to a database
  • Executing queries
  • Advanced Database Functions
• Query builder
  • Select queries
  • Insert queries
  • Update queries
  • Delete queries
  • WHERE Syntax
  • Other SQL Functions
• ORM
  • CRUD
    • Creating records
    • Reading records
    • Updating records
    • Deleting records
  • Relations

Connection

The class \Alexya\Database\Connection provides an easy layer for connecting to a database and execute queries.

Connecting to a database

To connect to a database you'll need to instance a \Alexya\Database\Connection object, the constructor accepts the following parameters:

• A string being server's host/ip
• An integer being server's port
• A string being database username
• A string being database password
• A string being database password

Executing queries

The method \Alexya\Database\Connection::execute accepts as parameter a string that is the SQL query to execute:

You can also send a boolean indicating if the connection should fetch all results or just one, you can also specify how the results will be fetched with a third parameter, by default it's PDO::FETCH_ASSOC.

Advanced Database Functions

If you want to take a total control over \Alexya\Database\Connection class you can use the method \Alexya\Database\Connection::getConnection that retruns the current PDO object with the database connection.

When an error happens the method \Alexya\Database\Connection::getError returns the latest error:

You can see the last executed query with the property \Alexya\Database\Connection::lastQuery:

Query builder

The class \Alexya\Database\QueryBuilder provides a fluent way for generating queries.

The constructor accepts as parameter the \Alexya\Database\Connection object with the connection to the database. Once you've generated the query you can execute it directly with the method execute or retrieve the SQL with the method getQuery.

If you want to build more than one query with the same \Alexya\Database\QueryBuilder use the method clear each time you finish a query.

Select queries

The method \Alexya\Database\QueryBuilder::select begins a SELECT query and accepts 3 types of parameters:

• Nothing (the same as passing "*" as parameter)
• A string that contains the name of the colum to select
• An array containing the columns to select

Next we must indicate the table that we will use for getting the columns, we do that with the method \Alexya\Database\QueryBuilder::from that accepts as parameter a string containing the name of the table:

Insert queries

The method \Alexya\Database\QueryBuilder::insert begins an INSERT query and accepts as parameter a string that is the name of the table to insert the new record:

The next thing is to add the values to insert to the table, for that we use the method \Alexya\Database\QueryBuilder::values that accepts an array as parameter, it contains the values to insert into table:

If an index of the array is an object or an array it will serialize it to convert it to a string.

Alternatively you can encode the values with JSON:

Update queries

The method \Alexya\Database\QueryBuilder::update begins an UPDATE query and accepts as parameter a stringt that is the name of the table to alter.

Now we have to set the values to alter, we do that with the method \Alexya\Database\QueryBuilder::set which accepts as parameter an array with the values to alter. You can append to the end of the key the following tags:

• [+]
• [-]
• [*]
• [/]

You can also serialize values like in INSERT queries.

Delete queries

Delete queries begins with the method \Alexya\Database\QueryBuilder::delete that accepts as parameter the table name as string:

WHERE Syntax

The method \Alexya\Database\QueryBuilder::where starts the WHERE clause and accepts as parameter containing an array:

For more advanced conditions you can use the followin tags:

• [>]
• [>=]
• [!]
• [<>]
• [><]

You can also serialize data by adding (JSON) to the begining of the key:

You can also add AND and OR statemets:

Other SQL functions

\Alexya\Database\QueryBuilder provides 3 other methods for SQL clauses:

The method \Alexya\Database\QueryBuilder::limit begins a LIMIT clause and can accept an integer or an array as parameter:

The method \Alexya\Database\QueryBuilder::offset begins a OFFSET clause and accepts an integer as parameter:

The method \Alexya\Database\QueryBuilder::sql appends raw SQL to the query, this method does not avoids SQL injection so it's not recommended to use unless you know what you're doing:

ORM

The class \Alexya\Database\ORM\Model acts as the mediator between the database table and the PHP code.

Before anything you should initialize the class with the method initialize. It accepts as parameter an object of type \Alexya\Database\Connection being the connection to the database and a string being the base namespace where the Model classes are located, this is if you want to store the Model classes in a separated namespace (default is "\"):

You should write a class that extends this for each model, but when you're following the naming conventions you'll surely finish with a package full of empty classes. To prevent this you can use the method instance which accepts as parameter the name of the database table. Also, all static methods accepts as last parameter the name of the table.

Extending this class allows you to take more control over it. You can specify the name of the table, the name of the primary key, relations...

The table name is, by default, the snake_case, plural name of the class, if you want to override it change the property _table with the name of the table:

The primary key is, by default, id, if you want to override it change the property _primaryKey with the name of the primary key:

The method onInstance is executed when the class has been instantiated, use it instead of the constructor.

CRUD

CRUD stands for Create, Read, Update, Delete.

Creating records

To create a new record call the method \Alexya\Database\ORM\Model::create:

Reading records

The method find finds a record from the database and returns an instance of the Model class. It accepts as parameter an integer being the value of the primary key or an array contaning the WHERE clause of the query:

You can send a second integer parameter being the amount of records to fetch from the database. If it's omited it will return a single record, otherwise an array of speficied amount of records.

Updating records

Once you have the ORM instance you can use the methods \Alexya\Database\ORM\Model::get and \Alexya\Database\ORM\Model::set to changes the values of columns, it also has the magic methods get, set, isset and unset for an alternative syntax. To update the database use the method \Alexya\Database\ORM\Model::save:

Deleting records

To delete records you must call the method \Alexya\Database\ORM\Model::delete:

Relations

The relations are established between two tables that share something in common.

The \Alexya\Database\ORM\Model class offers an easy way for establishing relations through the static property $_relations, which can be public or protected, but never private.

The $_relations property is an array. Each index of this array will be interpreted as a relation rule.

A rule can be:

• A string being the class name of the Model class that represents the table or the table name (if the class doesn't exist).
• An array containing the configuration of the rule.

If the index is an array, the key must be the name of the Model class and can have the following index:

• localKey: Name of the local key used for the relation (defaults to the foreign table name followed by the local primary key).
• foreignKey: Name of the foreign key used for the relation (defaults to the foreign primary key).
• type: Type of the relation (has_one or has_many) (defaults to has_one).
• name: Name of the property to create for the resulting relation (defaults to the name of the class).
• amount: Amount of records to retrieve for the relation (defaults to all records in the table).
• class: Name of the class that will be instanced for the relation (defaults to the Model class of the foreign table in case of a has_one relation and an array of Model classes of the foreign table in case of a has_many relation).
• setRelations: Whether the instanced models of the relation should process their relations array too or not (defaults to false).

The name of the Model class can either start with the prefix sent to the initialize method, or the name of table.

Example:

This example will make two relations:

• One for the table messages.
• One for the table configurations.

The relation for the messages table will load all records from the database that matches the local/foreign key relation.

By default, the local key is the name of the local table followed by the primary key of the foreign table, and the foreign key is the primary key of the foreign table.

So, given that User andMessagesfollows the standards of this class the local key would bemessages_idand the foreign key would beid, so the generated query would beSELECT * FROM messages WHERE id=users.messages_id;`.

For overriding the default local key change the index localKey and for overriding the default foreign key, change the index foreignKey.

As the message have a sender and a recipient, assuming that the id column on the messages table is the messages_id of the user is wrong, so instead we change the foreign key to a more suitable one: to_users_id

By default all the relations are one to one, meaning that only one from the database will be fetched.

As the user might have more than one message, we change the relation type by changing the type index in the value.

After this, we are able to access to all messages sent to the user through the property $user->Message which would be an array with all Message classes representing the records from the database.

However, calling that property Message isn't the best option since it's not a single message, but a collection of messages. We can change this name by setting the name index to something different.

Now all messages are in the $user->Messages property.

Another thing that we would like to change is the amount of records to retrieve from the database and Whether the instanced models should process their relations too. We can do this by changing the index amount and setRelations respectively.

Finally, we can decide if we should retrieve the messages only if the user has verified his email, we do this with the index condition, which is a closure that should return a boolean telling if the relation should be processed or not.

The closure must accept as parameter an array that will contain the result of the query.