ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

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.

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.

flush privileges
-> 権限の反映？
GRANT, REVOKE, or SET PASSWORDなどのステートメントでは必要ない？？

手動で権限テーブルをリロードするには、FLUSH PRIVILEGESステートメントを発行？
ふーん、そうなのか。。

グローバルレベル
GRANT ALL ON *.* TO user;
GRANT SELECT, INSERT ON *.* TO user;

データベースレベル
GRANT ALL ON db_name.* TO user;
GRANT SELECT, INSERT ON mydb.* TO user;

テーブルレベル
GRANT ALL ON db_name.table_name TO user;
GRANT SELECT, INSERT ON db_name.mytbl TO user;

カラムレベル
GRANT SELECT (col1), INSERT (col1, col2) ON db_name.table_name TO user;

実際にやってみましょう。
mysql> create user saru@localhost identified by ‘monkey’;
Query OK, 0 rows affected (0.59 sec)

mysql> show grants for saru@localhost;
+————————————————————————————————————-+
| Grants for saru@localhost |
+————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘saru’@’localhost’ IDENTIFIED BY PASSWORD ‘*A5892368AE83685440A1E27D012306B073BDF5B7’ |
+————————————————————————————————————-+
1 row in set (0.09 sec)

mysql> grant create on *.* to saru@localhost;
Query OK, 0 rows affected (0.05 sec)

mysql> show grants for saru@localhost;
+————————————————————————————————————–+
| Grants for saru@localhost |
+————————————————————————————————————–+
| GRANT CREATE ON *.* TO ‘saru’@’localhost’ IDENTIFIED BY PASSWORD ‘*A5892368AE83685440A1E27D012306B073BDF5B7’ |
+————————————————————————————————————–+
1 row in set (0.00 sec)

なるほど、grant userの後に、grantで権限を付けるわけですね。
ちょっと感動しました。