Working with database

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


The database configuration is located at config/config.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();


Start a transaction


Commit a transaction


Rollback a transaction


Executing A Select Query

Load a single row

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


or using another column

->loadByField('email', $email);

Load a collection

Let's say we want to get all active user

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

with limit and pagination

$activeUsers = $conn->getTable('user')
->where('status', '=', 1)
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'=> '',
'full_name'=> 'John',

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'=> '',
'full_name'=> 'John',
$conn->getTable('user')->where('id', '=', 1)->update($userData);

Executing a delete statement

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

"Where" condition


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

A little bit more complex

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

"Grouped" conditions

->where('status', '=', 0)
->andWhere('price', '>=', 10, '(')
->orWhere('visibility', '=', 1, null, ')')

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


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


->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']);