MariaDB10.4 PHPサンプルプログラム

phpMyAdmin 4.9.0.1 (MariaDB 10.4.6)

— y2sunlight 2019-10-10

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

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

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

サンプルプログラムでは MySQLi を使用します。MySQLiは、MySQL(またはMariaDB)データベースとの接続を行うPHP拡張モジュールで、前章で説明した 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オブジェクトを作る詳細

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")
  ]]]]]])

mysqli::connect

MySQLサーバーの接続詳細

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

mysqli::close

MySQLサーバーの切断詳細

mysqli::close ( void ) : bool

mysqli::query

クエリを実行する詳細

mysqli::query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] ) : mixed

mysqli_result::fetch_all

クエリ結果の全行を取得する詳細

mysqli_result::fetch_all ([ int $resulttype = MYSQLI_NUM ] ) : mixed

mysqli クラスの使用例

データベースとの接続

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();

テーブルの作成

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();

データの更新

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;
}

データの検索

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;
}

サンプルプログラム

サンプルプログラム( 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のクエリ文字列で指定します。
例えば：http://localhost/mysql/sqlfile.php?f=sample.sql
クライアント文字セットをUTF8に設定することができます(sオプション)。

サンプルプログラムを使った例

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

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

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;

実行結果

サンプルプログラム( sqlfile.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";
}

目次