### 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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行目は抜かす必要がありますね。
