lcloss/db

Simple PHP package to manage database

dev-main 2020-10-22 19:23 UTC

Requires

• lcloss/env: dev-main

Requires (Dev)

• phpunit/phpunit: 9

MIT

• Luciano Closs

• php
• mysql
• pdo
• sgbd

README

Simple PHP package to manage database

Install

$ composer require lcloss/db

Requirements

On root folder, create a .env file with these fields:

(Change accordanly your configuration)

[database]
driver = mysql
server = localhost
port   = 3306
dbname = testdb
user   = root
password = 

Usage

Using Database

You can use database directly, from static method exec:

    Database::exec( 'DROP DATABASE IF EXISTS activerecord;');
    Database::exec( 'CREATE DATABASE activerecord DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;');
    Database::exec( 'USE activerecord;');
    Database::exec( 'DROP TABLE IF EXISTS cliente;');
    $sql = <<<EOV
    CREATE TABLE client 
    (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        name        VARCHAR(80) NOT NULL,
        address     TEXT,
        updated_at  DATETIME,
        created_at  DATETIME
    );
EOV;
    Database::exec( $sql );

Creating a model

use LCloss\DB\ActiveRecord;

class Client extends ActiveRecord
{
    // Define automatically filled columns updated_at and created_at
    protected $log_timestamp = true;

    public function lastClients( $days = 7 )
    {
        return Client::all("created_at > '" . date('Y-m-d h:m:i', strtotime("-{$days} days") ));
    }
}

Insert rows

    $client = new Client();
    $client->name = "Some client";
    $client->address = "Some address street";

    $client->save();

Select rows by id

    $client = Client::find(1);

Select all data

    $clients = Client::all();

Navigating

    $cond = "name LIKE 'Some%'";
    $limit = 10;
    $offset = 20;
    $clients = Client::all($cond, $limit, $offset);

Update rows

    $client = Client::find(1);

    $client->name = "Changed to this name";
    $client->save();

Delete rows

    $client = Client::find(1);
    $client->delete();

Retrieving a list of rows

    for ($i = 1; $i < 11; $i++) {
        $client = new Client();
        $client->name = "Client {$i}";
        $client->address = "Street number {$i}";
        
        if ( $client->save(); ) {
            echo "Client {$i} saved!\n<br />";
        } else {
            echo "There are a problem when saving client {$i}!\n<br />";
        }
    }

    $clients = Client::all();

    foreach( $clients as $client )
    {
        echo $client->name . "\n<br />";
    }

Find first row

    $client = Client::findFirst("name = 'Client 4'");
    echo $client->name . "\n<br />";

Retrieving a list with conditions

    $res = Client::all("address = 'Street 1'");
    foreach( $clients as $client )
    {
        echo $client->name . "\n<br />";
    }

Counting rows

    $rows = Client::count();
    echo "There are {$rows} clients on database.\n<br />";