Working with database

Polavi application uses MySql as a database storage. You can interact with database very easy by using query builder.

Configuration

The database configuration is located at config/config.php

<?php
define('DB_DRIVER', 'mysql');
define('DB_HOST', 'localhost');
define('DB_DATABASE', 'database');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_PREFIX', '');

This file will be generated automatically when you install the application.

Open a connection

To open a connection, you just need to create an instance of Polavi\Services\Db\Processor class

$conn = new Processor();

or sometime you can use this short form

$conn = _mysql();

Transactions

Start a transaction

$conn->startTransaction();

Commit a transaction

$conn->commit();

Rollback a transaction

$conn->rollback();

Executing A Select Query

Load a single row

This example shows you how to select a user from the user table by ID

$conn->getTable('user')
->load($id);

or using another column

$conn->getTable('user')
->loadByField('email', $email);

Load a collection

Let's say we want to get all active user

$activeUsers = $conn->getTable('user')
->where('status', '=', 1)
->fetchAllAssoc();
foreach($activeUsers as $user) {
echo $user['email'];
}

with limit and pagination

$activeUsers = $conn->getTable('user')
->where('status', '=', 1)
->fetchAssoc([
'limit'=>100,
'page'=>1,
'sort_by'=>'id',
'sort_order'=>'ASC'
]);
foreach($activeUsers as $user) {
echo $user['email'];
}

Executing an insert statement

Let's say we want to insert a user to user table

$userData = [
'email'=> 'email@example.com',
'full_name'=> 'John',
'user_name'=>'john'
];
$conn->getTable('user')->insert($userData);

In the above example, $userData is an array with key is the column name. Processor object will map this array to the table. To against SQL injection, Processor object will bind a parameter to every value automatically.

Executing an update statement

$userData = [
'email'=> 'email@example.com',
'full_name'=> 'John',
'user_name'=>'john'
];
$conn->getTable('user')->where('id', '=', 1)->update($userData);

Executing a delete statement

$conn->getTable('user')->where('id', '=', 1)->delete();

"Where" condition

Simple

$conn->getTable('product')->where('sku', '=', 'sku')->delete();

A little bit more complex

$conn->getTable('product')->where('status', '=', 0)->andWhere('price', '>=', 10)->delete();

"Grouped" conditions

$conn->getTable('product')
->where('status', '=', 0)
->andWhere('price', '>=', 10, '(')
->orWhere('visibility', '=', 1, null, ')')
->delete();

Above code will generate a query like this:

DELETE FROM 'product' WHERE status = 0 AND (price >= 10 OR visibility = 1)

Joining table

This example shows you how to join table product and table product_description

$conn->getTable('product')
->leftJoin('product_description');

You may notice that in the above example I do not provide any join condition. Actually, Processor will do it automatically for you based on foreign key between those 2 tables. It also means you can not join 2 tables if they have no relationship (foreign key defined).

Joining table with an extra condition

Example:

$conn->getTable('product')
->leftJoin('product_description', null, [
[
'column' => "product_description.language_id",
'operator' => "=",
'value' => $language,
'ao' => 'and',
'start_group' => null,
'end_group' => null
]
]);

Executing a raw query

Sometime, you may want to execute a raw sql query. This is how you do it:

$conn->executeQuery("SELECT * from product");

With binding

$conn->executeQuery("SELECT * from product where sku=:sku", ['sku'=> 'product-sku']);