PakuReserve R&D Laboratory

DBの設計上、NULLが入っている場合の集計の際にちょっとハマりました。

下記のような注文テーブル(orders)があったとします。

DESC orders;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| total     | int(11) | YES  |     | NULL    |                |
| sub_total | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

SELECT * FROM orders;
+----+-------+-----------+
| id | total | sub_total |
+----+-------+-----------+
|  1 |  1050 |       500 |
|  2 |  1575 |      NULL |
+----+-------+-----------+

各注文のtotalからsub_totalを差し引いた金額(エイリアスをcostとする)を取得したい時、

SELECT (total - sub_total) AS cost FROM orders;

このようにすると、NULLの場合は0として計算してくれるだろうと思っていたら甘かったです。
id: 1のcostは550、id: 2のcostはNULLとなります。

SELECT (IFNULL(total,0) - IFNULL(sub_total,0))
AS cost FROM orders;

NULLが入っている可能性がある場合は、このようにIFNULLを使ってやればNULLならば0として計算してくれます。

IFNULL(expr1,expr2)

expr1がNULLでない場合はexpr1を返し、それ以外の場合はexpr2を返します。

チョコボール MySQL, データベース

・フォームから送信された日付をDBにINSERT、UPDATEできる形にするための整形
・DBからSELECTで引っ張ってきたDATETIME型の日付の整形

上記を例として日付の整形ってよく出食わします。
sprintf、substr、explode、正規表現を使う等、いろんなやり方があると思いますが…
自分が使っている方法をまとめてみます。

※例 DATETIME型の文字列を整形して表示

$time = '2008-07-10 21:02:23';
$data = sscanf($time, '%d-%d-%d %d:%d:%d');

sscanf()はprintf()の入力版で、文字列を指定したフォーマットに基づいて配列で抜き出します。
(フォーマットについてはprintf()のマニュアル参照)

$dataは下記のようになります

Array
(
    [0] => 2008
    [1] => 7
    [2] => 10
    [3] => 21
    [4] => 2
    [5] => 23
)

一発で変数に入れたい場合はlist()関数を使いましょう。

list($year, $month, $day, $hour, $minute, $second) =
	sscanf($time, '%d-%d-%d %d:%d:%d');
echo $year.'年'.$month.'月'.$day.'日 '.$hour.'時'.$minute.'分'.$second.'秒';

//【結果】2008年7月10日 21時2分23秒

こんな感じにすれば取り敢えず整形して表示できますが、一桁の月、日はゼロで穴埋めしたかったりする場合もあったりするので、ここは関数sprintf()を使って整形します。

echo sprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	$year, $month, $day, $hour, $minute, $second);

//【結果】2008年07月10日 21時02分23秒

sprintf()の引数では配列を指定できませんが、vsprintf()を使うと配列を指定でき、

list($year, $month, $day, $hour, $minute, $second) =
	sscanf($time, '%d-%d-%d %d:%d:%d');
echo sprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	$year, $month, $day, $hour, $minute, $second);

の二行を一行でやっつけちゃえます。

echo vsprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	sscanf($time, '%d-%d-%d %d:%d:%d'));

関数やメソッドで用意しておくと使い回しできて良いかと思われます。

チョコボール MySQL, PHP, Tips, データベース

チョコボールです。
MySQLにてSELECT時に値の形を変えて引っ張ってくる方法。
僕はステータスが数字で管理されているようなDB(※例: 男性が「1」、女性が「2」等)をfetchしてきて表示する時等に使ってます。

取り敢えずSELECTで引っ張ってきてプログラム側で加工してやればいいんですが、CASE式を使えば加工の必要が無いので結構楽です。

下記のようなmemberテーブルがあったとします。

+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | 田中 |      0 |
|  2 | 辻川 |      1 |
|  3 | 鈴木 |      0 |
|  4 | 山本 |      1 |
|  5 | 西田 |      0 |
|  6 | 上村 |      2 |
|  7 | 星野 |      0 |
|  8 | 宮村 |      3 |
|  9 | 川田 |      1 |
| 10 | 山田 |      1 |
+----+------+--------+

ステータス
0 → 通常会員
1 → 有料会員
2 → 退会済会員

そのまま

SELECT * FROM member

としてfetchすると

Arrray
(
	[0] => Array
		(
			[id] => 1
			[name] => 田中
			[type] => 0
		)

	[1] => Array
		(
			[id] => 2
			[name] => 辻川
			[type] => 1
		)

	[2] => Array
		(
			[id] => 3
			[name] => 鈴木
			[type] => 0
		)
			・
			・
			・

のような感じで取得できますが、下記のようなSQLを組むことでプログラム側で加工無しに一発で引っ張ってくることが出来ます。

SELECT id, name,
	CASE
		WHEN type = '0' THEN '通常会員'
		WHEN type = '1' THEN '有料会員'
		WHEN type = '2' THEN '退会済会員'
		ELSE '不明'
	END type
FROM member

 Array
(
	[0] => Array
		(
			[id] => 1
			[name] => 田中
			[type] => 通常会員
		)

	[1] => Array
		(
			[id] => 2
			[name] => 辻川
			[type] => 有料会員
		)

	[2] => Array
		(
			[id] => 3
			[name] => 鈴木
			[type] => 通常会員
		)
			・
			・
			・

CASE式は他にもいろいろ使えるのでまた紹介します！

チョコボール MySQL, PHP

本日投稿2個目、チョコボールです。

DBから引っ張ってきた文字列をセレクトボックスで表示させる箇所で、
長ったらしい文字列があるせいでデザインが崩れてしまっている！という指摘を受けました。

指定した幅以上は「…」で表示するという、よくある表示方法で対処しようと思い、
substr関数等であれこれ試していましたが、mb_strimwidthという関数を使うといい感じでした。

mb_strimwidth(【文字列】,【開始位置のオフセット(最初の文字は0)】,【幅】,
【丸めた後にその文字列の最後に追加される文字列】,【文字エンコーディング】)

表示部分で『この横幅以下に抑えたい』という時に使えます。

文字コードに依存せず、第3引数の数値は半角を幅1、全角を幅2とします。
文字数ではありません。
また、第4引数も含めた数値になります。

$str = "あいうえおかきくけこ";
$str2 = "This is a pen.";

echo mb_strimwidth($str, 0, 10, '…', 'UTF-8');
echo '<br />';
echo mb_strimwidth($str2, 0, 10, '…', 'UTF-8');

上記の結果は下記のようになります。

あいうえ…
This is a…

全角の文字の場合でも半角の文字の場合でもだいたい横幅が揃うはずです。

smarty側で書く場合はこんな感じです。

{$str|mb_strimwidth:0:10:'…':'UTF-8'|escape}

チョコボール PHP

チョコボールです。

PHP言語での文字コードの変換といえば『mb_convert_encoding』関数。

mb_convert_encoding(【文字列】,【変換後の文字コード】,【変換前の文字コード】);

返り値は変換後の文字列です。

以前書いた記事、CSVファイル出力関数の時にも使いましたが、出力する直前に、

$str = mb_convert_encoding($str, 'SJIS', 'UTF-8');

のように使って文字列の文字コードを変換したりするわけです。

上記の場合、UTF-8からShift JISに変換です。

第3引数→第2引数

第3引数を指定しなかった場合は、内部文字エンコーディング
(php.ini の mbstring.internal_encoding の値)を使用することになります。

配列中の文字列の文字コードを一気に変換したい時、
foreach等でループさせてmb_convert_encodingかけて…
とかしなくてもmb_convert_variables関数で一行でいけます。

mb_convert_variables(【変換後の文字コード】,【変換前の文字コード】, 【文字列、配列、オブジェクト】);

変数はカンマで区切って複数指定できます。

第2引数→第1引数

mb_convert_encodingと違い、返り値は変換前の文字エンコーディングを返します。
失敗した場合はFALSEを返します。

$ret = mb_convert_variables('SJIS', 'UTF-8', $array);

上記の場合、返り値$retはSJISになります。

単に変換を掛けたいだけの場合は

mb_convert_variables('SJIS', 'UTF-8', $array);

の一行で。

一見似ている関数ですが、引数の順番の違い、返り値の違いに注意が必要です。

チョコボール PHP

またまたチョコボールです。

趣味で運営してるサイトはロリポップサーバーを利用してますが、
割と最近PHPがバージョンアップしてPHP5に切り替え可能になりました。

自動的にPHP5になるのかと思ってひやひやしていましたが、管理画面から切り替え可能という形でした。
そりゃそうですよね。。

で、近日、切り替え作業をしようと思ってます。

仕事での開発環境はPHP5なので当たり前のように使っていたfile_put_contents関数ですが、PHP4で使えなくてマジっすかと思った記憶があります。
PHP4ではfopenしてfwriteしてfcloseする必要があります。

ということでfile_put_contents関数等を使ったデバッグ方法を。。

コーディング中、最も簡単なデバッグ方法は

 echo $data;

とか

 echo "<pre>";
print_r($data);
echo "</pre>";

とか

 echo "<pre>";
var_dump($data);
echo "</pre>";

ですかね。。

それ以降の処理をストップしてデバッグしたければ直後に

exit;

なんかを書けばいいと思います。

ただ、画面では表示されない処理のデバッグをしたい時やデバッグ内容やエラーログをテキストに記録したりしたい時があるので下記のような関数を作って使ってます。

■画面にデバッグ表示

function _debug($str, $showHtml = false){
	echo "<pre>\n";
	$ret = print_r($str, true);
	if($showHtml){
		$ret = str_replace('< ', '&lt;', '&gt;', $ret));
	}
	echo $ret."\n";
	echo "</pre>\n";
}

$strに<hr />などのHTML的に特殊な意味をもつ文字列が入っていた場合、デバッグ内容がうまく表示されないので、
そのような時は第二引数にtrueを入れてやると特殊文字をHTMLエンティティに変換して表示させます。

■ファイルに追記してデバッグ

function _log($str, $type = 'debug', $file_name = null){
	if(is_null($file_name)){
        $file_name = LOG_DIR.'/'.$type.'_'.date('Ymd').'.log';
	}
	$data = date('Y/m/d H:i:s').' '.print_r($str, true). "\r\n";
	file_put_contents($file_name, $data, FILE_APPEND | LOCK_EX);
}

※LOG_DIR→ログが出力されるディレクトリのパスの定数

上記で日ごとにログが取れます。

ただ、このままですと直接ファイルにアクセスされかねないので、
http://labs.pakureserve.jp/archives/90
のように
.htaccess
を利用して指定拡張子をアクセス出来ないようにしたりの工夫が必要ですね。

次は、弊社システム開発部、のびーにょサブマネージャーから教えてもらったデバッグ方法です。

mail(’【デバッグを受け取りたいメールアドレス】’, __FILE__.’:’.__LINE__, print_r(【変数や配列等】, true));

これはすごく便利。。。

削除し忘れて放置するとメールボックスがすごいことになりかねませんので要注意です。笑

明日、弊社、設立3年目になるようです。

今後とも宜しくお願い致します。

チョコボール PHP

チョコボールです。
あと一か月働けば実務経験1年です。

前回csvファイルの一般的な書式について書きましたが、

その続きとして、DBから引っ張ってきたデータをCSV出力する関数を作ってみました。

例えば

+----+--------+-------+
| id |   name | level |
+----+--------+-------+
|  1 |  quest |    50 |
|  2 | arthur |    45 |
|  3 |  maria |    35 |
+----+--------+-------+

のようなテーブルが有り、これらを全て引っ張ってきてごにょごにょして下記のような配列に格納するとします。

$header(1行目の項目名)
Array
(
	[0] => ID
	[1] => 名前
	[2] => レベル
)

$data(データ部分)
Array
(
	[0] => Array
		(
			[id] => 1
			[name] => quest
			[level] => 50
		)

	[1] => Array
		(
			[id] => 2
			[name] => arthur
			[level] => 45
		)

	[2] => Array
		(
			[id] => 3
			[name] => maria
			[level] => 35
		)
)

これら配列を下記の関数に渡してやります。

downloadCsv($header, $data, 'member');

function downloadCsv($header = array(), $data = array(), $prefix = '')
{
	$ret = '';
	// ヘッダー(1行目の項目名)がある場合
	if(count($header) > 0){
		foreach($header as $val){
			//「"」は「""」に置換してエスケープ
			$tmp[] = str_replace('"', '""', $val);
		}
		$ret .= '"'.implode('","', $tmp).'"'."\r\n";
		unset($tmp);
	}

	foreach($data as $val){
		foreach($val as $vval){
			$tmp[] = str_replace('"', '""', $vval);
		}
		$ret .= '"'.implode('","', $tmp).'"'."\r\n";
		unset($tmp);
	}

	// 文字コードを変換
	$ret = mb_convert_encoding($ret, 'SJIS', 'UTF8');

	// 出力する際のファイル名
	$file_name = $prefix.date('Ymd').'.csv';

	// HTTPヘッダ出力
	Header("Content-Disposition: attachment; filename=${file_name}");
	Header("Content-Type: application/octet-stream; name=${file_name}");
	Header("Cache-Control: ");
	Header("Pragma: ");

	// データを出力
	echo $ret;
	exit;
}

member20081031.csvみたいな感じでDLできると思います。

もちろん、ソース内コメントの「文字コードを変換」部分は内部文字コードに合わせて変更しないとCSVが文字化けしてしまいます。
上記はUTF-8の場合です。

あと、バッファーに何か入ってる場合(デバッグ出力文字等)、その文字もCSVに出力されてしまうので、一番最初の行に

ob_end_clean();

を追加して出力のバッファリングをオフにすると良いです。

一度軽くハマったんですが “\r\n” は ‘\r\n’ じゃ駄目です。
文字列として認識してしまいますので。
ダブルクォートで囲んであげましょう。

チョコボール MySQL, PHP, データベース

そろそろプログラム歴1年のチョコボールです。

WEBブラウザは主にFirefoxを使っています。
(私以外の弊社システム開発部メンバーはみんな主に「Sleipnir」使ってます。。)

Firefoxデフォルトの設定でブラウザ右上部分にある「検索バー」について。

ググる時はだいたいここから検索する方、多いと思うんですが、辞書やYoutube動画、Wikipedia等、検索機能を追加していくことでかなり便利になります。

MozillaのFirefox用アドオンページ - 検索ページ
ここでは、メジャーな検索機能を追加していくことができます。

これがあると便利だな～的な検索エンジンがあまり用意されておらず、若干テンション下がりましたが、
ボランティアで運営されている「Mycroft プロジェクト」にて、マニアックな検索エンジンがたくさん紹介されています。

PHP関数を入力したらすぐにマニュアルの解説ページに飛べたらいいな～と思って探してみたら有りました！
「Mycroft プロジェクト」にて「php.net」と検索し、「PHP Function List - JP-ja」をクリックすることですぐに組み込めます。

便利ですね！

速度的には以前私が書いた記事、
PHP標準関数の手っ取り早い調べ方
http://labs.pakureserve.jp/archives/18
とあまり変わらないかもですが…。

eBay(海外オークション)で落札・出品をたまにするんですが、英文を作る時にすごく参考にしてるサイトがあります。
英語を学習されてる学生の方で利用している方は多いのではないでしょうか？
英辞郎 on the WEB
最近の言い回しも結構ヒットします。
「送料は」「DVDを焼く」「コーヒーが」という感じで作文的に検索しても目的に近い英文がヒットするので重宝してます。

これも有りました！
「alc.co.jp」と検索して「英辞郎 on the WEB」をクリックで。

ショートカットをうまく使うとさらに早くなるかもしれません。
「Ctrl + k」で検索バーの入力欄へ移動、入力して、「Ctrl + ↑」、「Ctrl + ↓」で検索エンジンを選択して「Enter」！
ちなみに新しいタブで検索結果を表示したい場合は、入力後、「Alt + Enter」です。

これからも便利な検索エンジン探してみようと思います。

チョコボール PHP, Tips, その他

久しぶりのチョコボールです。

最近CSV出力でちょっとハマりました。

プログラムでCSVファイルを生成する際、基本的に全てのフィールドに対してダブルクォーテーションで囲むようにしています。
必要とするCSVファイルによっては「ダブルクォーテーション無しで」という制約もありますので、そういった場合を除いての話です。

フィールド内にダブルクォーテーションやカンマが入っていなければ、フィールドデータをダブルクォーテーションで囲む必要は無いのですが、
入っていた場合、Excel等では正常に読み込めなくなります。
カンマを含むデータがフィールドに入っていた場合、「一つのフィールドの区切り」として認識しちゃいますから！

フィールドをダブルクォーテーションで囲んだ場合、フィールド内のダブルクォーテーションをエスケープする必要があります。

phpの世界ではエスケープといえば「\」マークをつけることが何かと多いです。
とあるシステムのCSV関連のクラスではフィールド内の「”」を「\”」と置換して出力しており、何の疑いも持たなかったんですが、
出力したCSVをExcelで読み込んでみたところ、ダブルクォーテーションを含むフィールドあたりでズレが生じてしまいました。

調べた結果、CSVファイルの一般的書式としてRFCが存在しました。

RFC4180

2005年の秋に制定されたようです。割と最近ですね。

要点をまとめると、

• 改行コードは(CR+LF)。
• 改行コード(CR+LF)、ダブルクォーテーション、カンマを含むフィールドは
  ダブルクォーテーションで囲むべきである。
• フィールド内にダブルクォーテーションを含む場合は、
  その直前にひとつダブルクォーテーションを付加してエスケープしなければならない。
• ファイル内最終レコードの末尾には改行コードがあってもなくてもよい。

フィールド内の「”」は「”"」のように置換してあげる必要があったのですね。

csvをRFC4180準拠で生成してやるとExcelでは読み込めると思います。

しかし、Excelでフィールド内でAlt + Enterで改行を入れてcsvとして保存するとその部分の改行コードはLFになります。。

取り敢えずRFC4180でのcsv形式が広く普及するといいな～と思います。

チョコボール PHP

二日連続で失礼します。
チョコボールです。

昨日の天気は荒れに荒れてましたね。。
今も荒れてますが。。
長時間雷鳴りっぱなしの豪雨！
住まいは東京の西の方なのでモロに影響受けました。。

PCが壊れるとまずいので家帰ってからずっとPCの電源抜いてました。
というのも、落雷で家電製品が壊れた…なんて話をよく聞くからです。

一か月ほど前、近所で落雷があり、同じアパートの住人がPCの電源が立ち上がらなくなった…と悔やんでいました。
その時、自分は家にいましたが、危険を感じて電源引っこ抜いていたので運よく無事でした。。

ここまで落雷が多いと、雷サージ対策をしなきゃまずいって気持ちになってきます。
今度電気屋に行った時は絶対買おう。。

昨日から久々にCakePHPを触ることになりました。
弊社コマースシステムの管理機能追加です。

現在のCakePHPのバージョンは1.2ですが、弊社のシステムはバージョン1.1で構築したもの。

ほぼ初心者の頃、いきなりCakePHPを触っていた自分はDB関係のデータ取得等はメソッドを使いまくっていたのでSQLになかなか慣れませんでした。
他人が書いたSQL文中のJOINという文字を見るとどうなってるか分からず、拒絶反応を起こしてしまう…というようなひどい状態が続いていました。
テーブルを結合してデータを取ってきてくれたりするCakePHPのアソシエーション機能は非常に便利ですが、理解しないままこれを利用しているといつまでたってもSQLについて詳しくなれません。。

SQLはできるだけベタに近い状態で書いた方がフレームワークを理解していない人でもすぐ理解でき、保守は楽だと思います。
無駄なSQLをたくさん流すと負荷がかかって遅くなりますし。。
処理が重くなるところはできるだけ簡素で効率のいいSQLを書きたいものです。

一通りWEBアプリケーションに精通している方達はこのようなフレームワークを使った方が高速に開発できると思います。

CakePHPの好きなところはすごく好きなんですけどね。。
システムとして見るとかなり勉強にもなります。

パフォーマンス性や保守性を重視するか、高速開発を重視するか…。

ただ間違いないと思うことは、処理が重いシステムは誰も嫌がる…とういことです。

チョコボール MySQL, PHP, Tips, データベース