テーブルを作成(mysql)
忘れてしまうので記録。
ここのjsonデータを出力できるようなテーブルを作成する構文をphpで出力する。
http://checker-site.hatenablog.com/entry/2015/12/14/235144
<?php $json_data = array(); //idにauto_incrementを設定 echo "create table test(id int(11) auto_increment,name varchar(20),gender varchar(2),twitter_name varchar(20),twitter_id varchar(20),status tinyint(1), checker_id int(11),"; $all = array( "total", "broadcast", "chat"); $total = array( "viewers", "isLive", "lastDate"); $service = array("ustream", "twitch", "niconico", "livetube", "youtubelive", "twicas", "fc2", "afreecacom", "cavetube", "hitbox"); $chat_service = array("ustirc", "juschat"); $chat = array("id", "channel_name"); $item = array("isLive", "viewers", "title", "description", "created_at", "id", "channel_name", "user_name", "url", "archive_url", "snapshot", "lastDate"); foreach($total as $i){ $json_data["total"][$i] = true; if($i == "viewers"){ echo "total_" . $i . " int(11),"; }elseif($i == "isLive"){ echo "total_" . $i . " tinyint(1),"; }elseif($i=="lastDate"){ echo "total_" . $i . " TIMESTAMP,"; }elseif($i=="twitter_name"){ echo "total_" . $i . " varchar(20),"; }elseif($i=="twitter_id"){ echo "total_" . $i . " int(11),"; } } foreach($service as $i){ foreach($item as $j){ $json_data["broadcast"][$i][$j] = true; if($j == "isLive"){ echo $i . "_" . $j . " tinyint(1),"; }elseif($j == "viewers"){ echo $i . "_" . $j . " int(11),"; }elseif($j == "title"){ echo $i . "_" . $j . " varchar(40),"; }elseif($j == "description"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="created_at"){ echo $i . "_" . $j . " TIMESTAMP,"; }elseif($j=="id"){ echo $i . "_" . $j . " int(11),"; }elseif($j=="channel_name"){ echo $i . "_" . $j . " varchar(20),"; }elseif($j=="user_name"){ echo $i . "_" . $j . " varchar(20),"; }elseif($j=="url"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="archive_url"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="snapshot"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="lastDate"){ echo $i . "_" . $j . " TIMESTAMP,"; } } } foreach($chat_service as $i){ foreach($chat as $j){ $json_data["chat"][$i][$j] = true; if($j == "isLive"){ echo $i . "_" . $j . " tinyint(1),"; }elseif($j == "viewers"){ echo $i . "_" . $j . " int(11),"; }elseif($j == "title"){ echo $i . "_" . $j . " varchar(40),"; }elseif($j=="created_at"){ echo $i . "_" . $j . " TIMESTAMP,"; }elseif($j=="id"){ echo $i . "_" . $j . " int(11),"; }elseif($j=="channel_name"){ echo $i . "_" . $j . " varchar(20),"; }elseif($j=="user_name"){ echo $i . "_" . $j . " varchar(20),"; }elseif($j=="url"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="archive_url"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="snapshot"){ echo $i . "_" . $j . " varchar(100),"; }elseif($j=="lastDate"){ echo $i . "_" . $j . " TIMESTAMP,"; } } } $json = json_encode($json_data); echo "PRIMARY KEY (id));";//idにauto_incrementを設定 echo PHP_EOL . PHP_EOL;
出力結果↓
create table test(id int(11) auto_increment,name varchar(20),gender varchar(2),twitter_name varchar(20),twitter_id varchar(20),status tinyint(1), checker_id int(11),total_viewers int(11),total_isLive tinyint(1),total_lastDate date,ustream_isLive tinyint(1),ustream_viewers int(11),ustream_title varchar(40),ustream_description varchar(100),ustream_created_at date,ustream_id int(11),ustream_channel_name varchar(20),ustream_user_name varchar(20),ustream_url varchar(100),ustream_archive_url varchar(100),ustream_snapshot varchar(100),ustream_lastDate date,twitch_isLive tinyint(1),twitch_viewers int(11),twitch_title varchar(40),twitch_description varchar(100),twitch_created_at date,twitch_id int(11),twitch_channel_name varchar(20),twitch_user_name varchar(20),twitch_url varchar(100),twitch_archive_url varchar(100),twitch_snapshot varchar(100),twitch_lastDate date,niconico_isLive tinyint(1),niconico_viewers int(11),niconico_title varchar(40),niconico_description varchar(100),niconico_created_at date,niconico_id int(11),niconico_channel_name varchar(20),niconico_user_name varchar(20),niconico_url varchar(100),niconico_archive_url varchar(100),niconico_snapshot varchar(100),niconico_lastDate date,livetube_isLive tinyint(1),livetube_viewers int(11),livetube_title varchar(40),livetube_description varchar(100),livetube_created_at date,livetube_id int(11),livetube_channel_name varchar(20),livetube_user_name varchar(20),livetube_url varchar(100),livetube_archive_url varchar(100),livetube_snapshot varchar(100),livetube_lastDate date,youtubelive_isLive tinyint(1),youtubelive_viewers int(11),youtubelive_title varchar(40),youtubelive_description varchar(100),youtubelive_created_at date,youtubelive_id int(11),youtubelive_channel_name varchar(20),youtubelive_user_name varchar(20),youtubelive_url varchar(100),youtubelive_archive_url varchar(100),youtubelive_snapshot varchar(100),youtubelive_lastDate date,twicas_isLive tinyint(1),twicas_viewers int(11),twicas_title varchar(40),twicas_description varchar(100),twicas_created_at date,twicas_id int(11),twicas_channel_name varchar(20),twicas_user_name varchar(20),twicas_url varchar(100),twicas_archive_url varchar(100),twicas_snapshot varchar(100),twicas_lastDate date,fc2_isLive tinyint(1),fc2_viewers int(11),fc2_title varchar(40),fc2_description varchar(100),fc2_created_at date,fc2_id int(11),fc2_channel_name varchar(20),fc2_user_name varchar(20),fc2_url varchar(100),fc2_archive_url varchar(100),fc2_snapshot varchar(100),fc2_lastDate date,afreecacom_isLive tinyint(1),afreecacom_viewers int(11),afreecacom_title varchar(40),afreecacom_description varchar(100),afreecacom_created_at date,afreecacom_id int(11),afreecacom_channel_name varchar(20),afreecacom_user_name varchar(20),afreecacom_url varchar(100),afreecacom_archive_url varchar(100),afreecacom_snapshot varchar(100),afreecacom_lastDate date,cavetube_isLive tinyint(1),cavetube_viewers int(11),cavetube_title varchar(40),cavetube_description varchar(100),cavetube_created_at date,cavetube_id int(11),cavetube_channel_name varchar(20),cavetube_user_name varchar(20),cavetube_url varchar(100),cavetube_archive_url varchar(100),cavetube_snapshot varchar(100),cavetube_lastDate date,hitbox_isLive tinyint(1),hitbox_viewers int(11),hitbox_title varchar(40),hitbox_description varchar(100),hitbox_created_at date,hitbox_id int(11),hitbox_channel_name varchar(20),hitbox_user_name varchar(20),hitbox_url varchar(100),hitbox_archive_url varchar(100),hitbox_snapshot varchar(100),hitbox_lastDate date,ustirc_id int(11),ustirc_channel_name varchar(20),juschat_id int(11),juschat_channel_name varchar(20),PRIMARY KEY (id));
