PHP-MySQLi-Database-Classの解説

MysqliDb -- MySQLi のシンプルなラッパーの解説 日本語訳

PHP-MySQLi-Database-Classは日本ではあまり知られていませんが、
githubでは☆2800スターを集めて世界中の人に使われているものとなります。

本家githubはこちらから
https://github.com/ThingEngineer/PHP-MySQLi-Database-Class

利用方法

このクラスを利用するには、まずMysqliDb.phpをプロジェクトにインポート。

require_once ('MysqliDb.php');

composerでの利用方法

composer経由でライブラリをインストールすることも可能です。

composer require thingengineer/mysqli-database-class:dev-master

初期化 (initialization)

デフォルトではutf8のcharsetが設定されているので、簡単な初期化が可能です。
$db = new MysqliDb ('host', 'username', 'password', 'databaseName');

高度な初期化

$db = new MysqliDb (Array (
                'host' => 'host',
                'username' => 'username', 
                'password' => 'password',
                'db'=> 'databaseName',
                'port' => 3306,
                'prefix' => 'my_',
                'charset' => 'utf8'));

テーブルの接頭辞、ポート、データベースのcharsetパラメータはオプションです。
charsetを設定すべきでない場合は、nullに設定します。
また、すでに接続されている mysqli オブジェクトを再利用することも可能です。

$mysqli = new mysqli ('host', 'username', 'password', 'databaseName');
$db = new MysqliDb ($mysqli);

オブジェクトの作成時にテーブルのプレフィックスが設定されていなかった場合は、
後から別の呼び出しでテーブルのプレフィックスを設定することができます。

$db->setPrefix ('my_');

mysqlへの接続が切断される場合、Mysqlidbは自動的に一度データベースに再接続しようとします。
この動作を無効にするには以下のようにしてください。

$db->autoReconnect = false;

すでに作成された mysqliDb オブジェクトを別のクラスや関数から取得する場合は以下のように設定します。

    function init () {
        // db staying private here
        $db = new MysqliDb ('host', 'username', 'password', 'databaseName');
    }
    function myfunc () {
        // obtain db object created in init  ()
        $db = MysqliDb::getInstance();
        ...
    }

複数のデータベース接続

複数のデータベースに接続する必要がある場合は、以下の方法で使用してください。

$db->addConnection('slave', Array (
                'host' => 'host',
                'username' => 'username',
                'password' => 'password',
                'db'=> 'databaseName',
                'port' => 3306,
                'prefix' => 'my_',
                'charset' => 'utf8')
);

データベースを選択するには connection() メソッドを使用します。

$users = $db->connection('slave')->get('users');

##オブジェクトのマッピング
dbObject.php は、モデル表現機能を提供するために mysqliDb の上に構築されたオブジェクトマッピングライブラリです。

挿入クエリ(Insert Query)

簡単な例

$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe'
);
$id = $db->insert ('users', $data);
if($id)
    echo 'user was created. Id=' . $id;

fuunction挿入

$data = Array (
	'login' => 'admin',
    'active' => true,
	'firstName' => 'John',
	'lastName' => 'Doe',
	'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
	// password = SHA1('secretpassword+salt')
	'createdAt' => $db->now(),
	// createdAt = NOW()
	'expires' => $db->now('+1Y')
	// expires = NOW() + interval 1 year
	// Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
);

$id = $db->insert ('users', $data);
if ($id)
    echo 'user was created. Id=' . $id;
else
    echo 'insert failed: ' . $db->getLastError();

INSERT ... ON DUPLICATE KEY UPDATE 構文

$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe',
               "createdAt" => $db->now(),
               "updatedAt" => $db->now(),
);
$updateColumns = Array ("updatedAt");
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert ('users', $data);

複数のデータセットを一度に挿入

$data = Array(
    Array ("login" => "admin",
        "firstName" => "John",
        "lastName" => 'Doe'
    ),
    Array ("login" => "other",
        "firstName" => "Another",
        "lastName" => 'User',
        "password" => "very_cool_hash"
    )
);
$ids = $db->insertMulti('users', $data);
if(!$ids) {
    echo 'insert failed: ' . $db->getLastError();
} else {
    echo 'new users inserted with following id\'s: ' . implode(', ', $ids);
}

すべてのデータセットが同じキーしか持たない場合は、単純化することができます。

$data = Array(
    Array ("admin", "John", "Doe"),
    Array ("other", "Another", "User")
);
$keys = Array("login", "firstName", "lastName");

$ids = $db->insertMulti('users', $data, $keys);
if(!$ids) {
    echo 'insert failed: ' . $db->getLastError();
} else {
    echo 'new users inserted with following id\'s: ' . implode(', ', $ids);
}

データ置換（REPLACE文）

Replace() メソッドは insert() と同じ API を実装しています。

データを更新する(UPDATE文)

$data = Array (
	'firstName' => 'Bobby',
	'lastName' => 'Tables',
	'editCount' => $db->inc(2),
	// editCount = editCount + 2;
	'active' => $db->not()
	// active = !active;
);
$db->where ('id', 1);
if ($db->update ('users', $data))
    echo $db->count . ' records were updated';
else
    echo 'update failed: ' . $db->getLastError();

update() はリミットパラメータもサポートしています。

$db->update ('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10

データを取得する(SELECT文)

セレクト/ゲット関数が呼び出された後、金額または返された行は$count変数に格納されます。

$users = $db->get('users'); //contains an Array of all users 
$users = $db->get('users', 10); //contains an Array 10 users

またはカスタムカラムを設定して選択します。関数を使用することもできます。

$cols = Array ("id", "name", "email");
$users = $db->get ("users", null, $cols);
if ($db->count > 0)
    foreach ($users as $user) { 
        print_r ($user);
    }

一行だけを選択する

$db->where ("id", 1);
$user = $db->getOne ("users");
echo $user['id'];

$stats = $db->getOne ("users", "sum(id), count(*) as cnt");
echo "total ".$stats['cnt']. "users found";

1つの列の値または関数の結果を選択

$count = $db->getValue ("users", "count(*)");
echo "{$count} users found";

複数の行から1つの列の値または関数の結果を選択

$logins = $db->getValue ("users", "login", null);
// select login from users
$logins = $db->getValue ("users", "login", 5);
// select login from users limit 5
foreach ($logins as $login)
    echo $login;

データを追加する(INSERT文)

また、特定のテーブルに .CSV または .XML データを読み込むこともできます。
.csv データを挿入するには、以下の構文を使用します。

$path_to_file = "/home/john/file.csv";
$db->loadData("users", $path_to_file);

これは、/home/john/ (johnのホームディレクトリ)フォルダにfile.csvという.csvファイルをロードします。
オプションでオプションの配列を添付することもできます。有効なオプションは以下の通りです。

Array(
	"fieldChar" => ';', 	// Char which separates the data
	"lineChar" => '\r\n', 	// Char which separates the lines
	"linesToIgnore" => 1	// Amount of lines to ignore at the beginning of the import
);

Attach

$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA ...

DATAの代わりにLOCAL DATAを使用するように指定

$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1, "loadDataLocal" => true);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA LOCAL ...

XML挿入

XML データをテーブルにロードするには、loadXML メソッドを使用します。構文は loadData 構文と同じです。

$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file);

オプションのパラメータを追加

Array(
	"linesToIgnore" => 0,		// Amount of lines / rows to ignore at the beginning of the import
	"rowTag"	=> "<user>"	// The tag which marks the beginning of an entry
)

使用例

$options = Array("linesToIgnore" => 0, "rowTag"	=> "<user>"):
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file, $options);

ページネーション(Pagination)

ページ分割された結果を取得するには get() の代わりに paginate() を使用します。

$page = 1;
// set page limit to 2 results per page. 20 by default
$db->pageLimit = 2;
$products = $db->arraybuilder()->paginate("products", $page);
echo "showing $page out of " . $db->totalPages;

MAP

純粋な配列で結果を取得するのではなく、必要なキーを含む連想配列で結果を取得することも可能です。get() で取得するフィールドが 2 つだけの場合は配列($k => $v)で、それ以外の場合は配列 ($k => 配列 ($v, $v)) で結果を返します。

$user = $db->map ('login')->ObjectBuilder()->getOne ('users', 'login, id');
Array
(
    [user1] => 1
)

$user = $db->map ('login')->ObjectBuilder()->getOne ('users', 'id,login,createdAt');
Array
(
    [user1] => stdClass Object
        (
            [id] => 1
            [login] => user1
            [createdAt] => 2015-10-22 22:27:53
        )

)

リターン型の定義

MysqliDbは、3つの異なるフォーマットで結果を返すことができます。配列の配列、オブジェクトの配列、Json文字列です。返り値の型を選択するには、ArrayBuilder()、ObjectBuilder()、JsonBuilder()メソッドを使用します。ArrayBuilder() はデフォルトの戻り値の型であることに注意してください。

// Array return type
$= $db->getOne("users");
echo $u['login'];
// Object return type
$u = $db->ObjectBuilder()->getOne("users");
echo $u->login;
// Json return type
$json = $db->JsonBuilder()->getOne("users");

生のSQLクエリの実行

$users = $db->rawQuery('SELECT * from users where id >= ?', Array (10));
foreach ($users as $user) {
    print_r ($user);
}

長いifチェックを避けるために、生のクエリの選択結果を扱うためのヘルパー関数があります。
結果の1行を取得します。

$user = $db->rawQueryOne ('select * from users where id=?', Array(10));
echo $user['login'];
// Object return type
$user = $db->ObjectBuilder()->rawQueryOne ('select * from users where id=?', Array(10));
echo $user->login;

1カラムの値を文字列として取得

$password = $db->rawQueryValue ('select password from users where id=? limit 1', Array(10).
echo "パスワードは {$password} です。
注意: rawQueryValue() が配列ではなく文字列を返すようにするには、 クエリの最後に 'limit 1' を追加しなければなりません。

複数の行から1列の値を取得します。

$logins = $db->rawQueryValue ('select login from users limit 10');
foreach ($logins as $login)
    echo $login;

応用例

$params = Array(1, 'admin');
$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);
print_r($users); // contains Array of returned rows

// will handle any SQL query
$params = Array(10, 1, 10, 11, 2, 10);
$q = "(
    SELECT a FROM t1
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
) UNION (
    SELECT a FROM t2 
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
)";
$results = $db->rawQuery ($q, $params);
print_r ($results); // contains Array of returned rows

データを取得する条件を設定する(WHERE句) & グループ化したデータを取得する条件を設定する(HAVING句)

where(), orWhere(), having(), orHaving() メソッドを使用すると、クエリの where と having の条件を指定することができます。where() でサポートされているすべての条件は、having() でも同様にサポートされています。

警告: カラム間の比較を行うためには、生のwhere条件のみをカラム名として使用するか、関数をバインド変数として渡すことはできません。

$db->where ('id', 1);
$db->where ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';

$db->where ('id', 1);
$db->having ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 HAVING login='admin';

演算子を使用して、カラム間の比較

// 間違い
$db->where ('lastLogin', 'createdAt');
// 正しい
$db->where ('lastLogin = createdAt');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE lastLogin = createdAt;

$db->where ('id', 50, ">=");
// or $db->where ('id', Array ('>=' => 50));
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id >= 50;

BETWEEN / NOT BETWEEN:

$db->where('id', Array (4, 20), 'BETWEEN');
// or $db->where ('id', Array ('BETWEEN' => Array(4, 20)));

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20

IN / NOT IN:

$db->where('id', Array(1, 5, 27, -1, 'd'), 'IN');
// or $db->where('id', Array( 'IN' => Array(1, 5, 27, -1, 'd') ) );

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');

OR CASE:

$db->where ('firstName', 'John');
$db->orWhere ('firstName', 'Peter');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'

NULL比較

$db->where ("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL

LIKE比較

$db->where ("fullName", 'John%', 'like');
$results = $db->get("users");
// Gives: SELECT * FROM users where fullName like 'John%'

$db->where ("id != companyId");
$db->where ("DATE(createdAt) = DATE(lastLogin)");
$results = $db->get("users");

Or raw condition with variables:

$db->where ("(id = ? or id = ?)", Array(6,2));
$db->where ("login","mike")
$res = $db->get ("users");
// Gives: SELECT * FROM users WHERE (id = 6 or id = 2) and login='mike';

ページネーション総数の例

$offset = 10;
$count = 15;
$users = $db->withTotalCount()->get('users', Array ($offset, $count));
echo "Showing {$count} from {$db->totalCount}";

ページネーション総数の例

クエリーキーワード

LOW PRIORITY | DELAYED | HIGH PRIORITY | IGNORE およびその他の mysql キーワードを INSERT ()、REPLACE ()、GET ()、UPDATE ()、DELETE() メソッド、または FOR UPDATE | LOCK IN SHARE MODE に SELECT () に追加するには、以下のようにします。

$db->setQueryOption ('LOW_PRIORITY')->insert ($table, $param);
// GIVES: INSERT LOW_PRIORITY INTO table ...

$db->setQueryOption ('FOR UPDATE')->get ('users');
// GIVES: SELECT * FROM USERS FOR UPDATE;

キーワードの配列を使用

$db->setQueryOption (Array('LOW_PRIORITY', 'IGNORE'))->insert ($table,$param);
// GIVES: INSERT LOW_PRIORITY IGNORE INTO table ...

同じようにキーワードはSELECTクエリでも使用できます。

$db->setQueryOption ('SQL_NO_CACHE');
$db->get("users");
// GIVES: SELECT SQL_NO_CACHE * FROM USERS;

オプションで、メソッドチェーニングを使用して、何度もオブジェクトを参照せずに何度もどこを呼び出すことができます。

$results = $db
	->where('id', 1)
	->where('login', 'admin')
	->get('users');

データを削除する(DELETE文)

$db->where('id', 1);
if($db->delete('users')) echo 'successfully deleted';

取得するデータをソートする(ORDER BY句)

$db->orderBy("id","asc");
$db->orderBy("login","Desc");
$db->orderBy("RAND ()");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();

値で順番に並べる

$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));
$db->get('users');
// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;

setPrefix()の機能を使用していて、orderBy()メソッドでテーブル名を使用する必要がある場合は、
テーブル名が ``でエスケープされていることを確認してください。

$db->setPrefix ("t_");
$db->orderBy ("users.id","asc");
$results = $db->get ('users');
// WRONG: That will give: SELECT * FROM t_users ORDER BY users.id ASC;

$db->setPrefix ("t_");
$db->orderBy ("`users`.id", "asc");
$results = $db->get ('users');
// CORRECT: That will give: SELECT * FROM t_users ORDER BY t_users.id ASC;

データをグループ化する(GROUP BY句)

$db->groupBy ("name");
$results = $db->get ('users');
// Gives: SELECT * FROM users GROUP BY name;

内部結合を行う(INNER JOIN句)

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);

結合文にAND条件を追加

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

結合文にOR条件を追加

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinOrWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

プロパティ共有

プロパティをコピーすることも可能です。

$db->where ("agentId", 10);
$db->where ("active", true);

$customers = $db->copy ();
$res = $customers->get ("customers", Array (10, 10));
// SELECT * FROM customers where agentId = 10 and active = 1 limit 10, 10

$cnt = $db->getValue ("customers", "count(id)");
echo "total records found: " . $cnt;
// SELECT count(id) FROM users where agentId = 10 and active = 1

サブクエリ

Subquery init
Subquery init without an alias to use in inserts/updates/where Eg. (select * from users)

$sq = $db->subQuery();
$sq->get ("users");
A subquery with an alias specified to use in JOINs . Eg. (select * from users) sq

$sq = $db->subQuery("sq");
$sq->get ("users");
Subquery in selects:

$ids = $db->subQuery ();
$ids->where ("qty", 2, ">");
$ids->get ("products", null, "userId");

$db->where ("id", $ids, 'in');
$res = $db->get ("users");
// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)

MySQLサブクエリを使ったINSERT文

$userIdQ = $db->subQuery ();
$userIdQ->where ("id", 6);
$userIdQ->getOne ("users", "name"),

$data = Array (
    "productName" => "test product",
    "userId" => $userIdQ,
    "lastUpdated" => $db->now()
);
$id = $db->insert ("products", $data);
// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

MySQLサブクエリを使ったJOIN文

$usersQ = $db->subQuery ("u");
$usersQ->where ("active", 1);
$usersQ->get ("users");

$db->join($usersQ, "p.userId=u.id", "LEFT");
$products = $db->get ("products p", null, "u.login, p.productName");
print_r ($products);
// SELECT u.login, p.productName FROM products p LEFT JOIN (SELECT * FROM t_users WHERE active = 1) u on p.userId=u.id;

EXISTS と NOT EXISTS

$sub = $db->subQuery();
    $sub->where("company", 'testCompany');
    $sub->get ("users", null, 'userId');
$db->where (null, $sub, 'exists');
$products = $db->get ("products");
// Gives SELECT * FROM products WHERE EXISTS (select userId from users where company='testCompany')

Has

この関数の前に where メソッドを呼び出して指定した where 条件を満たす要素が少なくとも存在する場合に TRUE を返す便利な関数です。

$db->where("user", $user);
$db->where("password", md5($password));
if($db->has("users")) {
    return "You are logged";
} else {
    return "Wrong user/password";
}

Helper methods

データベースから切断

    $db->disconnect();

mysql の接続が切れた場合の再接続

if (!$db->ping())
    $db->connect()

最後に実行されたSQLクエリを取得します。この関数がSQLクエリを返すのはデバッグ目的のみであることに注意してください。

    $db->get('users');
    echo "Last executed query was ". $db->getLastQuery();

テーブルが存在するかどうかをチェック

    if ($db->tableExists ('users'))
        echo "hooray";

mysqli_real_escape_string() wrapper:

$escaped = $db->escape ("' and 1=1");

トランザクションヘルパー

トランザクションはinnoDBのテーブルで動作していることを覚えておいてください。挿入に失敗した場合は、トランザクションをロールバックします。

$db->startTransaction();
...
if (!$db->insert ('myTable', $insertData)) {
    //Error while saving, cancel new record
    $db->rollback();
} else {
    //OK
    $db->commit();
}

エラーヘルパー

クエリを実行した後、エラーが発生したかどうかをチェックするオプションがあります。
MySQLのエラー文字列や、最後に実行したクエリのエラーコードを取得することができます。

$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']);

if ($db->getLastErrno() === 0)
    echo 'Update succesfull';
else
    echo 'Update failed. Error: '. $db->getLastError();

クエリ実行時間のベンチマーク

クエリの実行時間を追跡するためには、setTrace()関数を呼び出す必要があります。

$db->setTrace (true);
// As a second parameter it is possible to define prefix of the path which should be striped from filename
// $db->setTrace (true, $_SERVER['SERVER_ROOT']);
$db->get("users");
$db->get("test");
print_r ($db->trace);

    [0] => Array
        (
            [0] => SELECT  * FROM t_users ORDER BY `id` ASC
            [1] => 0.0010669231414795
            [2] => MysqliDb->get() >>  file "/avb/work/PHP-MySQLi-Database-Class/tests.php" line #151
        )

    [1] => Array
        (
            [0] => SELECT  * FROM t_test
            [1] => 0.00069189071655273
            [2] => MysqliDb->get() >>  file "/avb/work/PHP-MySQLi-Database-Class/tests.php" line #152
        )

テーブルロック

テーブルをロックするには、setLockMethodと一緒にロックメソッドを使用します。
次の例では、書き込みアクセスのためにテーブルのユーザをロックします。

$db->setLockMethod("WRITE")->lock("users");

別のロックを呼び出すと、最初のロックが解除されます。

$db->unlock();

以前にロックされていたテーブルのロックを解除します。複数のテーブルをロックするには、配列を使用します。

$db->setLockMethod("READ")->lock(array("users", "log"));

これは、テーブルのユーザーとログをロックします。その後に*unlock()を使用するか、
テーブルがロックされたままになることを確認してください!