====== MariaDB10.4 PHPサンプルプログラム ======
phpMyAdmin 4.9.0.1 (MariaDB 10.4.6)

 --- //[[http://www.y2sunlight.com|y2sunlight]] 2019-10-10//

[[mariadb:top|MariaDBに戻る]]

関連記事
  * [[mariadb:10.4:phpmyadmin|MariaDB10.4 phpMyAdminの使い方]]
  * MariaDB10.4 PHPサンプルプログラム
  * [[mariadb:10.4:system-variables|MariaDB10.4 システム変数の参照と変更]]
  * [[mariadb:10.4:server-info|MariaDB10.4 サーバ情報の取得]]
  * [[mariadb:10.4:transaction|MariaDB10.4 トランザクション]]

以下「MySQL」は「MariaDB」に読み替えて下さい。

-----

===== サンプルプログラムについて =====

本章では、MySQLの動作を理解するために、サンプルプログラム [[#サンプルプログラム|sqlfile.php]] を作成します。このプログラムは次章以降で利用します。

サンプルプログラムでは MySQLi を使用します。MySQLiは、MySQL(またはMariaDB)データベースとの接続を行うPHP拡張モジュールで、前章で説明した phpMyAdmin でも使用されています。

サンプルプログラムでは以下のデータベースを使用します。作り方は、本編の「[[mariadb:10.4:phpmyadmin#データベースの作成]]」を参照して下さい。

^ ホスト名 | localhost |
^ ポート番号 | 3366|
^ データベース名 | sunlight_db |
^ 照合順序 | utf8_bin |
^ ユーザ名 | sunlight |
^ パスワード | sunlight |

参考リンク
  * https://www.php.net/manual/ja/book.mysqli.php

本章のサンプルプログラムの最新版はGitHubで公開しています
  * https://github.com/y2sunlight/sqlfile

> GitHubでは同等の機能のある最新版を公開しています。

\\

===== mysqli クラスの使い方 =====

ここでは、サンプルプログラム [[#サンプルプログラム|sqlfile.php]] で使用するmysqli クラス(及びmysqli_resultクラス)について簡単に紹介します。

=== mysqli::__construct ===

mysqliオブジェクトを作る [[https://www.php.net/manual/ja/mysqli.construct.php|詳細]]
<code>
mysqli::__construct (
  [ string $host     = ini_get("mysqli.default_host")
  [,string $username = ini_get("mysqli.default_user")
  [,string $passwd   = ini_get("mysqli.default_pw")
  [,string $dbname   = ""
  [,int    $port     = ini_get("mysqli.default_port")
  [,string $socket   = ini_get("mysqli.default_socket")
  ]]]]]])
</code>

=== mysqli::connect===
MySQLサーバーの接続 [[https://www.php.net/manual/ja/mysqli.construct.php|詳細]]
<code>
mysqli::connect (
  [ string $host = ini_get("mysqli.default_host")
  [,string $username = ini_get("mysqli.default_user")
  [,string $passwd = ini_get("mysqli.default_pw")
  [,string $dbname = ""
  [,int $port = ini_get("mysqli.default_port")
  [,string $socket = ini_get("mysqli.default_socket")
  ]]]]]]) : void
</code>

=== mysqli::close===
MySQLサーバーの切断 [[https://www.php.net/manual/ja/mysqli.close.php|詳細]]
<code>
mysqli::close ( void ) : bool
</code>

=== mysqli::query===
クエリを実行する [[https://www.php.net/manual/ja/mysqli.query.php|詳細]]
<code>
mysqli::query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] ) : mixed
</code>

=== mysqli_result::fetch_all===
クエリ結果の全行を取得する [[https://www.php.net/manual/ja/mysqli-result.fetch-all.php|詳細]]
<code>
mysqli_result::fetch_all ([ int $resulttype = MYSQLI_NUM ] ) : mixed
</code>

\\

===== mysqli クラスの使用例 =====

==== データベースとの接続 ====

<file php sample1.php>
<?php
$host = 'localhost';
$user_name = 'sunlight';
$password = 'sunlight';
$database_name = 'sunlight_db';
$port = 3306;

// データベースへ接続
$mysqli = @new mysqli( $host, $user_name, $password, $database_name, $port );

// 接続エラーの確認
if( $mysqli->connect_errno )
{
    die($mysqli->connect_errno . ' : ' . $mysqli->connect_error);
}

echo "Connected successfully : {$host}/{$database_name}";

// 接続解除
$mysqli->close();
</file>

==== テーブルの作成 ====

<file php sample2.php>
<?php
$host = 'localhost';
$user_name = 'sunlight';
$password = 'sunlight';
$database_name = 'sunlight_db';
$port = 3306;

// データベースへ接続
$mysqli = @new mysqli( $host, $user_name, $password, $database_name, $port );

// 接続エラーの確認
if( $mysqli->connect_errno )
{
    die($mysqli->connect_errno . ' : ' . $mysqli->connect_error);
}

// テーブルを作成するSQLを作成
$sql = <<< EOF
CREATE TABLE syain (
  syain_no int NOT NULL AUTO_INCREMENT,
  syain_name varchar(50),
  syain_age int,
  PRIMARY KEY (syain_no)
)
EOF;

// SQL実行
if ($mysqli->query($sql)===FALSE)
{
    echo('Query Error: ' . $mysqli->error);
}

echo "Created Table successfully : {$host}/{$database_name}";

// 接続解除
$mysqli->close();
</file>

==== データの更新 ====

<file php sample3.php>
<?php
$host = 'localhost';
$user_name = 'sunlight';
$password = 'sunlight';
$database_name = 'sunlight_db';
$port = 3306;

// データベースへ接続
$mysqli = @new mysqli( $host, $user_name, $password, $database_name, $port );

// 接続エラーの確認
if( $mysqli->connect_errno )
{
    die($mysqli->connect_errno . ' : ' . $mysqli->connect_error);
}

// 挿入
$sql = "INSERT INTO syain(syain_name, syain_age) VALUES('Suzuki',50),('Yamamoto',30)";
exec_sql($mysqli, $sql);

// 更新
$sql = "UPDATE syain SET syain_age = syain_age + 1";
exec_sql($mysqli, $sql);

// 削除
$sql = "DELETE FROM syain WHERE syain_age > 50";
exec_sql($mysqli, $sql);

// 接続解除
$mysqli->close();

/**
 * クエリSQLの実行
 * @param mysqli $mysqli
 * @param string $sql
 * @return mysqli_result|boolean
 */
function exec_sql(mysqli $mysqli, string $sql)
{
    echo "{$sql}<br>";

    if (($ret = $mysqli->query($sql))===FALSE)
    {
        echo("Query Error: {$mysqli->error}<br><br>");
    }
    else
    {
        echo "Query successfully<br><br>";
    }
    return $ret;
}
</file>

==== データの検索 ====

<file php sample4.php>
<?php
$host = 'localhost';
$user_name = 'sunlight';
$password = 'sunlight';
$database_name = 'sunlight_db';
$port = 3306;

// データベースへ接続
$mysqli = @new mysqli( $host, $user_name, $password, $database_name, $port );

// 接続エラーの確認
if( $mysqli->connect_errno )
{
    die($mysqli->connect_errno . ' : ' . $mysqli->connect_error);
}

// 検索
$sql = "SELECT * FROM syain;";
exec_sql($mysqli, $sql);

// 接続解除
$mysqli->close();

/**
 * クエリSQLの実行
 * @param mysqli $mysqli
 * @param string $sql
 * @return mysqli_result|boolean
 */
function exec_sql(mysqli $mysqli, string $sql)
{
    echo "{$sql}<br>";

    if (($ret = $mysqli->query($sql))===FALSE)
    {
        echo("Query Error: {$mysqli->error}<br><br>");
    }
    else if ($ret===TRUE)
    {
        echo "Query successfully<br><br>";
    }
    else
    {
        $result = $ret->fetch_all(MYSQLI_ASSOC);
        var_dump($result);
    }
    return $ret;
}
</file>

\\

===== サンプルプログラム =====

=== サンプルプログラム( sqlfile.php )の仕様 ===

  * プログラムはSQLスクリプトファイル(テキストファイル)を読み込みます。
  * ファイルにはSQL文、EVAL文、コメントを含み、文はセミコロン('';'')で区切ります。
  * 行コメント( ''-- Comment'' )とブロックコメント( ''/* Comment */'' )が使用できます。
  * 改行を出力したい場合は ''#;'' を行先頭に入力します。
  * SQL文がSELECT文( またはSHOW文 )の場合は検索結果を表示し、非SELECT文の場合はSQLの実行のみ行います。
  * EVAL文はPHPのeval関数で実行します。但し複文の実行はできません。例えば：''EVAL sleep(1);''
  * EVAL文はクォート処理をしていないので、''EVAL echo 'Hellow;';'' などは途中で文が区切られエラーになります。
  * 検索結果及び実行結果はHTMLで出力します。
  * SQLスクリプトファイルはURLのクエリ文字列で指定します。\\ 例えば：''<nowiki>http://localhost/mysql/sqlfile.php?f=sample.sql</nowiki>''
  * クライアント文字セットをUTF8に設定することができます(sオプション)。

=== サンプルプログラムを使った例 ===
ブラウザからサンプルプログラム(sqlfile.php)を実行します。

<code>
http://localhost/mysql/sqlfile.php?f=sample.sql&s=1
</code>

<file sql sample.sql>
-- テーブル作成
DROP TABLE IF EXISTS syain;
CREATE TABLE syain (
  syain_no int(10) NOT NULL,
  syain_name varchar(50),
  bumon_no int(10),
  PRIMARY KEY (syain_no)
);

-- テーブルにデータを挿入
INSERT INTO syain VALUES(1,'Suzuki',3);
INSERT INTO syain VALUES(2,'Yamamoto',1);
INSERT INTO syain VALUES(3,'Tanaka',2);

-- テーブルの検索
SELECT * FROM syain;
</file>

実行結果
[{{:mariadb:10.4:sqlfile01.png?nolink|}}] //

// 

=== サンプルプログラム( sqlfile.php )のソース ===

<file php sqlfile.php>
<?php
/**
 * Configuration
 */
// データベース接続先
define("HOST",          "localhost");   // ホスト名
define("USER_NAME",     "sunlight");    // ユーザ名
define("PASSWORD",      "sunlight");    // パスワード
define("DATABASE_NAME", "sunlight_db"); // データベース名
define("PORT",          3306);          // ポート

// SQLファイルの保存先
define("SQL_PATH", dirname(__FILE__) . "/sql");

/**
 * Main
 */
// SQスクリプト取得
if (isset($_REQUEST['f']))
{
    $sql_text = ReadScriptFile(SQL_PATH . "/{$_REQUEST['f']}");
    $sql_array = GetSqlText($sql_text);
}
else
{
    die('Illegal call');
}

// データベースへの接続
$mysqli = @new mysqli( HOST, USER_NAME, PASSWORD, DATABASE_NAME, PORT );
if( $mysqli->connect_errno )
{
    die($mysqli->connect_errno . ' : ' . $mysqli->connect_error);
}

// 文字セットの指定
if (isset($_REQUEST['s']))
{
    $mysqli->query('SET NAMES utf8');
}

// レスポンス処理
HTML_Begin();
DoSqlScript( $mysqli, $sql_array );
HTML_End();

// データベースの切断
$mysqli->close();

/**
 * SQLスクリプトの実行
 * @param mysqli $mysqli　MySQLiオブジェクト
 * @param string[] $sql_text SQL文の配列
 */
function DoSqlScript( mysqli $mysqli, array $sql_text )
{
    if (empty($sql_text)) return;

    $sqltime = 0;
    foreach( $sql_text as $sql )
    {
        if ( !$sql || $sql[0]=='#' ) $sql=''; // 先頭の#は改行

        // SQL文表示
        print HTML_Escape(strlen($sql)<80 ? $sql : substr($sql,0,80)." ...");
        print "<br />\n";
        ob_flush(); flush();

        if ( !$sql ) continue; # 空行

        // 特別なEVAL文の実行
        if ( preg_match( "/^eval\s+(.+)/i", $sql, $reg ) )
        {
            eval("{$reg[1]};");
            continue;
        }

        // SELECT文と非SELECT文で処理を分ける
        $time1 =  microtime_as_float();
        if (preg_match("/^(select|show)\s/i", $sql))
        {
            if (!DoSelect($mysqli, $sql)) break;
        }
        else
        {
            $res = $mysqli->query($sql);
            if($res===false)
            {
                SqlError("DoSqlScript", $mysqli->error);
                break;
            }
        }
        $time2 =  microtime_as_float();
        $sqltime += ($time2-$time1);

        ob_flush(); flush();
    }
    print "exec time: ".sprintf('%01.03f', $sqltime)." [sec]<br />\n";
}

/**
 * SELECT文の実行
 * @param mysqli $mysqli　MySQLiオブジェクト
 * @param string $sql SQL文
 * @return boolean 成功でTrueを返す
 */
function DoSelect( mysqli $mysqli, string $sql )
{
    // 検索実行
    $res = $mysqli->query($sql);
    if($res===false)
    {
        SqlError("DoSelect", $mysqli->error);
        return false;
    }

    // カラム名の表示
    if ($rows = $res->fetch_all(MYSQLI_ASSOC))
    {
        print "<table>\n";
        print "<tr>\n";
        foreach( $rows[0] as $key => $value )
        {
            print "<th>$key</th>\n";
        }
        print "</tr>\n";
    }
    else
    {
        print "No results found.<br />\n";
        return true;
    }

    // 行の表示
    foreach( $rows as $row )
    {
        print "<tr>\n";
        foreach( $row as $value )
        {
            $value = HTML_Escape($value);
            print "<td>$value</td>\n";
        }
        print "</tr>\n";
    }

    print "</table><br />\n";
    return true;
}

/**
 * SQLテキストの取得
 * @param string $text SQLテキスト
 * @return string[] SQL文の配列
 */
function GetSqlText( string $text )
{
    $text = str_replace(["\r\n","\r"], "\n", $text);

    // Remove comment
    $text = preg_replace("/\/\*.*?\*\//s", '', $text);
    $text = preg_replace("/--.*?$/m", '', $text);

    // Split SQL text
    $sql = preg_split("/\s*;\s*/", $text);
    array_walk($sql, function(&$item){
        $item = trim($item);
    });
    $sql = array_filter($sql, function($val){
        return !empty(trim($val));
    });
    return $sql;
}

/**
 * SQLスクリプトファイルの読み込み
 * @param string $sql_file ファイル名
 * @return string SQLテキスト
 */
function ReadScriptFile( string $sql_file )
{
    if (!preg_match("/^[^\.].*\.sql/",$sql_file))
    {
        SqlError("URL","Illegal script file name.");
    }

    if(($text=@file_get_contents($sql_file))===false)
    {
        SqlError("open","Can't file open $sql_file.");
    }
    return $text;
}

/**
 *　マイクロ秒取得
 * @return float
 */
function microtime_as_float()
{
    list($usec, $sec) = explode(' ', microtime());
    return ((float)$sec + (float)$usec);
}

/**
 * HTML エスケープ
 * @param string $str
 * @return string
 */
function HTML_Escape( string $str )
{
    return htmlentities( $str, ENT_QUOTES, "utf-8" );
}

/**
 * HTML エスケープ(FORM用)
 * @param string $str
 * @return string
 */
function FORM_Escape( string $str )
{
    return htmlspecialchars( $str, ENT_QUOTES, "utf-8" );
}

/**
 * HTMLの開始処理
 */
function HTML_Begin()
{
    header('Content-type: text/html; charset=utf8');
    print <<<EOF
<html>
<head>
<style type="text/css">
body  {font:10pt monospace;}
table {font:10pt monospace;border-collapse:collapse;}
th,td {border:solid 1px #000000;}
th    {background-color:#eeeeee;}
</style>
</head>
<body>\n
EOF;
}

/**
 * HTMLの終了処理
 */
function HTML_End(){
    print "</body></html>\n";
}

/**
 * エラーメッセージ表示
 * @param string $title タイトル
 * @param string $msg メッセージ
 */
function SqlError($title, $msg)
{
    print "<span style='color:red'>[Error]$title: $msg</span><br />\n";
}
</file>

\\