勉強したことのメモ

webプログラマが勉強したことのメモ。

PHPとSQLiteでi-mobileのデータをグラフ化

   

i-mobileでクリック保証のアフィリエイトだけど数値が並んでいるだけでグラフでは確認できない。なのでSQLiteの練習もかねて、グラフ化を行った際のメモ。

機能は以下の通り。

 

  • 詳細レポートの日別データCSVをアップロードできるフォーム
  • アップロードしたデータをSQLiteに保存
  • 既に登録されている日付分は上書き保存せず、差分だけ保存する
  • データリセット機能
  • highcharts.jsを使ってグラフ表示

 


 

 

■下準備

以下からhighcharts.jsを用意してアップロードしとく。

http://www.highcharts.com/

 


 

 

■ソース

<?
if( !is_file('affiliate.sqlite') ){
	$flg = 1;
}

$db = new SQLite3('affiliate.sqlite');

if( $flg ){
	$query = "CREATE TABLE 'i-mobile' ( 'seq' INTEGER PRIMARY KEY, 'ymd' DATE , 'pv' INT, 'imp' INT, 'click' INT, 'ctr' FLOAT, 'cpm' FLOAT, 'income' INT );";
	$result = $db->query($query);
}


/* リセット時 */
if( $_GET['mode'] == 'reset' ){
	$query = 'DELETE FROM `i-mobile`';

	$result = $db->query($query);

	if( !$result ){
		$msg = 'reset error!';
	} else {
		header('Location:' . $_SERVER['PHP_SELF'] . '?mode=reseted');
	}
}


/* ファイルアップロード時 */
if (is_uploaded_file($_FILES["upfile"]["tmp_name"])) {

	$msg = '';

	do {

		/* アップロード */
		if( !move_uploaded_file($_FILES["upfile"]["tmp_name"], $_FILES["upfile"]["name"]) ){
			$msg = 'file upload error!';
			break;
		}

		chmod( $_FILES["upfile"]["name"], 0644);
		setlocale(LC_ALL, 'ja_JP.UTF-8');
		$data = file_get_contents($_FILES["upfile"]["name"]);
		$data = mb_convert_encoding($data, 'UTF-8', 'sjis-win');
		$temp = tmpfile();
		$meta = stream_get_meta_data($temp);
		fwrite($temp, $data);
		rewind($temp);
		$file = new SplFileObject($meta['uri']);
		$file->setFlags(SplFileObject::READ_CSV);
		$csv  = array();
		foreach($file as $line) {
			$csv[] = $line;
		}
		fclose($temp);
		$file = null;
		unlink($_FILES["upfile"]["name"]);


		/* 既に入力されている日付の呼び出し */
		$query = '
			SELECT ymd 
			FROM `i-mobile`
		';
		$result = $db->query($query);

		if( !$result ){
			$msg = 'select error!';
			break;
		}

		while ($row = $result->fetchArray()) {
			$readData[$row['ymd']] = 1;
		}


		/* 書き込み */
		$insertData = '';
		for( $i = 1; $i < count($csv); $i++ ){

			if( !$readData[$csv[$i][0]] ){ //既に登録されている分は不要

				$query = '
					INSERT INTO `i-mobile` 
						(ymd, pv, imp, click, ctr, cpm, income) 
					VALUES 
						("' . $csv[$i][0] . '","' . $csv[$i][1] . '","' . $csv[$i][2] . '", "' . $csv[$i][3] . '","' . str_replace('%','',$csv[$i][4]) . '", "' . str_replace('¥','',$csv[$i][5]) . '", "' . $csv[$i][6] . '")
				';

				$result = $db->query($query);

				if( !$result ){
					$msg = 'insert error!';
					break;
				}
			}
		}


		if( $msg == '' ){
			header('Location:' . $_SERVER['PHP_SELF'] . '?mode=uploaded');
		}

	} while (0);
}


/* 読み込み */
$query = '
	SELECT * 
	FROM `i-mobile` 
	ORDER BY ymd ASC 
';
$result = $db->query($query);

$highchartsData['ymd'] = '';
$highchartsData['pv'] = '';
$highchartsData['imp'] = '';
$highchartsData['click'] = '';
$highchartsData['ctr'] = '';
$highchartsData['cpm'] = '';
$highchartsData['income'] = '';

while ($row = $result->fetchArray()) {
	$affiliateData[$row['id']]['ymd'] = $row['ymd'];
	$affiliateData[$row['id']]['pv'] = $row['pv'];
	$affiliateData[$row['id']]['imp'] = $row['imp'];
	$affiliateData[$row['id']]['click'] = $row['click'];
	$affiliateData[$row['id']]['ctr'] = $row['ctr'];
	$affiliateData[$row['id']]['cpm'] = $row['cpm'];
	$affiliateData[$row['id']]['income'] = $row['income'];

	$highchartsData['ymd'] .= '"'. $row['ymd'] . '",';
	$highchartsData['pv'] .= $row['pv'] . ',';
	$highchartsData['imp'] .= $row['imp'] . ',';
	$highchartsData['click'] .= $row['click'] . ',';
	$highchartsData['ctr'] .= $row['ctr'] . ',';
	$highchartsData['cpm'] .= $row['cpm'] . ',';
	$highchartsData['income'] .= $row['income'] . ',';
}
?>

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>i-mobileグラフ化</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<link href="//netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/css/bootstrap-combined.min.css" rel="stylesheet">
<script src="//netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/js/bootstrap.min.js"></script>
<script src="./highcharts/js/highcharts.js"></script>
<script>
$(function(){
	$('#line').highcharts({
		chart: {
			type: 'line',
			marginRight: 130,
			marginBottom: 25
		},
		title: {
			text: 'i-mobile',
			x:-20 //center
		},
		xAxis: {
			categories: [<? echo rtrim($highchartsData['ymd'], ',');?>]
		},
		yAxis: {
			title: {
				align: 'middle',
				rotation: -90

			},
			plotLines: [{
				value: 0,
				width: 1,
				color: '#808080'
			}]
		},
		legend: {
			layout: 'vertical',
			align: 'right',
			verticalAlign: 'top',
			x: -80,
			y: 0,
			borderWidth: 0
		},
		series: [{
			name: 'ページ表示回数',
			data: [<? echo rtrim($highchartsData['pv'], ',');?>]
		}, {
			name: '広告表示回数',
			data: [<? echo rtrim($highchartsData['imp'], ',');?>]
		}, {
			name: 'ページCTR',
			data: [<? echo rtrim($highchartsData['ctr'], ',');?>]
		}, {
			name: 'ページCPM',
			data: [<? echo rtrim($highchartsData['cpm'], ',');?>]
		}, {
			name: '収益金額',
			data: [<? echo rtrim($highchartsData['income'], ',');?>]
		}]
	});
});
function dataReset(){
	if( confirm('データをリセットしますか?') ){
		location.href = '<?=$_SERVER["PHP_SELF"]?>?mode=reset'
	} else {
		return false;
	}
}
</script>
</head>
<body>
	<? if( $msg ){ ?>
		<div class="alert alert-error">
			<?=$msg?>
		</div>
	<? } ?>
	<? if( $_GET['mode'] == 'uploaded' || $_GET['mode'] == 'reseted' ){?>
		<div class="alert alert-success">
			<?=( $_GET['mode'] == 'uploaded' ) ? 'CSVファイルのアップロードが完了しました。': 'データのリセットが完了しました。';?>
		</div>
	<? } ?>
	<form enctype="multipart/form-data" method="post" action="">
		<input type="file" name="upfile"><br />
		<input type="submit" value="Upload" class="btn btn-info">
		<input type="button" value="Reset" class="btn btn-danger" onclick="return dataReset();">
	</form>
	<div id="line"><!--グラフ領域--></div>
</body>
</html>

 


 

 

■その他

ソースをそのまま保存して、highchats.jsのパスを書き換えてアップロードするだけで動くはず。

気付いた点としてSQLiteのバージョンが3.7.11以前のものだと、MySQLでよく使う以下の形のバルクインサートができない模様。

 

INSERT INTO table 
	(aaa, bbb) 
VALUES 
	(aaa,bbb), (aaa,bbb)

 

以下のような形ならバルクインサート可能みたいだけど、1回に挿入できるのは500件まで。

 

INSERT INTO member
SELECT 0 AS id, 'foo' AS name
UNION ALL SELECT 1, 'bar'
UNION ALL SELECT 2, 'baz'
UNION ALL SELECT 3, 'qux';

 

500件以上なら分ける処理とか入れるのが面倒くさかったので1回ずつ挿入にした。

また、データのリセット時にTRUNCATEを使おうとしたけどSQLiteには無いらしい。DELETE FROM tableで対応。

BBSと違って複数人数が使うものでもないし、日別データならデータ数も少ないし、こういうページにSQLiteが向いていそう。

 - PHP, jQuery, SQLite

  関連記事

tableのヘッダーを残して表示(jquery-decapitate)

bootstrapはあまり使わないので切り離して使えるように したいところ。 ■ ...

Ajaxでボタンとテキストボックス操作

ボタンを押して「https://aaa/bbb/ajax.php」からの 返答を ...

リアルタイムプレビュー時の改行とテーブルの上部揃え

■サンプル http://sample.taitan916.info/previ ...

Opauthで「Please change the…」エラー

Opauthを使用中に「Notice: Please change the va ...

PHPで配列に特定の値が入っているか検索

PHPで配列に特定の値が入っているか検索して trueかfalseを返したい、と ...

UTF-16にエンコードされている文字列をUTF-8へデコード

エンコードされている文字列であまり見かけない感じのものがあった。UTF-8やSh ...

MySQLiでUPDATE文

MySQLiでUPDATE文 ■ソース $mysqli = new mysqli ...

PHPでxmlのpubDateを見やすい形に整形する

PHPでsimplexml_load_file関数とかを使ってデータを取得した際 ...

Ajaxで画像のアップロード(jquery.upload)

業務中に画像の選択後、アップロードボタンを押して画像のアップ、 その後フォームの ...

ゼロ詰め(ゼロパディング)の逆はゼロサプレス

ゼロパディングしている日付のゼロの部分を排除したかった。ただ、どうやって調べたら ...