なんでも受け取れるフォーム。（SQLite3、自動的にテーブル作成、自動的にカラム拡張、303リダイレクト、データ表示、CSVダウンロード機能つき）

flexform.php

<?php // -*- coding: utf-8 -*- >

// [インストール]
// dat というディレクトリを作って、パーミッションを777にします。
// <form action='flexform.php' method='post'>
// で投げると、勝手にデータベースを作り、フォームにあったカラムを作成します。
// 
// [データ表示]
// form.php? にアクセスすると、テーブル一覧が表示されます。
// リンクをクリックすると、そのテーブルの内容が表示されます。
//
// [セキュリティ]
// (.htaccess)
// <Files flexform.php>
// 	<Limit GET>
// 		Order deny,allow
// 		Allow from 127.0.0.1
// 	</Limit>
// </Files>
// 
// (dat/.htaccess)
// Order deny,allow
// Deny from all

class flexform {

	public function __construct(&$db,$config) {
		$this->db = $db;
		$this->config = $config;
	}
	
	public function check_column() {
		$recv_keys = array_keys($_POST);
		$result = $this->db->query(sprintf('PRAGMA table_info (`%s`)',$this->config['table_name']));
		$column_keys = array();
		while($res = $result->fetchArray(SQLITE3_ASSOC)) {
			foreach ($res as $col_key => $col_val) {
				if ($col_key === 'name') {
					$column_keys[] = $col_val;
				}
			}
		}
		$append_columns = array_diff($recv_keys,$column_keys);

		foreach ($append_columns as $append_column) {
			$sql = sprintf('ALTER TABLE `%s` ADD COLUMN `%s`',$this->config['table_name'],$this->db->escapeString($append_column));
			$stmt = $this->db->exec($sql);
		}
	}

	public function insert_db() {

		$sql_col_part = '';
		$sql_val_part = '';
		foreach ($_POST as $recv_key => $recv_val) {
			$sql_col_part .= '`'.$this->db->escapeString($recv_key).'`,';
			$sql_val_part .= '\''.$this->db->escapeString($recv_val).'\',';
		}
		$sql_col = substr($sql_col_part,0,-1);
		$sql_val = substr($sql_val_part,0,-1);
		$sql = sprintf('INSERT INTO `%s` (%s) VALUES (%s)',$this->config['table_name'],$sql_col,$sql_val);
		$this->db->exec($sql);
	}

	public function redirect() {
		header("Location: {$this->config['redirect_uri']}",true,303);
	}

	public function __destruct() {
		$this->db->close();
	}

}

class formviewer {
	public function __construct(&$db,$config) {
		$this->db = $db;
		$this->config = $config;
	}

	public function show() {
		$result = $this->db->query('select name from sqlite_master where type=\'table\';');	
		$tables = array();
		while($res = $result->fetchArray(SQLITE3_NUM)) {
			$tables[] = $res[0];
		}
		$string = '';
		foreach ($tables as $table) {
			$result = $this->db->query(sprintf('PRAGMA table_info (`%s`)',$table));
			$column_keys = array();
			$string .= "<a href='?detail=$table' rel='nofollow'><h2>$table</h2></a><table>";
			while($res = $result->fetchArray(SQLITE3_ASSOC)) {
				$string .= '<tr>';
				foreach ($res as $col_key => $col_val) {
					$string .= '<td>'.$col_val.'</td>';
				}
				$string .= '</tr>';
			}
			$string .= '</table>';
		}
		return $string;
	}

	public function detail($table) {
		$sheet = $this->_fetch_detail($table);
		$string = "<h2>$table</h2><a href='?download=$table' rel='nofollow'>CSV Download</a><table>";
		foreach($sheet as $row) {
			foreach ($row as $col_val) {
				$string .= '<td>'.$col_val.'</td>';
			}
			$string .= '</tr>';
		}
		$string .= '</table>';
		return $string;
	}

	public function csv($table) {
		$sheet = $this->_fetch_detail($table);
		$csv = '';
		foreach ($sheet as $row) {
			foreach ($row as $col) {
				$csv .= '"'.$col.'",';
			}
			$csv = substr($csv,0,-1)."\n";
		}
		echo $csv;
	}

	private function _fetch_detail($table) {
		$result = $this->db->query(sprintf('SELECT * FROM (`%s`)',$this->db->escapeString($table)));
		$column_keys = array();
		$sheet = array();
		while($res = $result->fetchArray(SQLITE3_ASSOC)) {
			$row = array();
			foreach ($res as $col_key => $col_val) {
				$row[] = $col_val;
			}
			$sheet[] = $row;
		}
		return $sheet;
	}
}

$config = array(
	'database_name' => 'dat/database.sqlite3',
	'redirect_uri' => 'http://hikkoshi-500yen.com/',
);

if ( ! class_exists('SQLite3') ) {
	echo 'SQLite3がインストールされていない. try: yum install php-sqlite3 , apt-get php5-sqlite3';
	die('SQLite3 not exists');
}
try {
	$db = new SQLite3($config['database_name']);
}
catch (Exception $e) {
	echo "{$this->config['database_name']} databaseの作成または接続に失敗. パーミッションはあるか？";
	print_r($e);
	die('SQLite3 construct failed');
}


if ($_SERVER['REQUEST_METHOD'] === 'POST') {
	if (isset($_SERVER['HTTP_REFERER'])) {
		$config['table_name'] = substr(hash('sha224',$_SERVER['HTTP_REFERER']),0,20);
	}
	else {
		$config['table_name'] = 'noref';
	}
	$sql = sprintf('CREATE TABLE IF NOT EXISTS `%s` (id INTEGER PRIMARY KEY AUTOINCREMENT)',$config['table_name']);
	if ( ! $db->exec($sql) )  {
		echo "{$config['table_name']} tableの作成または接続に失敗.";
		die('SQLite3 create table failed');
	}
	$form = new flexform($db,$config);
	$form->check_column();
	$form->insert_db();
	$form->redirect();
}
else if ($_SERVER['REQUEST_METHOD'] === 'GET') {
	$viewer = new formviewer($db,$config);
	if (isset($_GET['detail'])) {
		$html = $viewer->detail($_GET['detail']);
?>
<!DOCTYPE html>
<head>
	<meta charset='utf-8' />
	<meta name="robots" content="noindex,nofollow" />
	<title>flexform.php: detail</title>
</head>
<body>
<h1><a href='?' rel='nofollow'>flexform</a></h1>
<?php echo $html; ?>
</body>
<?php
	}
	else if (isset($_GET['download'])) {
		header(sprintf("Content-Disposition: attachment; filename=%s.csv",preg_replace('/[^\w\d]+/','',$_GET['download'])));
		$html = $viewer->csv($_GET['download']);
	}
	else {
		$html = $viewer->show();
?>
<!DOCTYPE html>
<head>
	<meta charset='utf-8' />
	<meta name="robots" content="noindex,nofollow" />
	<title>flexform.php: tables</title>
</head>
<body>
<h1><a href='?' rel='nofollow'>flexform</a></h1>
<?php echo $html; ?>
</body>
<?php
	}
}