### dbとテーブルの作成
mysql> create database myapp;
mysql> use myapp;
mysql> create table users(
-> id int primary key auto_increment,
-> name varchar(255),
-> sports varchar(255)
-> );
nameとsportsを入れたcsvファイル(users.csv)を作成します。
### pdoの確認
$ php -i|grep PDO
PDO
PDO support => enabled
PDO drivers => mysql, sqlite
PDO Driver for MySQL => enabled
PDO Driver for SQLite 3.x => enabled
### csv.php
ini_set('display_errors', 1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=myapp;charaset=utf8','root','****',
array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e){
exit('データベース接続失敗.'.$e->getMessage());
}
$fp = fopen("users.csv", "r");
while(! feof($fp)){
$csv = fgets($fp);
$csv = trim($csv,'"');
$csv = mb_convert_encoding($csv, "UTF-8", "utf-8");
$csv = str_replace('"','',$csv);
$csv_array = explode(",",$csv);
$stmt = $pdo->prepare("INSERT INTO users(name, sports) VALUES(:name, :sports)");
$stmt->bindParam(':name', $csv_array[0],PDO::PARAM_STR);
$stmt->bindParam(':sports', $csv_array[1],PDO::PARAM_STR);
$stmt->execute();
}
echo "insert done";
$ php csv.php
insert done
mysql> select * from users;
+—-+—————–+—————-+
| id | name | sports |
+—-+—————–+—————-+
| 1 | name | sports
|
| 2 | Efa Trujillo | Gynastics
|
| 3 | Erica Werner | Rowing
|
| 4 | Amin Guerrero | Wiffleball
|
| 5 | Abbie Bender | Rugby
|
| 6 | Kamron Mcneill | Surfing
|
| 7 | Arabella Sutton | Basketball
|
| 8 | Emily Kerr | Curling
|
| 9 | Connar Small | Ice skating
|
| 10 | Haidar Fletcher | Figure skating |
+—-+—————–+—————-+
10 rows in set (0.00 sec)
1行目からinsertされてしまうので、1行目は抜かす必要がありますね。
