WebX-Db - Mysql PHP library

Main features and design goals of webx-db: * Name based auto-escaped parametrization. * Nested transaction support (savepoint X and rollback to savepoint X). * Key violation exception with name of violated key. * Easy to use in IOC based designs. * Light weight., _(*1)

Installing

* Packagist: webx-db

Getting started

    use WebX\Db\Db;
    use WebX\Db\Impl\DbImpl;

    $db = new DbImpl([
        "user" => "mysqlUser",
        "password" => "mysqlPassword",
        "database" => "mysqlDatabase"
    ]);

    //or

    $db = new DbImpl($mysqli); //Instance of mysqli

A simple insert

    $person = ["first"=>"Alex", "last"=>"Morris","email"=>"am@domain.com"];
    $db->execute("INSERT INTO people (first,last,email,phone) VALUES(:first,:last,:email,:phone)", $person);
    //SQL: INSERT INTO people (first,last,email,phone) VALUES('Alex','Morris','am@domain',NULL);

    $id = $db->insertId();          //The value of the autogenerated primary key.

A simple select

    foreach($db->allRows("SELECT * FROM table") as $row) {
        echo($row->string('first'));
        echo($row->string('last'));
    }

Insert with key violations

    $person = [...];
    try {
        $db->execute("INSERT INTO people (first,last,email) VALUES(:first,:last,:email)", $person);
        echo("Person registered");
    } catch(DbKeyException $e) {
        if($e->key()==='emailKey') { // Name of the defined unique key in MySQL
            echo("The {$person->string('email')} is already registered";
        } else {
            echo("Some other key violation occured.");
        }
    }

Nested transactions


    $db->startTx();
    $db->execute("INSERT INTO table (col) VALUES('1')");

        $db->startTx();
        $db->execute("INSERT INTO table (col) VALUES('2')"); // Will not be commited
        $db->rollbackTx();

        $db->startTx();
        $db->execute("INSERT INTO table (col) VALUES('3')");
        $db->commitTx();

    $db->commitTx();

Note: If an outer tx is rolled back all its inner txs are also rolled back., _(*2)

Wrap transactions in a closure

The execution of the closure is wrapped in a startTx() commitTx()|rollbackTx() depending on if the closure throws an exception or not., _(*3)

    $db->executeInTx(function($db) { // The db instance must be passed as the only argument to closure.
        $db->execute("INSERT INTO table (col) VALUES('1')");
        $db->execute("INSERT INTO table (col) VALUES('2')");

        if(true) { //Now the transaction will be rolled back
            throw new \Exception("An error occured");
        }
    });