月別アーカイブ: 2024年9月

Google Spread Sheetでデータベスを作る

2024年9月16日未分類haruki

GoogleSpreadSheetを元に、Mysqlのデータベースを作るプログラムを作ってみる

対応は

タイトル名　ー＞　データベース名

シート名　ー＞　テーブル名

シートの中は、

フィールド名

フィールドの型

データ

として、１列目に、上記内容を指定できる書式とする

フィールド名　ー＞　fieldname

フィールドの型　ー＞　fieldtype

テーブルのコメント　ー＞　table comment

フィールドのコメント　ー＞　fieldcomment

SQLのテンプレート　-> template

データ　ー＞　>

コメント　ー＞　＃

言語はPHP

データはGoogleSpreadSheet

取得方法はGoogleAPI

作成はMysql

とする

データの元となるGggleSpreadSheetを使って、閲覧許可にして置く

ID　を、取得しておく

以下を参考にして

https://www.otsuka-bs.co.jp/web-creation/blog/archive/20230904-03.html

接続とタイトルの取得確認をする

シートの内容は、以下のような感じ

実データを書き込むと以下になる

fieldtypeを２行にしてみました

作成した、シートは閲覧許可にしておく

データを参照するプログラムは以下となる

&lt;?php
// php importer.php
ini_set('display_errors', "On");
require './vendor/autoload.php';
$key ="my-project-php-XXXXXXXX-XXXXXXXfca9.json";
$client = new \Google_Client();
$client->setAuthConfig($key);
$client->addScope(\Google_Service_Sheets::SPREADSHEETS);
$client->setApplicationName("Test"); // 適当な名前でOK
$service = new Google_Service_Sheets($client);
$spreadsheet_id = 'xxxxxxxxxx';
//https://docs.google.com/spreadsheets/d/105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0
$spreadsheet_id = '105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';
$response = $service->spreadsheets->get($spreadsheet_id);
print($response->properties->title."\n"); // タイトル
$sheets = $response->getSheets();
print(count($sheets)."\n");
print($sheets[0]->properties->title."\n");
print($sheets[1]->properties->title."\n");
$sheet_name=$sheets[0]->properties->title;
$sheet_range="A1:F11";
$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);
$notice = $response->getValues();
print_r($notice);
for ($i = 0; $i &lt;count($notice); $i++) {
print_r($notice[$i]);

for ($j = 0; $j &lt;count($notice[$i]); $j++) {
print_r($notice[$i][$j]);
}
}
exit("ok");

<?php

// php importer.php

ini_set('display_errors', "On");

require './vendor/autoload.php';

$key ="my-project-php-XXXXXXXX-XXXXXXXfca9.json";

$client = new \Google_Client();

$client->setAuthConfig($key);

$client->addScope(\Google_Service_Sheets::SPREADSHEETS);

$client->setApplicationName("Test"); // 適当な名前でOK

$service = new Google_Service_Sheets($client);

$spreadsheet_id = 'xxxxxxxxxx';

//https://docs.google.com/spreadsheets/d/105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0

$spreadsheet_id = '105NXXXXXXXXlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';

$response = $service->spreadsheets->get($spreadsheet_id);

print($response->properties->title."\n"); // タイトル

$sheets = $response->getSheets();

print(count($sheets)."\n");

print($sheets[0]->properties->title."\n");

print($sheets[1]->properties->title."\n");

$sheet_name=$sheets[0]->properties->title;

$sheet_range="A1:F11";

$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);

$notice = $response->getValues();

print_r($notice);

for ($i = 0; $i <count($notice); $i++) {

print_r($notice[$i]);

for ($j = 0; $j <count($notice[$i]); $j++) {

print_r($notice[$i][$j]);

}

exit("ok");

では、

テーブル名と

フィールドの行を見つける

フィールドのタイプを見つける

Create文を作ってみる

&lt;?php
// php importer.php
ini_set('display_errors', "On");
require './vendor/autoload.php';
$key ="my-project-php-xxxxxx-xxxxxxxxxca9.json";

$client = new \Google_Client();
$client->setAuthConfig($key);
$client->addScope(\Google_Service_Sheets::SPREADSHEETS);
$client->setApplicationName("Test"); // 適当な名前でOK
$service = new Google_Service_Sheets($client);

$spreadsheet_id = 'xxxxxxxxxx';
//https://docs.google.com/spreadsheets/d/105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0
$spreadsheet_id = '105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';
$response = $service->spreadsheets->get($spreadsheet_id);
print($response->properties->title."\n"); // タイトル
$sheets = $response->getSheets();
print(count($sheets)."\n");
print($sheets[0]->properties->title."\n");
print($sheets[1]->properties->title."\n");
$sheet_name=$sheets[0]->properties->title;
$sheet_range="A1:F12";
$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);
$notice = $response->getValues();
print_r($notice);
for ($i = 0; $i &lt;count($notice); $i++) {
print_r($notice[$i]);

for ($j = 0; $j &lt;count($notice[$i]); $j++) {
print_r($notice[$i][$j]);
}
}
// 
$r_filedname=0;
$r_filetype1=1;
$r_filetype2=2;
$r_data_start=8;
$r_data_end=11;
$fileds="";
$slqtemp="CREATE TABLE IF NOT EXISTS  `#tablename#` ( `id` INT(11) NOT NULL AUTO_INCREMENT,#fields# , `pub_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`) USING BTREE)COLLATE=`utf8mb4_general_ci`ENGINE=InnoDB";
$tablename="fruit";
for ($j = 1; $j &lt;count($notice[$r_filedname]); $j++) {
if($j!=1){$fileds=$fileds.",";}
$fileds=$fileds."`".$notice[$r_filedname][$j]."`".$notice[$r_filetype1][$j]." ".$notice[$r_filetype2][$j];
}
print("\n".$fileds);
// https://www.sejuku.net/blog/30092
//$text = 'apple,orange,apple,orange';
//指定した文字列が一致したら置き換える
//$replace = str_replace('apple', 'melon', $text);
$slqtemp=str_replace('#tablename#', $tablename, $slqtemp);
$slqtemp=str_replace('#fields#', $fileds, $slqtemp);
print("\n".$slqtemp);
$sql_insert="insert into ".$tablename."(";
$sql_insert2="";
for ($j = 1; $j &lt;count($notice[$r_filedname]); $j++) {
if($j!=1){
	$sql_insert=$sql_insert.",";
	$sql_insert2=$sql_insert2.",";
}
$sql_insert=$sql_insert."`".$notice[$r_filedname][$j]."`";
$sql_insert2=$sql_insert2."?";
}
$sql_insert=$sql_insert.") values (".$sql_insert2.")";
print("\n");
print($sql_insert);
try {
$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
} catch (PDOException $e) {
    exit($e->getMessage());    
}
// テーブル作成
$stmt = $pdo->prepare($slqtemp);
$flag = $stmt->execute();
print($flag);
// データインポート
for ($i = $r_data_start; $i &lt;= $r_data_end; $i++) {
//$r_data_start=8;
//$r_data_end=11;
	$stmt = $pdo->prepare($sql_insert);
	$mapdata=array();
	for ($j = 1; $j &lt;count($notice[$r_filedname]); $j++) {
		if(strpos(strtoupper($notice[$r_filetype1][$j]),"INT")!==false){
			print($notice[$r_filetype1][$j]."にはINT１が含まれます\n");
			if($notice[$i][$j]==""){
				$setdata="0";
			}else{
				$setdata=$notice[$i][$j];
			}
		}else{
			print($notice[$r_filetype1][$j]."にはINT１が含まれません\n");
			 $setdata=mb_convert_encoding($notice[$i][$j],"utf-8","sjis") ;
		}
		array_push($mapdata,$setdata);
	}
//			print_r($mapdata);
		$flag = $stmt->execute($mapdata);
	}
exit("\nok");

100

101

102

<?php

// php importer.php

ini_set('display_errors', "On");

require './vendor/autoload.php';

$key ="my-project-php-xxxxxx-xxxxxxxxxca9.json";

$client = new \Google_Client();

$client->setAuthConfig($key);

$client->addScope(\Google_Service_Sheets::SPREADSHEETS);

$client->setApplicationName("Test"); // 適当な名前でOK

$service = new Google_Service_Sheets($client);

$spreadsheet_id = 'xxxxxxxxxx';

//https://docs.google.com/spreadsheets/d/105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso/edit?gid=0#gid=0

$spreadsheet_id = '105NExxxxxxlzrizMVGDH7i-UEi-eKRlvDKODGBAHnso';

$response = $service->spreadsheets->get($spreadsheet_id);

print($response->properties->title."\n"); // タイトル

$sheets = $response->getSheets();

print(count($sheets)."\n");

print($sheets[0]->properties->title."\n");

print($sheets[1]->properties->title."\n");

$sheet_name=$sheets[0]->properties->title;

$sheet_range="A1:F12";

$response = $service->spreadsheets_values->get($spreadsheet_id, $sheet_name.'!'.$sheet_range);

$notice = $response->getValues();

print_r($notice);

for ($i = 0; $i <count($notice); $i++) {

print_r($notice[$i]);

for ($j = 0; $j <count($notice[$i]); $j++) {

print_r($notice[$i][$j]);

}

$r_filedname=0;

$r_filetype1=1;

$r_filetype2=2;

$r_data_start=8;

$r_data_end=11;

$fileds="";

$slqtemp="CREATE TABLE IF NOT EXISTS `#tablename#` ( `id` INT(11) NOT NULL AUTO_INCREMENT,#fields# , `pub_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`) USING BTREE)COLLATE=`utf8mb4_general_ci`ENGINE=InnoDB";

$tablename="fruit";

for ($j = 1; $j <count($notice[$r_filedname]); $j++) {

if($j!=1){$fileds=$fileds.",";}

$fileds=$fileds."`".$notice[$r_filedname][$j]."`".$notice[$r_filetype1][$j]." ".$notice[$r_filetype2][$j];

}

print("\n".$fileds);

// https://www.sejuku.net/blog/30092

//$text = 'apple,orange,apple,orange';

//指定した文字列が一致したら置き換える

//$replace = str_replace('apple', 'melon', $text);

$slqtemp=str_replace('#tablename#', $tablename, $slqtemp);

$slqtemp=str_replace('#fields#', $fileds, $slqtemp);

print("\n".$slqtemp);

$sql_insert="insert into ".$tablename."(";

$sql_insert2="";

for ($j = 1; $j <count($notice[$r_filedname]); $j++) {

if($j!=1){

$sql_insert=$sql_insert.",";

$sql_insert2=$sql_insert2.",";

}

$sql_insert=$sql_insert."`".$notice[$r_filedname][$j]."`";

$sql_insert2=$sql_insert2."?";

}

$sql_insert=$sql_insert.") values (".$sql_insert2.")";

print("\n");

print($sql_insert);

try {

$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]);

} catch (PDOException $e) {

exit($e->getMessage());

}

// テーブル作成

$stmt = $pdo->prepare($slqtemp);

$flag = $stmt->execute();

print($flag);

// データインポート

for ($i = $r_data_start; $i <= $r_data_end; $i++) {

//$r_data_start=8;

//$r_data_end=11;

$stmt = $pdo->prepare($sql_insert);

$mapdata=array();

for ($j = 1; $j <count($notice[$r_filedname]); $j++) {

if(strpos(strtoupper($notice[$r_filetype1][$j]),"INT")!==false){

print($notice[$r_filetype1][$j]."にはINT１が含まれます\n");

if($notice[$i][$j]==""){

$setdata="0";

}else{

$setdata=$notice[$i][$j];

}

}else{

print($notice[$r_filetype1][$j]."にはINT１が含まれません\n");

$setdata=mb_convert_encoding($notice[$i][$j],"utf-8","sjis") ;

}

array_push($mapdata,$setdata);

}

// print_r($mapdata);

$flag = $stmt->execute($mapdata);

}

exit("\nok");

フィールド名の位置はどは、ハードコーディングですが

これで、デーブルの作成と、データのインポートができました

一応数値が空の時の処理も、無事対応できているようです

あとは、一度にテーブルを読む込できるようにします

２回目で、不要なテーブルは先頭に-(マイナス)を付けておいて、スルーするようにしよう

また、テーブルをDropして、強制書き換えもできるが、必要なときには、機能を追加してみます

PHPでCSVファイルによりMysqlを更新

2024年9月9日未分類haruki

前回の続きで、PHPを使ってメールでアップロードしたCSVファイルでデータベースを更新する処理を作ってみます

行う内容としては

１，CSVファイルの解析

２，対象となるデータベース、テーブルの特定

３，データベースの更新（追加）

４，結果のレポート

となります

まず、CSVファイルの解析は

ファイルを開いて、データーの取得です

ファイルの大きさにもよるのですが

PHPには、CSVを読み込む関数があるので、それを使ってみる

また、１行目がフィールド名になっている事が多いので、

１行目は特別とします

データは、以下のような形式になります

"日付","摘要","入金金額","出金金額","残高","メモ"
"20240831","Visaデビット利用 ダイソー／ＮＦＣ 承認番号：450835 TID：584244293990318","","1100","97402",""
"20240830","振込  ストライプジヤパン（カ","19170","","106302",""

"日付","摘要","入金金額","出金金額","残高","メモ"

"20240831","Visaデビット利用ダイソー／ＮＦＣ承認番号：450835 TID：584244293990318","","1100","97402",""

"20240830","振込ストライプジヤパン（カ","19170","","106302",""

ファイルを扱う場合、文字コードも意識しないと行けないのですが

文字化けに注意しながら進めてみる

ファイル名を

inp_file.csv

とすると

＜?php
// CSVファイルを読み込みモードで開く
$fp = fopen("inp_data.csv", "r");
// ファイルを1行ずつ取得する
while (($line = fgetcsv($fp)) !== FALSE) {
//dump($line);
//print("1");
//print_r($line);
//print("2");
//print_r(mb_convert_encoding($line,"sjis","utf-8"));
//print("3");
//print_r(mb_convert_encoding($line,"utf-8","sjis"));
//print("4");
//print_r(mb_convert_encoding($line,"sjis","auto"));
//print("5");
//print_r(mb_convert_encoding($line,"utf-8","auto")); これで、上手く行った
//print("6");
	for ($c = 0; $c &lt; count($line); $c++) {
	$str = mb_convert_encoding($line[$c] ,"utf-8","auto");
// Call to undefined function mb_convert_encoding() 
// sudo apt-get install php-mbstring ダメ
// sudo apt-get install php7.4-mbstring　上手く行った
// Ubuntu 24.04にて
	print($str);		
	}
}
fclose($fp);
// 参考　https://qiita.com/web-serve/items/164329e512f6ff7e9bdc 
?>

＜?php

// CSVファイルを読み込みモードで開く

$fp = fopen("inp_data.csv", "r");

// ファイルを1行ずつ取得する

while (($line = fgetcsv($fp)) !== FALSE) {

//dump($line);

//print("1");

//print_r($line);

//print("2");

//print_r(mb_convert_encoding($line,"sjis","utf-8"));

//print("3");

//print_r(mb_convert_encoding($line,"utf-8","sjis"));

//print("4");

//print_r(mb_convert_encoding($line,"sjis","auto"));

//print("5");

//print_r(mb_convert_encoding($line,"utf-8","auto")); これで、上手く行った

//print("6");

for ($c = 0; $c < count($line); $c++) {

$str = mb_convert_encoding($line[$c] ,"utf-8","auto");

// Call to undefined function mb_convert_encoding()

// sudo apt-get install php-mbstring ダメ

// sudo apt-get install php7.4-mbstring　上手く行った

// Ubuntu 24.04にて

print($str);

}

fclose($fp);

// 参考　https://qiita.com/web-serve/items/164329e512f6ff7e9bdc

まずは、データベースを固定して

追加の文章を作ってみよう

insertとupdateがあるが全てinsertとしよう

ただし、以前にアップロードしたデータについては

除外することにする

１行目のヘッダーとカラムの対応表が必要になる

INSERT INTO テーブル名 (列名1, 列名2,…) VALUES (値1, 値2,…);

となるので、列名と値の対応が必要になる

列名を、ヘッダーに合わせて、順番を変更するか

あるいは、確定して、行く方法もあるが、順番は確定として

進めることにする

次に、重複を避ける方法として、レコードを特定するユニークなコードがあればいいが、必ずしもあるとは、限らないため

データの一致にて行うことにする

つまり、フィールドが全て、同じ場合は、見送りとし

カラムがわずかでも変わっていた場合には、新規に追加するものとする

INSERT INTO テーブル名 (列名1, 列名2,…) VALUES

まで、固定で、

(値1, 値2,…);

を、入力したデータにて変更するよになる

SQL文を、文字列で作成する方法もあるが、エラーチェックは、インジェクション処理などの処理を含まれた、処理を行う

データベース名
openbook

テーブル名
tp_gmo_corp

“日付”,”摘要”,”入金金額”,”出金金額”,”残高”,”メモ”

CREATE TABLE `tp_gmo_corp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`tp_datetime` VARCHAR(2000) NOT NULL COLLATE 'utf8mb4_general_ci',
	`tp_month` VARCHAR(100) NOT NULL DEFAULT '2024' COLLATE 'utf8mb4_general_ci',
	`tp_summary` VARCHAR(2000) NOT NULL COLLATE 'utf8mb4_general_ci',
	`tp_deposit` INT(11) NOT NULL DEFAULT '0',
	`tp_investment` INT(11) NOT NULL DEFAULT '0',
	`tp_balance` INT(11) NOT NULL DEFAULT '0',
	`tp_type` INT(11) NOT NULL DEFAULT '0',
	`tp_memo` VARCHAR(200) NOT NULL COLLATE 'utf8mb4_general_ci',
	`pub_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `tp_gmo_corp` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`tp_datetime` VARCHAR(2000) NOT NULL COLLATE 'utf8mb4_general_ci',

`tp_month` VARCHAR(100) NOT NULL DEFAULT '2024' COLLATE 'utf8mb4_general_ci',

`tp_summary` VARCHAR(2000) NOT NULL COLLATE 'utf8mb4_general_ci',

`tp_deposit` INT(11) NOT NULL DEFAULT '0',

`tp_investment` INT(11) NOT NULL DEFAULT '0',

`tp_balance` INT(11) NOT NULL DEFAULT '0',

`tp_type` INT(11) NOT NULL DEFAULT '0',

`tp_memo` VARCHAR(200) NOT NULL COLLATE 'utf8mb4_general_ci',

`pub_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`) USING BTREE

)

COLLATE='utf8mb4_general_ci'

ENGINE=InnoDB

;

データベースへの書き込み（insert）はで行う

文字化けしたので

mb_convert_encoding($line,”utf-8″,”auto”)

を、追加した所、エラーとなった

sudo apt-get install php-mbstring

で、行ったが、やはりダメ

sudo apt install php7.4-mbstring

で、上手く行った

次は

could not find driver

のエラーになった

sudo apt-get install php7-mysql

では、読み込めなかったので

sudo apt-get install php7.4-mysql

で、解消した

これで、CSVに従って書き込んでいたら

数値カラムのInsertでエラーになった

$culum=””;

で、数値のカラムに入れるとエラーになる

””のときは、0に書き換える必要があるとると

カラムの属性情報も管理しないといけない

手抜きは、できないようである

まずは、ハードコーディングで、サンプルを書き込む所まで

行ってみる

ファイルを開けて、追加してみる

＜?php
try {
$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
} catch (PDOException $e) {

    exit($e->getMessage());    

}

// ヘッダー
// "日付","摘要","入金金額","出金金額","残高","メモ"
// 実データ
// "20240831","Visaデビット利用 ダイソー／ＮＦＣ 承認番号：450835 TID：584244293990318","","1100","97402",""
// １行動作検証
$tp_datetime="20240831";
$tp_summary="Visaデビット利用 ダイソー／ＮＦＣ 承認番号：450835 TID：584244293990318__";
//$tp_deposit="";  数値のカラムだとエラーになる
$tp_deposit="0";
$tp_investment="1100";
$tp_balance="97402";
$tp_memo="";
$sql = 'insert into tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)';
$stmt = $pdo->prepare($sql);
$flag = $stmt->execute(array($tp_datetime,$tp_summary,$tp_deposit,$tp_investment,$tp_balance,$tp_memo));
// could not find driver
// sudo apt-get install php7-mysql  こちらはだめ
// sudo apt-get install php7.4-mysql　こちらで、できた
print($flag);

＜?php

try {

$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]);

} catch (PDOException $e) {

exit($e->getMessage());

}

// ヘッダー

// "日付","摘要","入金金額","出金金額","残高","メモ"

// 実データ

// "20240831","Visaデビット利用ダイソー／ＮＦＣ承認番号：450835 TID：584244293990318","","1100","97402",""

// １行動作検証

$tp_datetime="20240831";

$tp_summary="Visaデビット利用ダイソー／ＮＦＣ承認番号：450835 TID：584244293990318__";

//$tp_deposit=""; 数値のカラムだとエラーになる

$tp_deposit="0";

$tp_investment="1100";

$tp_balance="97402";

$tp_memo="";

$sql = 'insert into tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)';

$stmt = $pdo->prepare($sql);

$flag = $stmt->execute(array($tp_datetime,$tp_summary,$tp_deposit,$tp_investment,$tp_balance,$tp_memo));

// could not find driver

// sudo apt-get install php7-mysql こちらはだめ

// sudo apt-get install php7.4-mysql　こちらで、できた

print($flag);

２つの処理を合体してみよう

＜?php
try {
$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
} catch (PDOException $e) {

    exit($e->getMessage());    

}
// CSVファイルを読み込みモードで開く
$fp = fopen("inp_data.csv", "r");
// ファイルを1行ずつ取得する
$fl=0;
while (($line = fgetcsv($fp)) !== FALSE) {
	for ($c = 0; $c &lt; count($line); $c++) {
	$str = mb_convert_encoding($line[$c] ,"utf-8","auto");
	print($str);
	}	
	if($fl != 0){// ヘッダーは除外
		$tp_datetime="20240831";
		$tp_datetime=$line[0];
		$tp_summary="Visaデビット利用 ダイソー／ＮＦＣ 承認番号：450835 TID：584244293990318__";
		$tp_summary=mb_convert_encoding($line[1] ,"utf-8","auto");
		$tp_deposit="0";
		$tp_deposit=$line[2];
		if($line[2] == ''){
			$tp_deposit="0";
		}	
		$tp_investment="1100";
		$tp_investment=$line[3];
		if($line[3] == ''){
			$tp_investment="0";
		}	// intのカラムは全て行わないと行けない
		$tp_balance="97402";
		$tp_balance=$line[4];
		$tp_memo="";
		$tp_memo=$line[5];
		$sql = 'insert into tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)';
		$stmt = $pdo->prepare($sql);
		$flag = $stmt->execute(array($tp_datetime,$tp_summary,$tp_deposit,$tp_investment,$tp_balance,$tp_memo));
		print($flag);
		}	
	
$fl=1;
}
fclose($fp);
?>

＜?php

try {

$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]);

} catch (PDOException $e) {

exit($e->getMessage());

}

// CSVファイルを読み込みモードで開く

$fp = fopen("inp_data.csv", "r");

// ファイルを1行ずつ取得する

$fl=0;

while (($line = fgetcsv($fp)) !== FALSE) {

for ($c = 0; $c < count($line); $c++) {

$str = mb_convert_encoding($line[$c] ,"utf-8","auto");

print($str);

}

if($fl != 0){// ヘッダーは除外

$tp_datetime="20240831";

$tp_datetime=$line[0];

$tp_summary="Visaデビット利用ダイソー／ＮＦＣ承認番号：450835 TID：584244293990318__";

$tp_summary=mb_convert_encoding($line[1] ,"utf-8","auto");

$tp_deposit="0";

$tp_deposit=$line[2];

if($line[2] == ''){

$tp_deposit="0";

}

$tp_investment="1100";

$tp_investment=$line[3];

if($line[3] == ''){

$tp_investment="0";

} // intのカラムは全て行わないと行けない

$tp_balance="97402";

$tp_balance=$line[4];

$tp_memo="";

$tp_memo=$line[5];

$sql = 'insert into tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)';

$stmt = $pdo->prepare($sql);

$flag = $stmt->execute(array($tp_datetime,$tp_summary,$tp_deposit,$tp_investment,$tp_balance,$tp_memo));

print($flag);

}

$fl=1;

}

fclose($fp);

これで、複数行の挿入ができた

次に、重複チェックを行う

既に登録済の行は登録しないようにする

行を特定する、カラムがないので、全てのカラムの一致で、除外するようにしてみる

＜?php
try {
$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
} catch (PDOException $e) {

    exit($e->getMessage());    

}
// CSVファイルを読み込みモードで開く
$fp = fopen("inp_data.csv", "r");
// ファイルを1行ずつ取得する
$fl=0;
while (($line = fgetcsv($fp)) !== FALSE) {
	for ($c = 0; $c &lt; count($line); $c++) {
	$str = mb_convert_encoding($line[$c] ,"utf-8","auto");
	print($c);
	print("=");
	print($str);
	}	
	if($fl != 0){// ヘッダーは除外
		$tp_datetime="20240831";
		$tp_datetime=$line[0];
		$tp_summary="Visaデビット利用 ダイソー／ＮＦＣ 承認番号：450835 TID：584244293990318__";
		$tp_summary=mb_convert_encoding($line[1] ,"utf-8","auto");
		$tp_deposit="0";
		$tp_deposit=$line[2];
		if($line[2] == ''){
			$tp_deposit="0";
		}	
		$tp_investment="1100";
		$tp_investment=$line[3];
		if($line[3] == ''){
			$tp_investment="0";
		}	
		$tp_balance="97402";
		$tp_balance=$line[4];
		$tp_memo="";
		$tp_memo=$line[5];
//   ここから、登録済か調べてみる
		$sql="select count(*) from tp_gmo_corp where ";
		$sql=$sql."tp_datetime= :tp_datetime ";
		$sql=$sql."and tp_summary= :tp_summary ";
		$sql=$sql."and tp_deposit= :tp_deposit ";
		$sql=$sql."and tp_investment= :tp_investment ";
		$sql=$sql."and tp_balance= :tp_balance ";
		$sql=$sql."and tp_memo= :tp_memo ";
//		print($sql);
		$stmt = $pdo->prepare($sql);
		$stmt->bindValue(':tp_datetime', $tp_datetime , PDO::PARAM_STR);
		$stmt->bindValue(':tp_summary', $tp_summary , PDO::PARAM_STR);
		$stmt->bindValue(':tp_deposit', $tp_deposit , PDO::PARAM_INT);
		$stmt->bindValue(':tp_investment', $tp_investment , PDO::PARAM_INT);
		$stmt->bindValue(':tp_balance', $tp_balance , PDO::PARAM_INT);
		$stmt->bindValue(':tp_memo', $tp_memo , PDO::PARAM_STR);
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		// 参考　http://hono-wp.seesaa.net/article/411183815.html
		//      https://qiita.com/wakahara3/items/d7a3674eecd3b021a21e
		print_r($result);
		print($result[0]["count(*)"]);
		if($result[0]["count(*)"]==0){ //未登録なので、登録
			print("Write");
			$sql = 'insert into tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)';
			$stmt = $pdo->prepare($sql);
			$flag = $stmt->execute(array($tp_datetime,$tp_summary,$tp_deposit,$tp_investment,$tp_balance,$tp_memo));
			print($flag);
		}
		if($result[0]["count(*)"]=!0){  // 登録済なので、見送り
		print("Send");
		}
		}	
	
$fl=1;
}
fclose($fp);
?>

＜?php

try {

$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]);

} catch (PDOException $e) {

exit($e->getMessage());

}

// CSVファイルを読み込みモードで開く

$fp = fopen("inp_data.csv", "r");

// ファイルを1行ずつ取得する

$fl=0;

while (($line = fgetcsv($fp)) !== FALSE) {

for ($c = 0; $c < count($line); $c++) {

$str = mb_convert_encoding($line[$c] ,"utf-8","auto");

print($c);

print("=");

print($str);

}

if($fl != 0){// ヘッダーは除外

$tp_datetime="20240831";

$tp_datetime=$line[0];

$tp_summary="Visaデビット利用ダイソー／ＮＦＣ承認番号：450835 TID：584244293990318__";

$tp_summary=mb_convert_encoding($line[1] ,"utf-8","auto");

$tp_deposit="0";

$tp_deposit=$line[2];

if($line[2] == ''){

$tp_deposit="0";

}

$tp_investment="1100";

$tp_investment=$line[3];

if($line[3] == ''){

$tp_investment="0";

}

$tp_balance="97402";

$tp_balance=$line[4];

$tp_memo="";

$tp_memo=$line[5];

// ここから、登録済か調べてみる

$sql="select count(*) from tp_gmo_corp where ";

$sql=$sql."tp_datetime= :tp_datetime ";

$sql=$sql."and tp_summary= :tp_summary ";

$sql=$sql."and tp_deposit= :tp_deposit ";

$sql=$sql."and tp_investment= :tp_investment ";

$sql=$sql."and tp_balance= :tp_balance ";

$sql=$sql."and tp_memo= :tp_memo ";

// print($sql);

$stmt = $pdo->prepare($sql);

$stmt->bindValue(':tp_datetime', $tp_datetime , PDO::PARAM_STR);

$stmt->bindValue(':tp_summary', $tp_summary , PDO::PARAM_STR);

$stmt->bindValue(':tp_deposit', $tp_deposit , PDO::PARAM_INT);

$stmt->bindValue(':tp_investment', $tp_investment , PDO::PARAM_INT);

$stmt->bindValue(':tp_balance', $tp_balance , PDO::PARAM_INT);

$stmt->bindValue(':tp_memo', $tp_memo , PDO::PARAM_STR);

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 参考　http://hono-wp.seesaa.net/article/411183815.html

// https://qiita.com/wakahara3/items/d7a3674eecd3b021a21e

print_r($result);

print($result[0]["count(*)"]);

if($result[0]["count(*)"]==0){ //未登録なので、登録

print("Write");

$sql = 'insert into tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)';

$stmt = $pdo->prepare($sql);

$flag = $stmt->execute(array($tp_datetime,$tp_summary,$tp_deposit,$tp_investment,$tp_balance,$tp_memo));

print($flag);

}

if($result[0]["count(*)"]=!0){ // 登録済なので、見送り

print("Send");

}

$fl=1;

}

fclose($fp);

一応できたが、データ依存性が大きいので、これをいかに、汎用化するか

まずは、ヘッダーにより、フィールドの定義を行う

これをもとに、前回のプログラムを書き換えてみます

＜?php
$keyname="tp_gmo_corp"; //GOM法人口座
$keyname="tp_gmo_priv"; //GMO個人口座
$keyname="tp_stripe"; //Stripe
$keyname="tp_paypal"; //Pyapal

$keyname="tp_gmo_priv"; //　今回

// 縦型がいいか、横型がいいか　横型のORマッパー形式

$tabledic=array();
$tabledic["tp_gmo_corp"]["tabelename"]="tp_gmo_corp";
$tabledic["tp_gmo_corp"]["culumzok"]=array(1,1,0,0,0,1);
$tabledic["tp_gmo_corp"]["culumselect"]=array(1,1,1,1,1,1);
$tabledic["tp_gmo_corp"]["culumcheck"]=array(1,1,1,1,1,1);
$tabledic["tp_gmo_corp"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");
$tabledic["tp_gmo_corp"]["filename"]="gmo_corp.csv";

$tabledic["tp_gmo_priv"]["tabelename"]="tp_gmo_priv";
$tabledic["tp_gmo_priv"]["culumzok"]=array(1,1,0,0,0,1);
$tabledic["tp_gmo_priv"]["culumselect"]=array(1,1,1,1,1,1);
$tabledic["tp_gmo_priv"]["culumcheck"]=array(1,1,1,1,1,1);
$tabledic["tp_gmo_priv"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");
$tabledic["tp_gmo_priv"]["filename"]="gmo_priv.csv";

$tabledic["tp_stripe"]["tabelename"]="tp_stripe";
$tabledic["tp_stripe"]["culumzok"]=array(1,1,0,0,0,1);
$tabledic["tp_stripe"]["culumselect"]=array(1,1,1,1,1,1);
$tabledic["tp_stripe"]["culumcheck"]=array(1,1,1,1,1,1);
$tabledic["tp_stripe"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");
$tabledic["tp_stripe"]["filename"]="stripe.csv";

$tabledic["tp_paypal"]["tabelename"]="tp_paypal";
$tabledic["tp_paypal"]["culumzok"]=array(1,1,0,0,0,1);
$tabledic["tp_paypal"]["culumselect"]=array(1,1,1,1,1,1);
$tabledic["tp_paypal"]["culumcheck"]=array(1,1,1,1,1,1);
$tabledic["tp_paypal"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");
$tabledic["tp_paypal"]["filename"]="paypal.csv";

$tabelename=$tabledic[$keyname]["tabelename"];
$culumzok=$tabledic[$keyname]["culumzok"];
$culumselect=$tabledic[$keyname]["culumselect"];
$culumcheck=$tabledic[$keyname]["culumcheck"];
$culumname=$tabledic[$keyname]["culumname"];
$filename=$tabledic[$keyname]["filename"];

print_r($tabelename);
print("----\n");
print_r($culumzok);
print("----\n");
print_r($culumselect);
print("----\n");
print_r($culumcheck);
print("----\n");
print_r($culumname);
print("----\n");
print_r($filename);
print("----\n");
//exit("ok");

// 2回目はandがつこので、 最初だけの処理で行う
// foreach php 最初だけ
// 参考　https://qiita.com/_hiro_dev/items/fc48722eb518c6382895
$sql_select="select count(*) from ".$tabelename." where ";
		foreach ($culumname as $index => $culum) {
			if(!($index === array_key_first($culumname))){ // １回目以降
				$sql_select=$sql_select."and ";
			}
			$sql_select=$sql_select.$culum."= :".$culum." ";
		}
			print($sql_select);

//$Sql = "insert into ".$tabelename."tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)";
$sql_insert="insert into ".$tabelename."(";
$sql_insert2="";
		foreach ($culumname as $index => $culum) {
			if(!($index === array_key_first($culumname))){ // １回目以降
				$sql_insert=$sql_insert.", ";
				$sql_insert2=$sql_insert2.",";
			}
			$sql_insert=$sql_insert.$culum;
			$sql_insert2=$sql_insert2."?";
		}
		$sql_insert=$sql_insert.") values (".$sql_insert2.")";
print($sql_select);
print("\n");
print($sql_insert);
//exit("ok");

try {
$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
} catch (PDOException $e) {

    exit($e->getMessage());    
}
// CSVファイルを読み込みモードで開く

$file_name="inp_data.csv";
//$file_name="inp_data_.csv";
$file_name=$filename;

$fp = fopen($file_name, "r");

// ファイルを1行ずつ取得する
$fl=0;
while (($line = fgetcsv($fp)) !== FALSE) {
	for ($c = 0; $c &lt; count($line); $c++) {
	$str = mb_convert_encoding($line[$c] ,"utf-8","sjis");
	print("---------\n");
	print($str);
	print("---------\n");
        if($c==2){
	print($c);
	print("=");
	print($str);
	print(":");
     }
	}	
	if($fl != 0){// ヘッダーは除外
			$stmt = $pdo->prepare($sql_select);
			foreach ($culumname as $index => $culum) {
			if ($culumzok[$index]==1){
						$stmt->bindValue(':'.$culum, mb_convert_encoding($line[$index],"utf-8","sjis") , PDO::PARAM_STR);
						}else{
						if($line[$index]==''){
						$stmt->bindValue(':'.$culum, "0" , PDO::PARAM_INT);
						}else{
						$stmt->bindValue(':'.$culum, $line[$index] , PDO::PARAM_INT);
						}
						}
			}
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		// 参考　http://hono-wp.seesaa.net/article/411183815.html
		//      https://qiita.com/wakahara3/items/d7a3674eecd3b021a21e
		print_r($result);
		print($result[0]["count(*)"]);
		if($result[0]["count(*)"]==0){
			print("Write");
			$stmt = $pdo->prepare($sql_insert);
			$mapdata=array();
			foreach ($culumname as $index => $culum) {
//			$setdata=$line[$index];
			if ($culumzok[$index]==1) {  // 文字列
			$setdata=mb_convert_encoding($line[$index],"utf-8","sjis") ;
			}else{ //数値
			if ($line[$index]==""){
			$setdata="0";}else{
			$setdata=$line[$index];
			}}
			array_push($mapdata,$setdata);
			}
			print_r($mapdata);
			$flag = $stmt->execute($mapdata);
			print($flag);
		}
		if($result[0]["count(*)"]=!0){
		print("Send");
		}
		}	
	
$fl=1;
}
fclose($fp);
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

＜?php

$keyname="tp_gmo_corp"; //GOM法人口座

$keyname="tp_gmo_priv"; //GMO個人口座

$keyname="tp_stripe"; //Stripe

$keyname="tp_paypal"; //Pyapal

$keyname="tp_gmo_priv"; //　今回

// 縦型がいいか、横型がいいか　横型のORマッパー形式

$tabledic=array();

$tabledic["tp_gmo_corp"]["tabelename"]="tp_gmo_corp";

$tabledic["tp_gmo_corp"]["culumzok"]=array(1,1,0,0,0,1);

$tabledic["tp_gmo_corp"]["culumselect"]=array(1,1,1,1,1,1);

$tabledic["tp_gmo_corp"]["culumcheck"]=array(1,1,1,1,1,1);

$tabledic["tp_gmo_corp"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");

$tabledic["tp_gmo_corp"]["filename"]="gmo_corp.csv";

$tabledic["tp_gmo_priv"]["tabelename"]="tp_gmo_priv";

$tabledic["tp_gmo_priv"]["culumzok"]=array(1,1,0,0,0,1);

$tabledic["tp_gmo_priv"]["culumselect"]=array(1,1,1,1,1,1);

$tabledic["tp_gmo_priv"]["culumcheck"]=array(1,1,1,1,1,1);

$tabledic["tp_gmo_priv"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");

$tabledic["tp_gmo_priv"]["filename"]="gmo_priv.csv";

$tabledic["tp_stripe"]["tabelename"]="tp_stripe";

$tabledic["tp_stripe"]["culumzok"]=array(1,1,0,0,0,1);

$tabledic["tp_stripe"]["culumselect"]=array(1,1,1,1,1,1);

$tabledic["tp_stripe"]["culumcheck"]=array(1,1,1,1,1,1);

$tabledic["tp_stripe"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");

$tabledic["tp_stripe"]["filename"]="stripe.csv";

$tabledic["tp_paypal"]["tabelename"]="tp_paypal";

$tabledic["tp_paypal"]["culumzok"]=array(1,1,0,0,0,1);

$tabledic["tp_paypal"]["culumselect"]=array(1,1,1,1,1,1);

$tabledic["tp_paypal"]["culumcheck"]=array(1,1,1,1,1,1);

$tabledic["tp_paypal"]["culumname"]=array("tp_datetime","tp_summary","tp_deposit","tp_investment","tp_balance","tp_memo");

$tabledic["tp_paypal"]["filename"]="paypal.csv";

$tabelename=$tabledic[$keyname]["tabelename"];

$culumzok=$tabledic[$keyname]["culumzok"];

$culumselect=$tabledic[$keyname]["culumselect"];

$culumcheck=$tabledic[$keyname]["culumcheck"];

$culumname=$tabledic[$keyname]["culumname"];

$filename=$tabledic[$keyname]["filename"];

print_r($tabelename);

print("----\n");

print_r($culumzok);

print("----\n");

print_r($culumselect);

print("----\n");

print_r($culumcheck);

print("----\n");

print_r($culumname);

print("----\n");

print_r($filename);

print("----\n");

//exit("ok");

// 2回目はandがつこので、最初だけの処理で行う

// foreach php 最初だけ

// 参考　https://qiita.com/_hiro_dev/items/fc48722eb518c6382895

$sql_select="select count(*) from ".$tabelename." where ";

foreach ($culumname as $index => $culum) {

if(!($index === array_key_first($culumname))){ // １回目以降

$sql_select=$sql_select."and ";

}

$sql_select=$sql_select.$culum."= :".$culum." ";

}

print($sql_select);

//$Sql = "insert into ".$tabelename."tp_gmo_corp (tp_datetime,tp_summary,tp_deposit,tp_investment,tp_balance,tp_memo) values (?,?,?,?,?,?)";

$sql_insert="insert into ".$tabelename."(";

$sql_insert2="";

foreach ($culumname as $index => $culum) {

if(!($index === array_key_first($culumname))){ // １回目以降

$sql_insert=$sql_insert.", ";

$sql_insert2=$sql_insert2.",";

}

$sql_insert=$sql_insert.$culum;

$sql_insert2=$sql_insert2."?";

}

$sql_insert=$sql_insert.") values (".$sql_insert2.")";

print($sql_select);

print("\n");

print($sql_insert);

//exit("ok");

try {

$pdo = new PDO('mysql:dbname=openbook;host=xxx.xxx.xxx.xxx;charset=utf8mb4', 'acountname', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]);

} catch (PDOException $e) {

exit($e->getMessage());

}

// CSVファイルを読み込みモードで開く

$file_name="inp_data.csv";

//$file_name="inp_data_.csv";

$file_name=$filename;

$fp = fopen($file_name, "r");

// ファイルを1行ずつ取得する

$fl=0;

while (($line = fgetcsv($fp)) !== FALSE) {

for ($c = 0; $c < count($line); $c++) {

$str = mb_convert_encoding($line[$c] ,"utf-8","sjis");

print("---------\n");

print($str);

print("---------\n");

if($c==2){

print($c);

print("=");

print($str);

print(":");

}

if($fl != 0){// ヘッダーは除外

$stmt = $pdo->prepare($sql_select);

foreach ($culumname as $index => $culum) {

if ($culumzok[$index]==1){

$stmt->bindValue(':'.$culum, mb_convert_encoding($line[$index],"utf-8","sjis") , PDO::PARAM_STR);

}else{

if($line[$index]==''){

$stmt->bindValue(':'.$culum, "0" , PDO::PARAM_INT);

}else{

$stmt->bindValue(':'.$culum, $line[$index] , PDO::PARAM_INT);

}

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 参考　http://hono-wp.seesaa.net/article/411183815.html

// https://qiita.com/wakahara3/items/d7a3674eecd3b021a21e

print_r($result);

print($result[0]["count(*)"]);

if($result[0]["count(*)"]==0){

print("Write");

$stmt = $pdo->prepare($sql_insert);

$mapdata=array();

foreach ($culumname as $index => $culum) {

// $setdata=$line[$index];

if ($culumzok[$index]==1) { // 文字列

$setdata=mb_convert_encoding($line[$index],"utf-8","sjis") ;

}else{ //数値

if ($line[$index]==""){

$setdata="0";}else{

$setdata=$line[$index];

}}

array_push($mapdata,$setdata);

}

print_r($mapdata);

$flag = $stmt->execute($mapdata);

print($flag);

}

if($result[0]["count(*)"]=!0){

print("Send");

}

$fl=1;

}

fclose($fp);

重複検索のカラムの範囲と、挿入するカラムの範囲の処理はこれから

Stripのときには、不要なカラムが多いのと、ユニークカラムがあるので、この時に活用していく予定です

データディクショナリーは、Jsonに落として、外部にする事もできそうです

既存のデータを調べて、重複部分から、テーブルを特定する方法もあるが

当面は、この方法で

また、データディクションリーから、新規テーブルも作れるが

はじめの１回なので、後でいいかな

x-serverメールをトリガーにして、プログラムを起動

2024年9月8日未分類haruki

メールの活用して、情報の管理をすると、入力の場所が明確になって

わかりやすい

メールの内容を、プログラムで確認する方法として

メールの受信をトリガーにして、プログラムを起動する方法がある

以下のページを参考にして、作ってみた

・XSERVER : メール受信をトリガーにして処理を起動
https://raspi.ryo.sc/xserver-mail-trigger/

・メール受信をトリガーにして任意のプログラムを実行する
https://revolutionary.hatenablog.jp/entry/20090226/p1

・XSERVERのメールフィルタでメール転送してみよう
https://seous.info/jobs/internet/1553#google_vignette

・メール受信をトリガーにPHP発動（ネットオウル・ファイアバード）
https://ameblo.jp/tencommon/entry-11493595921.html

・メールの振り分け
https://www.xserver.ne.jp/manual/man_mail_sorting.php

まずは、簡単なプログラムとして

メール受信だけを、確認するために

PHPのプログラムで

ログファイルを書き出してみる

PHPのプログラムは

test.phpとして、logのフォルダーを作っておく

&lt;?php
$f ="./log/app_".date('Ymd_Hi')."0.log";
error_log(date('Ymd_His')."\n",3, $f);

<?php

$f ="./log/app_".date('Ymd_Hi')."0.log";

error_log(date('Ymd_His')."\n",3, $f);

受取用のメールアカウントも作っておく

メールアカウントは

auto@xxxxxxxx.xsrv.jp

としておく

メールアカウントができたので、左のサイドメニューの「メールの振り分け」を選択

メールを送信して見るが、ログが吐き出さない

直接ブラウザから起動するとログを吐き出す

ログのパスをルートからのパスにしたら上手く行った

&lt;?php
$f ="./log/app_".date('Ymd_Hi')."0.log";
$f ="/home/xxx/xxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."0.log";
error_log(date('Ymd_His')."\n",3, $f);

<?php

$f ="./log/app_".date('Ymd_Hi')."0.log";

$f ="/home/xxx/xxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."0.log";

error_log(date('Ymd_His')."\n",3, $f);

次は、せっかくメールで送られて来るので

メールの情報を取得してみよう

ページ
「メール本文を取得して本文を変更する」
https://revolutionary.hatenablog.jp/entry/20090312/p1

https://www.webdata.jp/tech/php02.php
【PHP】メールの受信

https://shiranuik.hatenablog.jp/entry/20100624/1277391796
メールを分解する。の解説。

http://www.aiwake.co.jp/modules/bulletin/index.php?page=article&storyid=4
PHP : 受信メールの添付ファイルを保存する

https://qiita.com/ga_ku/items/2f2640905f91aeb82d6c
受信したメールをPHPで解析してDBに格納する

などを参考に
PEARを使って、メールの内容を取得してみる

require_once '/home/xxxx/xxxx.xsrv.jp/public_html/mimeDecode.php';
  // メールデータ取得
  $params['include_bodies'] = true;
  $params['decode_bodies']  = true;
  $params['decode_headers'] = true;
  $params['input'] = file_get_contents("php://stdin");
  $params['crlf'] = "\r\n";
  $structure = Mail_mimeDecode::decode($params);

  //送信者のメールアドレスを抽出
  $mail = $structure->headers['from'];
  $mail = addslashes($mail);
  $mail = str_replace('"','',$mail);

  //署名付きの場合の処理を追加
  preg_match("/&lt;.*>/",$mail,$str);
  if($str[0]!=""){
    $str=substr($str[0],1,strlen($str[0])-2);
    $mail = $str;
  }

require_once '/home/xxxx/xxxx.xsrv.jp/public_html/mimeDecode.php';

// メールデータ取得

$params['include_bodies'] = true;

$params['decode_bodies'] = true;

$params['decode_headers'] = true;

$params['input'] = file_get_contents("php://stdin");

$params['crlf'] = "\r\n";

$structure = Mail_mimeDecode::decode($params);

//送信者のメールアドレスを抽出

$mail = $structure->headers['from'];

$mail = addslashes($mail);

$mail = str_replace('"','',$mail);

//署名付きの場合の処理を追加

preg_match("/<.*>/",$mail,$str);

if($str[0]!=""){

$str=substr($str[0],1,strlen($str[0])-2);

$mail = $str;

}

これで、メールの内容が取得できた

添付ファイルを、取得して、格納したいが、ファイル名の取得が必要

            if($type != "jpeg" and $type != "jpg"){
              continue;
            }
            $filename = $part->filename;

if($type != "jpeg" and $type != "jpg"){

continue;

}

$filename = $part->filename;

これで、取得できた

行いたいのは、csvのファイルを、複数添付したものを、取得し処理したい

（ｄｂへの追加アップロード）

どんなないよか調べてみる

    case "multipart":  // マルチパート(画像付き)
      foreach($structure->parts as $part){
      $ff=$part->headers->content-disposition;
      $f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."03.log";
      error_log($ff, 3, $f);
      
      $result = print_r($part, true);
      
      $f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."04.log";
      error_log($result, 3, $f);

case "multipart": // マルチパート(画像付き)

foreach($structure->parts as $part){

$ff=$part->headers->content-disposition;

$f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."03.log";

error_log($ff, 3, $f);

$result = print_r($part, true);

$f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."04.log";

error_log($result, 3, $f);

内容を確認すると

stdClass Object
(
    [headers] => Array
        (
            [content-type] => text/csv; charset="Shift_JIS"; name="statement-102-1-1186829.csv"
            [content-disposition] => attachment; filename="statement-102-1-1186829.csv"
            [content-transfer-encoding] => base64
            [content-id] => &lt;f_m0tegpve0>
            [x-attachment-id] => f_m0tegpve0
        )

    [ctype_primary] => text
    [ctype_secondary] => csv
    [ctype_parameters] => Array
        (
            [charset] => Shift_JIS
            [name] => statement-102-1-1186829.csv
        )

stdClass Object

(

[headers] => Array

(

[content-type] => text/csv; charset="Shift_JIS"; name="statement-102-1-1186829.csv"

[content-disposition] => attachment; filename="statement-102-1-1186829.csv"

[content-transfer-encoding] => base64

[content-id] => <f_m0tegpve0>

[x-attachment-id] => f_m0tegpve0

)

[ctype_primary] => text

[ctype_secondary] => csv

[ctype_parameters] => Array

(

[charset] => Shift_JIS

[name] => statement-102-1-1186829.csv

)

内容が
 [ctype_secondary] => csv
なので、 
ctype_secondaryを取得して
csv
で、分岐して
ファイル名を
 [ctype_parameters] => Array
        (
            [name] => statement-102-1-1186829.csv
で、行えば良さそう

内容が

[ctype_secondary] => csv

なので、

ctype_secondaryを取得して

csv

で、分岐して

ファイル名を

[ctype_parameters] => Array

(

[name] => statement-102-1-1186829.csv

で、行えば良さそう

ファイル名は

       $filename=$part->ctype_parameters->name;

1	$filename=$part->ctype_parameters->name;

でいけるはずだが、取り込めない

四苦八苦したあと

           $filename=$part->ctype_parameters["name"];

1	$filename=$part->ctype_parameters["name"];

で、取り込めた

&lt;?php
require_once '/home/xxxx/xxxx.xsrv.jp/public_html/mimeDecode.php';
  // メールデータ取得
  $params['include_bodies'] = true;
  $params['decode_bodies']  = true;
  $params['decode_headers'] = true;
  $params['input'] = file_get_contents("php://stdin");
  $params['crlf'] = "\r\n";
  $structure = Mail_mimeDecode::decode($params);

  //送信者のメールアドレスを抽出
  $mail = $structure->headers['from'];
  $mail = addslashes($mail);
  $mail = str_replace('"','',$mail);

  //署名付きの場合の処理を追加
  preg_match("/&lt;.*>/",$mail,$str);
  if($str[0]!=""){
    $str=substr($str[0],1,strlen($str[0])-2);
    $mail = $str;
  }
  /*
   *「$structure->headers['to']」で送信元のメールアドレスも取得できます。
   */

  // 件名を取得
  $diary_subject = $structure->headers['subject'];
  switch(strtolower($structure->ctype_primary)){
    case "text": // シングルパート(テキストのみ)
      $diary_body = $structure->body;
      break;
    case "multipart":  // マルチパート(画像付き)
      foreach($structure->parts as $part){
        switch(strtolower($part->ctype_primary)){
          case "text": // テキスト
            $type = strtolower($part->ctype_secondary);
            if($type == "csv"){
             $filename="test.csv";  // 固定だと上手く行く
             $filename=$part->ctype_parameters->name; //　->nameが取り込めない
             $filename=$part->ctype_parameters["name"]; //　上手く行った
             $length = strlen( $part->body );
             fwrite( $fp, $part->body, $length );
             fclose( $fp );
             break;
            }
            $diary_body = $part->body;
            break;
          case "image": // 画像
            //画像の拡張子を取得する(小文字に変換
            $type = strtolower($part->ctype_secondary);
            //JPEGチェック（GIFやPNG形式の画像チェックなども可
            if($type != "jpeg" and $type != "jpg"){
              continue;
            }
            $filename = $part->filename;
            $fp = fopen("/home/xxxx/xxxx.xsrv.jp/public_html/auto/tmp/" .$filename,"w" );
            $length = strlen( $part->body );
            fwrite( $fp, $part->body, $length );
            fclose( $fp );
            break;
        }
      }
    break;
    default:
    $diary_body = "";
  }
  /*
   * 取得したメールアドレス、タイトル、本文、画像を使用してデータベースなどに取り込む
   */

$f ="./app_".date('Ymd_Hi')."1.log";
$f ="./log/app_".date('Ymd_Hi')."0.log";
$f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."0.log";
error_log($diary_subject , 3, $f);
// フルパスで指定しないとダメなようだ
$f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."0.json";
error_log(json_encode($structure), 3, $f);  //　添付ファイルがないと、解析ができる、ダウンロードしてFierFoxで開くと解析ができる

?>

<?php

require_once '/home/xxxx/xxxx.xsrv.jp/public_html/mimeDecode.php';

// メールデータ取得

$params['include_bodies'] = true;

$params['decode_bodies'] = true;

$params['decode_headers'] = true;

$params['input'] = file_get_contents("php://stdin");

$params['crlf'] = "\r\n";

$structure = Mail_mimeDecode::decode($params);

//送信者のメールアドレスを抽出

$mail = $structure->headers['from'];

$mail = addslashes($mail);

$mail = str_replace('"','',$mail);

//署名付きの場合の処理を追加

preg_match("/<.*>/",$mail,$str);

if($str[0]!=""){

$str=substr($str[0],1,strlen($str[0])-2);

$mail = $str;

}

*「$structure->headers['to']」で送信元のメールアドレスも取得できます。

// 件名を取得

$diary_subject = $structure->headers['subject'];

switch(strtolower($structure->ctype_primary)){

case "text": // シングルパート(テキストのみ)

$diary_body = $structure->body;

break;

case "multipart": // マルチパート(画像付き)

foreach($structure->parts as $part){

switch(strtolower($part->ctype_primary)){

case "text": // テキスト

$type = strtolower($part->ctype_secondary);

if($type == "csv"){

$filename="test.csv"; // 固定だと上手く行く

$filename=$part->ctype_parameters->name; //　->nameが取り込めない

$filename=$part->ctype_parameters["name"]; //　上手く行った

$length = strlen( $part->body );

fwrite( $fp, $part->body, $length );

fclose( $fp );

break;

}

$diary_body = $part->body;

break;

case "image": // 画像

//画像の拡張子を取得する(小文字に変換

$type = strtolower($part->ctype_secondary);

//JPEGチェック（GIFやPNG形式の画像チェックなども可

if($type != "jpeg" and $type != "jpg"){

continue;

}

$filename = $part->filename;

$fp = fopen("/home/xxxx/xxxx.xsrv.jp/public_html/auto/tmp/" .$filename,"w" );

$length = strlen( $part->body );

fwrite( $fp, $part->body, $length );

fclose( $fp );

break;

}

break;

default:

$diary_body = "";

}

* 取得したメールアドレス、タイトル、本文、画像を使用してデータベースなどに取り込む

$f ="./app_".date('Ymd_Hi')."1.log";

$f ="./log/app_".date('Ymd_Hi')."0.log";

$f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."0.log";

error_log($diary_subject , 3, $f);

// フルパスで指定しないとダメなようだ

$f ="/home/xxxx/xxxx.xsrv.jp/public_html/auto/log/app_".date('Ymd_Hi')."0.json";

error_log(json_encode($structure), 3, $f); //　添付ファイルがないと、解析ができる、ダウンロードしてFierFoxで開くと解析ができる

複数の添付ファイルを付けても、処理をしてくれた

後は、一連の操作の中で、DBへ書き込む処理を追加していく

ネット銀行（GMOあおぞら銀行）や、Stripeなどから、CSVをダウンロードして、メールで添付すると、自動的に、月次決算を集計するものにしていきたい

当面、自分だけで使うので、セキュリティ要件は緩めになっています

chatworkの投稿データをAPIで取得

2024年9月5日未分類haruki

Chatworkでのやり取りを使って

モチベーション（やる気）を出すために

案件の簡単な進捗管理を作ってみたく思った

ChatWorkのデータを取得してみよう

チャットワークでAPIで投稿内容を取得する

ttps://developer.chatwork.com/docs/getting-started

Chatwork画面右上の「利用者名」をクリックして表示されるメニューの「サービス連携」を選択してください。
サービス連携画面が開きますので、左側のメニューから「APIトークン」を選択してください。とのこと

まずは、APIトークンの取得する

APIの動作確認には、まずは、Curlがお勧めなので

ttps://qiita.com/n0bisuke/items/25813fba8120451d40da

を、参考に

curl -X GET -H “X-ChatWorkToken: 自分のAPIトークン” “https://api.chatwork.com/v1/rooms/{room_id}/messages?force=0”

ルームIDがあれば、チャンネルのルームの情報が取得できそう

{room_id}
は、
チャンネルを開いた時の
https://www.chatwork.com/#!rid36010xxxx
idのあとの数字（現在は９桁）

まずは、curlで、投げてみる

curl -X GET -H “X-ChatWorkToken: 自分のAPIトークン” “https://api.chatwork.com/v1/rooms/{room_id}/messages?force=0”

{“errors”:[“Chatwork API v1 is now obsolete. Please use the latest version.”]}

で、怒られた

ttps://developer.chatwork.com/reference/get-rooms-room_id-messages

本家で調べたら

ttps://api.chatwork.com/v2/rooms/{room_id}/messages

との事

v2でないと、行けないよう

curl -X GET -H “X-ChatWorkToken: 自分のAPIトークン” “https://api.chatwork.com/v2/rooms/{room_id}/messages?force=0”

よっしゃ！！つながった。

Jsonは、ブラウザで解析ができるので

curl -X GET -H “X-ChatWorkToken: 自分のAPIトークン” “https://api.chatwork.com/v2/rooms/{room_id}/messages?force=0″　> chatwork.json

と、jsonの拡張子で吐き出して

ChromeでなくFireFoxで表示しましょう。

見れた。

投稿の１００件が取り込めている

これで行けそう

タイムスタンプが send_timeが１０桁の数字
これを変換ししないと

これが、投稿時刻の年月日時分のはず

Googleで
chatwork api timestamp
で、検索

ttps://freeelover.com/2022/09/gas/gas%E6%B4%BB%E7%94%A8%E6%B3%95/1130/
ttps://www.shanaidx.com/chatwork_task/
// limit = new Date(json[i].limit_time * 1000 ); 　 // UNIXTIMEを変換してdateにセット

こんなのがあったので、UNIXTIMEというものか？

Googleで
UNIXTIME から　日付に　PHP
で、検索

ttps://onga-tec.hatenadiary.jp/entry/2016/08/19/PHP_unixtime%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%9F%E6%97%A5%E4%BB%98%E3%81%AE%E5%A4%89%E6%8F%9B%E3%83%95%E3%82%A9%E3%83%BC%E3%83%9E%E3%83%83%E3%83%88

にあったので、
これで、行きそう。

現在のunixtimeを求める。
echo time();

unixtimeを指定の日付形式にフォーマット。
echo date('Y/m/d H:i:s', time());

現在のunixtimeを求める。

echo time();

unixtimeを指定の日付形式にフォーマット。

echo date('Y/m/d H:i:s', time());

PHPで、直接確認するために

Googleで
コマンドラインで PHP を実行
で、検索

ttps://www.php.net/manual/ja/features.commandline.usage.php

php -r ‘print_r(get_defined_constants());’
こんなことできるんだ

php -r ‘echo time();’
1725521712

php -r ‘echo date(‘Y/m/d H:i:s’, time());’

エラーになった

’が重なったら、ダメですね

php -r “echo date(‘Y/m/d H:i:s’, time());”

表示してくれた

APIで取り込んだデータも

１０桁なので、そのままいけそう

取り込んだ、データで、変換してみる

php -r “echo date(‘Y/m/d H:i:s’, 1725106055);”

上手く行った

準備ができたので、まずは

PHPを使って、結果を、GoogleSpreadSheetに貼り付けるものを、作って行こう

それから、データベース（Mysql）へ、吐き出すプログラムをPHPで作って、進捗管理に使って行こうと思う

とりあえず、吐き出したJsonを使って、CSVを書き出してみた。

これで、ExcelやGoogleSpreadSheetには、読み込める

&lt;?php
$jsonUrl = "chatwork.json"; //JSONファイルの場所とファイル名を記述
if (file_exists($jsonUrl)) {
	$json = file_get_contents($jsonUrl);
	$obj = json_decode($json, true);
        $data="";
        for ($i = 0; $i &lt; count($obj); $i++) {
        $data=$data.'"_'.$obj[$i]["message_id"].'","'.date('Y/m/d H:i', $obj[$i]["send_time"]).'","'.$obj[$i]["account"]["name"].'","'.$obj[$i]["body"].'"'."\n";
        }
        print($data);
	$fname="outdata.csv";
	$output_fh = fopen($fname, "w");
	fwrite($output_fh, $data);
	fclose($output_fh);	
	}

<?php

$jsonUrl = "chatwork.json"; //JSONファイルの場所とファイル名を記述

if (file_exists($jsonUrl)) {

$json = file_get_contents($jsonUrl);

$obj = json_decode($json, true);

$data="";

for ($i = 0; $i < count($obj); $i++) {

$data=$data.'"_'.$obj[$i]["message_id"].'","'.date('Y/m/d H:i', $obj[$i]["send_time"]).'","'.$obj[$i]["account"]["name"].'","'.$obj[$i]["body"].'"'."\n";

}

print($data);

$fname="outdata.csv";

$output_fh = fopen($fname, "w");

fwrite($output_fh, $data);

fclose($output_fh);

}

2回目の呼び出しは

forceintegerDefaults to 0

forceはドキュメントより

強制的に最大件数まで取得するかどうか。
0を指定した場合（既定）は前回取得分からの差分のみを返しますが、1を指定した場合は強制的に最新のメッセージを最大100件まで取得します。

の引数は０だと、前回の続きなので、繰り返すと返信がなくなる

返信がない場合、１にするといい

余談ですが

Unixtime
1970年からの経過秒数
なんだ。。

php -r “echo date(‘Y/m/d H:i:s’, 9999999999);”

2286/11/21 02:46:39

260年後には、大騒ぎになるのかな？

残念ながら、そこまでは、生きて、立ち会うことは、少し難しそうですが。

プログラムを楽しもう

実際にプログラムを作り、スキルを磨いていきましょう。

月別アーカイブ: 2024年9月

Google Spread Sheetでデータベスを作る

PHPでCSVファイルによりMysqlを更新

x-serverメールをトリガーにして、プログラムを起動

chatworkの投稿データをAPIで取得