MyDB

• Install
• Class diagram
• Database for examples
• Usage example
• CRUD
  • Adding data
  • Reading data
  • Updating data
  • Data deletion
• Query builder
• Error handling
• Pagination

Install via composer

Command line

composer require texlab/mydb

Example composer.json file

{
    "require": {
        "texlab/mydb": "^0.0.5"
    }
}

Class diagram

Database for examples

CREATE DATABASE IF NOT EXISTS `mydb`;

USE `mydb`;

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Usage example

<?php

require 'vendor/autoload.php';

use TexLab\MyDB\DbEntity;
use TexLab\MyDB\DB;

$link = DB::link([
    'host' => 'localhost',
    'username' => 'root',
    'password' => '',
    'dbname' => 'mydb'
]);

$table1 = new DbEntity('table1', $link);

echo json_encode($table1->get());

CRUD

Adding data:

$table1->add([
    'name' => 'Peter',
    'description' => 'Director'
]);

Reading data:

$table1->get();

or a row with the given id

$table1->get(['id' => 3]);

Updating data:

$table1->edit(['id' => 2], [
    'name' => 'Alex',
    'description' => 'Manager'
]);

Data deletion:

$table1->del(['id' => 1]);

Custom queries

echo json_encode($table1->runSQL("SELECT * FROM table1"));

Query builder

echo json_encode(
    $table1
        ->reset()
        ->setSelect('id, name')
        ->setWhere("name like 'A%'")
        ->get()
);

$table1
    ->reset()
    ->setSelect('name, description')
    ->setWhere("description = 'Manager'")
    ->setOrderBy('name');

echo json_encode(
    $table1->get()
);

$table1->setSelect('*');

echo json_encode(
    $table1->get()
);

Error handling

<?php

require '../vendor/autoload.php';

use TexLab\MyDB\DB;
use TexLab\MyDB\Runner;

$runner = new Runner(
    DB::link(
        [
            'host' => 'localhost',
            'username' => 'root',
            'password' => 'root',
            'dbname' => 'test_db'
        ]
    )
);

$runner->setErrorHandler(
    function ($mysqli, $sql) {
        //put your error handling code here
        print_r([$mysqli->error, $mysqli->errno, $sql]);
    }
);

$runner->runSQL("SELECT * FROM unknown_table");

Result:

Array
(
    [0] => Table 'test_db.unknown_table' doesn't exist
    [1] => 1146
    [2] => SELECT * FROM unknown_table
)

Pagination

echo $table1
        ->setPageSize(2)
        ->pageCount();

echo json_encode($table1->setPageSize(2)->getPage(1));