Excel Mapping

VLOOKUP
-> In a table created in Excel, data is arranged vertically(rows) and horizontally(columns). From the table, hear that “extract data corresponding to a specific value”, and think the first thing that comes to mind is the “VLOOKUP(Buoy lookup) function.”

If want to process data in Excel, when write in a program, can we use in functions processed by excel itself together??

hash variable

A hash, also called an associative array, is an array in which “key” and “values” are associated as a pair. However, the array itself is characterized by being unordered. A hash variable is called a hash variable, which starts with %(percent) + 1 alphabetic character, and can use numbers, alphabetic characters and underscores (_) thereafter. Also, because case is distinguished, % and % A, for example, are treated as different things.

%fruit = ("red"=>"apple", "yellow"=>"banana");
print "$fruit{'red'}\n";

$fruit{"red"} = "apple";
$fruit{"yello"} = "banana";

%fruit = (red => "apple", yellow => "banana", purple => "grape");
@file = keys %fruit;

@file = values %fruit;
print "@file\n";

($key, $val) = each %fruit;
print "$key: $val\n";

Exclusive control

Exclusive control is the exclusive control of a resource when a conflict arises due to simultaneous access from multiple processes to shared resources that can be used by multiple processes in the execution of a computer program. While it is used as it is, it means the things of processing that maintains consistency by making other processes unavailable. Also called mutual exclusion or mutual exclusion. The case where up to k processes may access shared resources is called k-mutual exclusion.

Datatype: Time

The TIME type is a data type used to store time. The format in which ‘HH:MM:SS’ is the basis when inputting time as a string.

here is a sample.

mysql> create table timetest(
    -> id int auto_increment primary key,
    -> starttime time,
    -> endtime time
    -> )
    -> ;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from timetest;
Empty set (0.00 sec)

mysql> describe timetest;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| starttime | time    | YES  |     | NULL    |                |
| endtime   | time    | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.07 sec)

mysql> insert into timetest(starttime, endtime) values('08:00', '12:00');
Query OK, 1 row affected (0.07 sec)

mysql> select * from timtetest;
ERROR 1146 (42S02): Table 'test.timtetest' doesn't exist
mysql> select * from timetest;
+----+-----------+----------+
| id | starttime | endtime  |
+----+-----------+----------+
|  1 | 08:00:00  | 12:00:00 |
+----+-----------+----------+
1 row in set (0.00 sec)

I see.

Register the current time in mysql datatime using PDO

From the PHP point of view, MySQL’s datetime type is just a string, so it is OK if you convert the current time to MySQL’s datetime type format using current time with date() and class DateTime.

Here is a solution.

$stmt->bindParam(':created_at',date('Y-m-d H:i:s'), PDO::PARAM_STR);

Connect and write

$last_name = $_POST['last_name'];
$first_name = $_POST['first_name'];
$flast_name = $_POST['flast_name'];
$ffirst_name = $_POST['ffirst_name'];
$birth = $_POST['birth'];
$mail = $_POST['mail'];
$tel = $_POST['tel'];
$job_place = $_POST['job-place'];
$salary = $_POST['salary'];
$filename = $_POST['filename'];
$upload = $_POST['upload'];

try {
	$pdo = new PDO('mysql:host=localhost;dbname=engineer;charset=utf8','root','',array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e){
	exit('データベース接続失敗'. $e->getMessage());
}
$stmt = $pdo -> prepare("INSERT INTO register(last_name, first_name, flast_name, ffirst_name, birth, mail, tel, job_place, salary, filename, upload, created_at) values(:last_name, :first_name, :flast_name, :ffirst_name, :birth, :mail, :tel, :job_place, :salary, :filename, :upload, :created_at)");
$stmt->bindParam(':last_name',$last_name, PDO::PARAM_STR);
$stmt->bindParam(':first_name',$first_name, PDO::PARAM_STR);
$stmt->bindParam(':flast_name',$flast_name, PDO::PARAM_STR);
$stmt->bindParam(':ffirst_name',$ffirst_name, PDO::PARAM_STR);
$stmt->bindParam(':birth',$birth, PDO::PARAM_STR);
$stmt->bindParam(':mail',$mail, PDO::PARAM_STR);
$stmt->bindParam(':tel',$tel, PDO::PARAM_STR);
$stmt->bindParam(':job_place',$job_place, PDO::PARAM_STR);
$stmt->bindParam(':salary',$salary, PDO::PARAM_INT);
$stmt->bindParam(':filename',$filename, PDO::PARAM_STR);
$stmt->bindParam(':upload',$upload, PDO::PARAM_STR);
$stmt->bindParam(':created_at',date('Y-m-d H:i:s'), PDO::PARAM_STR);
$stmt->execute();

mysql> select * from register;
+----+-----------+------------+------------+-------------+-------+--------------      ------+---------------+-----------+--------+---------------+--------------------      -----------------+---------------------+
| id | last_name | first_name | flast_name | ffirst_name | birth | mail                     | tel           | job_place | salary | filename      | upload                                    | created_at          |
+----+-----------+------------+------------+-------------+-------+--------------      ------+---------------+-----------+--------+---------------+--------------------      -----------------+---------------------+
|  1 | 田中      | 太郎       | タナカ     | タロウ      |  1985 | engineer@gmai      l.com | 080-1234-5678 | 関東      |      0 |               | ./cfile/20190519071      727              | 2019-05-19 07:17:29 |
|  2 | 田中      | 太郎       | タナカ     | タロウ      |  1985 | engineer@gmai      l.com | 080-1234-5678 | 関東      |      0 | nightsky.jpeg | ./cfile/20190519071      835nightsky.jpeg | 2019-05-19 07:18:37 |
+----+-----------+------------+------------+-------------+-------+--------------      ------+---------------+-----------+--------+---------------+--------------------      -----------------+---------------------+
2 rows in set (0.00 sec)

Also want to send a email.

Transfer file to confirmation screen

index.php

<form action="confirm.php" enctype="multipart/form-data" method="post">
...

<p>
								職務経歴書<br>
								<label><input id="select-file" style="display:none" type="file" name="filename" onChange="file_selected()"><input id="file-name" type="text" value="ファイルを選択してください" readonly="readonly" onclick="file_select()"></label><span id="message">選択されていません</span>
							</p>
...
<p>
								「<a href="privacy.php">個人情報の取り扱い</a>」に同意の上、無料登録するのボタンをクリックしてください。
								<input type="submit" id="regist" value="同意して無料登録する">
							</p>
						</form>

confirm.php

 $upload = './cfile/'.$_FILES['filename']['name'];
 if(move_uploaded_file($_FILES['filename']['tmp_name'],$upload)){
 	echo 'success';
 } else {
 	echo 'failed';
 }

successed

delete temporary file before upload.

 $dir = glob('./cfile/*');
 foreach($dir as $file){
 	unlink($file);
 }
 $upload = './cfile/'.$_FILES['filename']['name'];
 if(move_uploaded_file($_FILES['filename']['tmp_name'],$upload)){
 	echo 'success';
 } else {
 	echo 'failed';
 }

– If this is done, the uploaded file will disappear when multiple users operate, so it has to be deleted by cron batch etc.

 $upload = './cfile/'.date("YmdHis").$_FILES['filename']['name'];
 if(move_uploaded_file($_FILES['filename']['tmp_name'],$upload)){
 	echo 'success';
 } else {
 	echo 'failed';
 }

PHP file upload process

Let’s upload the file while looking at the simplest code.

test.php

<form action="test2.php" enctype="multipart/form-data" method="post">
	<input name="file_upload" type="file">
	<input type="submit" value="upload">
</form>

test2.php

	$upload = './'.$_FILES['file_upload']['name'];
	if(move_uploaded_file($_FILES['file_upload']['tmp_name'], $upload)){
		echo 'upload success';
	} else {
		echo 'upload failed';
	}

well done.

PHPでファイルアップロードに確認画面を挟む時

アップロードした一時ファイルは、PHPの実行が終わった時点で削除されてしまう。
よって、$_FILESを次画面に送ってもファイルがない。
よって、確認画面表示時には既にサーバーに仮アップして、完了後にしかるべき場所に移動、という方法がある。

index.php

<p>
								職務経歴書<br>
								<label><input id="select-file" style="display:none" type="file" onChange="file_selected()" name="filename"><input id="file-name" type="text" value="ファイルを選択してください" readonly="readonly" onclick="file_select()"></label><span id="message">選択されていません</span>
							</p>

confirm.php

<td>職務経歴書</td>
							<td>
								<?php
									if(!empty($_POST&#91;"filename"&#93;)){
									    echo $_POST&#91;"filename"&#93;; 
								    } else {
								        echo "-";
								    }   
							    ?>
								
							</td>

当然、これではあかん。。

CloudWatch Logs

CloudWatch Logs is a log monitoring service provided by AWS. Collect OS logs and application logs of EC2 instances and monitor them in real time.

Amazon CloudWatch is an application monitoring service that runs on AWS. Can use Amazon CloudWatch to gain visibility into resource utilization, application performance, and operation status across the system. Use these insights to respond and keep your application running smoothly.

With CloudWatch Logs, can mainly used for:
– Log accumulation (you can set the retention period)
– Specific character filtering
– Graph what matched the filter pattern
– Alert settings linked with Amazon SNS(Simple Notification Service)

CloudWatchの取り込みが1GBあたり91.2円、1GBあたりのアーカイブが3.96円。
あれ、これどーやって計算するんだろう。。
たとえば、データ量として大きくなりそうなのはELBだが、ELBでどれ位のボリュームでどれ位のデータ量になるんだろうか??