Excelデータを二次元配列に読み込む

今 成田空港に向かう NEXの中でテストを走らせています もちろん Objectとして Excelの行や列を扱うのも良いのですが、MySQL側はあくまでも二次元配列という概念ですので、今後のために2次元配列に変換します その部分を作成しました

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

use PhpOffice\ PhpSpreadsheet\ Spreadsheet;
use PhpOffice\ PhpSpreadsheet\ Writer\ Xlsx as Writer;
use PhpOffice\ PhpSpreadsheet\ Reader\ Xlsx as Reader;
use PhpOffice\ PhpSpreadsheet\ Cell\ Coordinate;

$reader = new Reader();
//$reader->setInputEncoding('SJIS');
$spreadsheet = $reader->load( 'poster2018.xlsx' );
//$reader->setInputEncoding('SJIS');
$sheet = $spreadsheet->getActiveSheet();

$lines = array();
$max_row = 0;	// 今rowの最大数値
$max_column = 0;	// 	columnの最大値
$nlines = array(); // Columnを数字にした二次元配列

$row_counter = 0;
$column_counter = 0;
foreach($sheet->getRowIterator() as $row) {
  foreach($sheet->getColumnIterator() as $column) {
    $lines[$row_counter][$column_counter] = $sheet->getCell( $column->getColumnIndex() . $row->getRowIndex() )->getValue();
    // これで$linesという2次元配列に Excel dataが集積された
    // これからは $lines[n][m]のような二次元配列として扱うことが可能となる
    $column_counter++;
  }
  $row_counter++;
}
$max_row = $row_counter;
$max_column = $column_counter;

ここでキモとなるのが、Excelではそれぞれのセル座長は A1より始まる表記となっていることです 通常の二次元配列では[0][0]より始まる配列ですので、この変換が必要となります

また Excelファイルを扱う時何時も問題となるのが、文字コードです Excelでは DOSからの伝統で日本語文字コードは Shift-JISであり、現在のプログラミングで標準的な UTF-8とは合いませんし、文字化けが起こります

そこで、PhpSpreadSheetにも

$reader->setInputEncoding('SJIS');

というメソッドが定義されているのですが、最近の Excelで改善されたのか、何しろそのようなメソッドを使用せずに自動的に UTF-8に変換されるようです ですからこのメソッドは不要でした

この10日間 悩み抜いたバグがようやく解決

さて、https://www.kamakuralive.net/ のWeb programを作る段階で、コメコメ部門のポスター発表部分がまだ実装できていませんでした その理由は唯一つ、僕がズルというか楽をしたいからだったのです

既にポスター演題が 20集まっており、それをコメコメ委員の方々が Excel形式で集計してくださっていました しかし、それを Web databaseである MySQLデータベースに読み込まねばなりません 実は既に昨年、 Excel (これはShift-J文字コード) の xlsx形式-> Text形式 (.txt)に変換 -> 文字コードを UTF-8に変換 -> .txtを MySQL databaseに読み込み という部分は作成していました

まあ作成といっても、foreachでループを回して読み込むだけなのですが・・・

今回は折角ですので、自分の勉強のためにもPHPSpreadSheetという Excelファイルを PHPで読み込むライブラリを用いることにしたのです

その部分は案外簡単に Excel file読み込みに成功したのですが、肝腎の MySQL databaseに書き込む SQL文がエラーが出るのです その部分のバグとりが全くうまくいかず 10日間以上を無駄に過ごしてしまいました いや 決して人生において無駄というのは無いでしょう そう思わねばやってらんないよっ

結局その部分を簡略化して出しましょう

try {
  // MySQLサーバへ接続
  $pdo = new PDO( "mysql:host=$db_host;dbname=$db_name_sessions;charset=utf8", $db_user, $db_password );
  // 注意: 不要なspaceを挿入すると' $db_host'のようにみなされ、エラーとなる
} catch ( PDOException $e ) {
  die( $e->getMessage() );
}

for ( $rowNo = 1; $rowNo < 24; $rowNo++ ) {
  $stmt = $pdo->prepare( "SELECT COUNT(*) FROM `doctor_tbls` WHERE `email` = :email;" );
  $stmt->bindValue( ":email", $nnlines[ $rowNo ][ 12 ], PDO::PARAM_STR );
  $stmt->execute();
  $count_same_email = $stmt->fetchColumn();

  if ( $count_same_email < 1 ) { // 既にこのメルアドは登録されているのでスキップ
     $stmt_dr = $pdo->prepare( "INSERT INTO `doctor_tbls` (`english_sirname`, `english_firstname`, `is_male`, `email`, `changed`) VALUES (:enlish_sirname, :enlish_firstname, :is_male, :email, :changed);" ); 
    $stmt_dr->bindValue( ":english_sirname", $nnlines[ $rowNo ][ 1 ], PDO::PARAM_STR );
    $stmt_dr->bindValue( ":english_firstname", $nnlines[ $rowNo ][ 2 ], PDO::PARAM_STR );
    
    if ( $nnlines[ $rowNo ][ 3 ] === 'M' ) {
      $sex = 1;
    } else {
      $sex = 0;
    }
    
    $stmt_dr->bindValue( ":is_male", $sex, PDO::PARAM_INT );
    $stmt_dr->bindValue( ":email", $nnlines[ $rowNo ][ 12 ], PDO::PARAM_STR );
    $stmt_dr->bindValue(":changed", date('Y-m-d H:i:s'), PDO::PARAM_STR);
    
    $flag = $stmt_dr->execute();
    
    if ( !$flag ) {
      echo "<h2 style='color:red;'>Error</h2>";
      $infor = $stmt_dr->errorInfo();
      print_r($infor);
      exit( $infor[ 2 ] );
    }
    
  }
}

これがエラーで書き込めません、色々と試みたのがうまく行きませんので、最後に  print_r($infor)の一行を加えて、SQLエラー出力を行いました

そうすると MySQL error code

SQLSTATE[HY093]

というのが出てくるのです これは検索すると パラメータの数が合わないなのですが、目を更にして見てみてもそんな訳はありません しかし、この 10日間の苦難の末ようやく発見しました

 

VALUES (:enlish_sirname, :enlish_firstname, :is_male, :email, :changed)

この部分で english_ とすべきを enlishとしているのですね このためパラメータの数が合わないため SQL errorとなっていたのです なんだかなあ パカみたい 10日間損した気分 でも自分が招いたものだから仕方ありません

本日はこれから 成田空港に向かいます このエラーを解決したのでポスター・セッション部分のプログラム完成できますね

SELECTで選択した列数を PDOで PHPに渡すには

これはしばしば行う間違いなのですが、気が付かないと結構しつこいバグの原因となりますね

$sql = "SELECT COUNT(*) FROM `dr_tbl` WHERE `email` = :email AND `conf_id` = :conf_id;";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":email", $_SESSION['email'], PDO::PARAM_STR);
$stmt->bindValue(":conf_id", $_SESSION['conf_id'], PDO::PARAM_INT);
$stmt->execute();
    //$rowCount = $stmt->rowCount();
    //echo 'Executed!'.$rowCount.'<br>';
if ($stmt->fetchColumn() > 0) {	// 既に登録されているので弾く
        // この時 fetchColumn()が戻す値は SQL文で得られた count(*)ということになります
        // この結果 SELECT文から正しい件数を知ることができます

 

二日間バグで苦しみ

どうにも納得行かなかったのです それは、http://www.tri-international.orgのサイトをサーバー移行した時に始まったのです

そもそもこちらの都合でサーバー移行したのではありません サーバー運営会社の意向に沿って移行したのです これまでずっと使用してきた Rental Serverは FirstServerだったのですが、それが時代の移り変わりと共に、Zenlogicという次世代のサーバー環境になったのです 正直僕はここら Cloudとか詳しくないので何が違うのか良く分かりません

この移行の時に、これまでの全データも移行したのですが、問題は MySQL Database Server Dataだったのです これも移行したのですが、それと共に phpMyadminという MySQLサーバーデータ管理の秀逸のソフトがそのままでは作動しなくなり、改めてインストール必要でした そしてこの時に、port 3307にインストールされたのです これが問題だったのです

そもそも現在はMySQL5.1が port 3307で作動し、MySQL5.0が port 3306で作動している、という変な状況が続いているのです だいたい port番号なんて普段意識せずに使っているのですよっ

そしてサーバー移行前のデータは MySQL5.0から MySQL5.1に自動的にコピーされ、一見すると同じデータが2つの DB serverにあるのです

さて、PHPプログラムでは Internetアクセスした時には、

$db_host = '127.0.0.1';
//$db_port = '3307';//コメントアウトしているので port3306のまま
$db_user = '**A';
$db_password = '**B';

という風に 普段は port 3306の MySQL databaseに接続しているのです しかし管理ソフトの phpMyadminは port 3307で接続していたのです 当初は同じDBがコピーされていたので、phpMyadminでいくらデータをいらっても これは 3307接続ですから、PHP programからはその変更は見えません

結果的にわけのわからないことになってしまったのです この portの問題に気づくまでには丸一日かかりました そして現在は $db_port = ‘3307’; この一文を活かしているので、全てが port 3307で作動している MySQLでプログラム全体が動いているのです

いやあこのバグは判明にずいぶんかかりました しかし、僕の頭脳が未だ明晰であることが証明されてある意味嬉しいですね

PHP 7.1への更新成功

Zenlogicにサーバーを移行しましたが、その結果、PHPも5.3から7.1に upgradeすることが可能となりました

しかし、これをしたところ、先日記載したように画面が真っ白となる不具合が発生し、慌てて 5.3に戻したのです

そして行ったことは WordPressのデータを全て backupしました そして、それをhttps://の領域に移し、そこで再度 WordPressをインストールしたのですが、残念ながら管理者ID/PWがうまく移行できず、管理者権限で入れなくなってしまったのです

これには困りました

そこで、以前の PHP5.3での WordPressに戻し、今度 Pluginを停止し、そして再び PHP7.1にしましたところ、Wordpressが正常に作動していることを確認しました

ということで、現在は www.kamakuraheart.orgでWordpress/PHP7.1が作動しています 何故PHP7.1に拘ったかと言うと (1)速度が PHP5.3より五倍程度早くなることが確認されている (2)何しろ Laravel 5.5では PHP >= 7.0が必須なので

ということでした 目出度しです

昨日より札幌

昨日より札幌に入っています 昨日朝 札幌には雪が降りましたが、流石にこの季節 街に積もっていた雪は溶けています 昨日はあまり元気が無く、早々と寝たのですが、01:00AMには覚醒し、それからずっとプログラム書いていました

Web上のプログラムで、TRI Maniac Confenceなどの登録サイトです。相当に securityに気を使いながら書いています。いわゆる Zendや、CakePHPなどのPHP Frameworkは使用せずに、最初からコーディングしています どうもFrameworkの中のいろいろな取り決めが嫌いで、Frameworkには馴染めません もっとも、jQueryは違います、これはFrameworkというより Javascript Libraryと言うべきかも知れませんが、これは随分と使っています

あれ程なかなか転化できなかった PDO (PHP Data Object)の世界ですが、今ではすっかり馴染んで使用しています 何しろ PDOを用いれば、SQL Injection攻撃を完璧に跳ね返すことができるので、とても貴重です 今回も MySQL Databaseとの間で SQL文を用いてデータのやり取りが何回も何回もあるのですが、その全てで PDOを用いています。これは僕にしてみれば相当な進歩です

以前PDOに馴染めなかったのは、SQL文のデバッグがしづらかったからです

  print_r($pdo->errorInfo());
  exit;

とすることにより、エラーが起きているかどうかは分かるのですが、SQL文の何処にエラーがあるのか、それがなかなかわかりづらかったので敬遠していました しかし、ローカルで立ち上げたデータベースに対して phpMyAdminを用いて SQL文を発行して、デバッグする方法を思いついたので、この壁も乗り越えることができました

という訳で今は PDOを使いまくっています

PHP セッションのタイムアウト

昨日、スレンダークラブ神戸の Case Competition Awardの時に、後ろに座られた清水先生とお話する機会がありました

その中で、ログインした後、セッションが残り、ブラウザを一旦閉じるか、明示的にログアウトしないと、そのままログイン状態が継続する、とのお話がありました

もともと、そのように作っていたのですが、そのご指摘にそれも問題かも と、思うようになりました そこで、セッション・タイムアウトを組み込むことにしました

以前もこの件は検討したことがあり、その時に下調べし、「まあ簡単に実装できる」との確信があったのですが、やりだすとなかなか思うようにいきません

一番簡単にできるのは、php.iniの部分でsession timeoutを設定することですが、レンタル・サーバーでそんなことは不可能です

次の手段は、.htaccessファイルの中でsession timeoutを設定することですが、現在の FirstServerでそれが可能かどうか調べるのがめんどくさいのでした

次に考えられるのが、PHPの命令文で、ini_set_session_timeout()関数を発行することですが、それをしましたが、うまく行かないのです、もっともテストしたのは XAMPPですが・・・

それで、思い切って考えを根本から変えました

それは、徹底的にセッションを使いまわす、ということです

index.phpの最初に

 session_start();
 $_SESSION['strat'] = time();

このようにして、ページ遷移の部分の筆頭で、$_SESSION[‘start’}の書き換えおよび、経過時間のチェックを行い、時間が経過していれば、logout.phpに強制的に飛ばす、というものです
これでバッチリです

phpMyBackupProによるSQL backup dataの文字化け

ついに見つけたツール phpMyBackupProは非常に優れたものです。完璧に SQLでバック・アップが可能なのです。本当に素晴らしいと思います。しかも、フリーでダウンロードできるのです。

ただ、日本語には対応していません。このため、backupしたデータの日本語が文字化けしてしまい、そのままでは使い物になりません。データベースには UTF-8の文字コードでデータが蓄積されているので、phpMyBackupProそのもののデータ読み書きを UTF-8にすれば良いわけです。少しプログラムを見ていくと、どうやら Latinという文字コードがdefaultとして設定されているのです。まあ、どうやらイギリスで開発されているプログラムなので、かの国ではそれが標準なのでしょう。しかし、日本語対応させようと思えば、SJISあるいはEUC-Jという文字コード、そして現在の流れとしては UTF-8に対応せねばなりません。

そもそも Latinという文字コードでは一文字が 1Byte = 8 bitsであらわされます。これに対して、UTF-8では基本的に 3Bytes = 24 bitsで一文字があらわされます。日本語では、文字の数が何万とあるので、情報量として3Bytes必要なのです。

phpMyBackupProで同様の悩みをかかえて、それを解決された方がおられるのでは? と探したところ、やはりありましたね。ここ(A)です

実は、もっと有望な解説も見つけました。それはここ(B)です。このサイトでは、何と日本語化した phpMyBackupProをダウンロードできるようにしていました。しかし、その versionが 2.1とあり、現在の version 2.3よりも古いのが少し気になります。

そこで、(A)を参考に、functions.inc.phpをエディタで開いて、その 636行目に以下の文を発見しました

        // select db
        @mysql_select_db($db);

ここに 2行を追加しました:

        // select db
        @mysql_select_db($db);
		$sql = "SET NAMES utf8";		// utf8に設定する
		$res= mysql_db_query($db,"$sql");

この簡単な追加により、見事に日本語データが文字化けせずにきちんと backupされ、downloadできるようになりました。もっとも、未だテスト段階ですので、このパソコンのローカルに設定した偽装インターネット環境での確認です。

そのようにしてダウンロードした SQL文を用いて、サーバー・データが破壊されても、再構築が完璧にできるか否かを検証したところ、問題がおこりました。

きちんと SQL文では日本語が文字化けせずにダウンロードできているのに、何故か SQL文を実行して再構築すると、データベース上のデータが文字化けするのです。これは変です。 SQL文を読んでもきちんと

DEFAULT CHARSET=utf8

と設定されているのです。そこで吐き出された SQL文を最初から見ると何とその最初に

# http://www.phpMyBackupPro.net

### used character set: Latin ###
set names Latin;

というのを発見しました。折角データをutf8で設定しているのに、肝心の書き込み段階で Latinに設定されているのです。これが原因でした。
それで、この部分の Latin -> utf8に変更したところ、見事に完全データ復旧ができるようになったのです。素晴らしいです。もう少しテストしてからサーバーに実際に uploadしてみます。何はともあれ、今度のサーバー障害により色々なことを学びました。失敗から多くを学び、発展させることができました。

Ajax

Ajaxを用いて NAUSICA AMI試験の監査ツールを実装しました。Ajaxの仕組みなんてすっかり忘れていたのですが、プログラム書いている内にすぐに回復しました。やはりAjaxを用いると従来の Webページからは信じられないような画面の書き換えが行われ、プログラムを書いた甲斐があります。

その骨子は三つのパートです

  1. 主要画面
    1. ここではデータベースから受け取ったデータを cssを用いて見てくれを整えて出力する
    2. さらに、データを受信するためのボタンをつける
  2. データを受け取る Javascript (Ajax)
    1. ここがAjaxの根幹です
    2. ブラウザの Document Object Model (DOM)を用いてダイナミックに画面を書き換えます
  3. 裏で動くデータ加工プログラム
    1. データベース MySQLに対して SQLを発行してデータを得ます
    2. それを一定の形に加工します
    3. 加工の仕方としては、 JSONや HTMLを用いるのが一般的ですが、 TEXTでも良いのです 今回は HTMLを用いました

    忘れないようにおいおいプログラムを書き記します

サーバー復旧作業その後

何とかサーバー復旧しました。ひどい話です。突然全てのデータが完全に消失したのです。全てのデータとは

  1. ホームベージのデータ これにはhtml, css, Javascript, jQuery, cgiなどのデータやプログラムが含まれます
  2. 膨大なプログラム群 これにはPHP, Javascript, jQuery, SQLなどで書かれたプログラムがあります
  3. データベースに蓄えられたデータそのもの これはDatabase MySQLの中に格納されたデータです
  4. サーバーの各種設定 これにはデータベース接続、メール、FTPなどのユーザーネームとパスワードがあります

これら全てが一瞬にして無くなったのです。まずは4番から設定に入りました。そして、次に MySQLを開始させました。MySQLといえどもLinux Server上で走るプログラムの一つですのでまず走らせねばなりません。一旦走らせたらば、永久に走り続けるのです。そして、その MySQL上にバックアップしてあった 2012-05-23までのデータを含んだ SQL文を転送し、そしてSQLを実行しました。これでひとまずデータベースがその時点まで復旧しました。3の部分の復旧です。

次に行ったのは、2番です。膨大な主にPHPで書かれたプログラムを uploadしました。もともとサーバー側では PHPの実行プログラム (インタープリーター)が走っていますので、それでもう書いたプログラムが走り始めました。

最後にしたのは 1番です。これでブラウザからアクセス可能となりました。

ここまでの作業は正直それ程時間がかかる作業ではなく、主に uploadにかかる時間に依存します。つまり回線速度に依存しました。だいたい 1時間ぐらいでした。

問題はそれからでした。5-24から6-20までに登録されたデータがあります。この中で重要なものは、無作為割付を含んだ症例です。幸いプログラムが、登録があれば、あるメルアドに「登録がありました」という情報を送るようにしてありました。もちろん個人を同定できる情報は含まれません。このメールは消去せず蓄積していましたので、そのデータを用いて症例登録をすることにしました。

ただ、このためにはデータベースの色々なテーブルを参照しながらデータを作成せねばなりません。とても手作業でできる仕事ではありませんので、そのように PHPでプログラムを書かねばなりませんでした。時差ボケの中、しかもインターネット接続が不安定の中、大変な作業であり、効率の悪いものでしたので、これに 10時間ぐらいがかかってしまったのです。

そしてようやく出来る範囲最大限のデータ復活を成し遂げました。次に行ったのは、それらのデータが復活したことを、登録して頂いた先生方にメールで知らせることでした。これも手作業でできる仕事ではありませんので、プログラムを書かねばなりませんでした。自動的にメールを発信するプログラムは結構難しいのです。それでも2時間ぐらいで完成し、走らせました。

もう少しチューニングが必要かも知れません。何れにしても大変な経験をしました。バックアップを毎日のように取る必要があるかも知れませんね。