08. Database

Set up database

If your application requires to work with a database you need to set up it first. For that, open enviroment file /App/configs/.env and add all required settings to connect with the database:

DB_NAME='<Database Name>'
DB_USER='<Database Username>'
DB_PASSWORD='<Database Password>'
DB_HOST='localhost'

A minimum of two tables is required for the correct work of the Videna framework: users and tokens. You can easily create them by the SQL below (for example via PHPMyAdmin):

CREATE TABLE `videna`.`users` ( 
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , 
    `name` VARCHAR(100) NOT NULL, 
    `last_name` VARCHAR(100) NOT NULL DEFAULT '' , 
    `email` VARCHAR(100) NOT NULL , 
    `account` TINYINT UNSIGNED NOT NULL DEFAULT '100' , 
    `lang` VARCHAR(2) NOT NULL , 
    `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `videna`.`tokens` ( 
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , 
    `user_id` INT UNSIGNED NOT NULL , 
    `public_key` VARCHAR(100) NOT NULL , 
    `private_key` VARCHAR(100) NOT NULL , 
    `expires` INT UNSIGNED NOT NULL DEFAULT '0' , 
    `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

Next, create at least one administrator record in table users (with account setting =200):

INSERT INTO `users` (`name`, `last_name`, `email`, `account`, `lang`) 
    VALUES ('Name', 'LastName', 'admin.email@domain.com', '200', 'en');

Now your application is ready to work with the database.

Work process

Now, you can create custom application models. There are a couple of simple rules:

• Place your application models in the folder \App\Models\ in the appropriate namespace (App\Models;).
• Each model class needs to be extended from the base class \Videna\Models\Model.
• Each method needs to start with $db = static::getDB(); to connect with database.

Example of the application model \App\Models\Settings that works with a fake table settings:

namespace App\Models;

use \Videna\Models\Database;

class Settings extends Database
{
    public static function getSettingsById($id) {
        // Connect to Database:
        $db = static::getDB();
        // Prepare query:
        $sql = 'SELECT * FROM settings WHERE id = :id LIMIT 1';
        $stmt = $db->prepare($sql);
        // Execute statement and return record:
        $stmt->execute($id);
        return $stmt->fetch();
    }
}

Pre-cooked SQL

For any of your application models, you can use pre-cooked SQL requests. To use pre-cooked SQL requests, here are some rules:

• at any controllers where you call the model, use namespace \Videna\Models\Model:

use \Videna\Models\Model;

• model class name needs to match the table name in the database. For example, class name UserSettings works with table user_settings.

getAll()

Get all records from the table.

Model::getAll()

• No parameters are required.
• Returns array with all records or FALSE.

Example. Get all users from table users:

$users = Users::getAll();

get()

Find record(s) by one or more criteria for the existing model:

Model::get($criteria = [], $limit = '*');

here:

• $criteria is an array of search criteria, for example: ['email' => 'john.doe@domain.com'], default is empty array what means "no specific criteria".
• $limit is a number of records to get, for example: 1. Default is '*' what means "all records".
• Returns array with the record(s) or FALSE.

Example 1. Get all users from table users:

$users = Users::get();

Example 2. Get all admin users from table users:

$admins = Users::get([ 'account' => 200 ]);

Example 3. Get one user from the table users, by user email:

$user = Users::get(['email' => 'john.doe@domain.com'], 1);

count()

Count records qty by one or more criteria for the existing model:

Model::count($criteria);

here:

• $criteria is an array of criteria, for example: ['email' => 'john.doe@domain.com'].
• returns number of records or FALSE.

Example. Count all admin users in the table users:

$adminsQty = Users::count([ 'account' => 200 ]);

add()

Add record into DB for the existing model

Model::add($criteria);

here:

• $criteria is an array of inserted data.
• returns inserted record ID.

Example. Add new record in table users:

$userID = Users::add([
    'name' => 'John',
    'last_name' => 'Doe',
    'email' => 'john.doe@domain.com'
]);

del()

Delete record(s) from DB by one or more criteria for the existing model

Model::del($criteria, $condition = 'AND');

here:

• $criteria is an array of criteria for deleted records.
• $condition is a string with logic for criteria, by default 'AND'.
• returns a number of rows affected by the SQL statement.

Example. Delete one user from the table users, by user email:

Users::del(['email' => 'john.doe@domain.com']);

RAW SQL

Videna's database architecture allows for the use of PDO parameter binding, which protects your queries from potential SQL attacks.

To use RAW SQL requests, use the namespace:

use \Videna\Models\DB;

Raw call to the database

Use it for a SQL statement that does not require return values.

Example:

DB::query('DROP TABLE users');

RAW select

Raw SELECT records from the database using PDO placeholders; returns an array with all records.

Example:

$users = DB::select(
    'SELECT * FROM users WHERE account = :account', 
    ['account' => 100]
);

RAW insert

Raw INSERT of records into database using PDO placeholders; returns last added record ID.

Example:

$users = DB::insert(
    'INSERT INTO `users` (name, email) VALUES (:name, :email)',
    ['name' => 'John Doe', 'email' => 'john.doe@domain.com']
);

RAW update

Raw UPDATE of records in the database using PDO placeholders; returns a number of rows affected by the SQL statement.

Example:

$users = DB::update(
    'UPDATE users SET email = :email WHERE id = :id',
    ['id' => 123, 'email' => 'john.doe@domain.com']
);

RAW delete

Raw DELETE of records from database using PDO placeholders; returns a number of rows affected by the SQL statement.

Example:

$users = DB::delete(
    'DELETE FROM users WHERE id = :id',
    ['id' => 123]
);