ブラウザからCSVファイルをアップロードし、MySQLのデータベースにインポートするプログラムをPHPで作成しています。
エクセルで作成したCSVファイルはsjis形式のため、UTF8に変換してからMySQLにインポートしてあげると文字化けせずにインポートする事が出来るのですが、毎回手動でUTF8に変換するのは面倒ですのでファイルをアップロードしたタイミングで自動的にsjis形式からUTF8に変換したいと考えております。
ファイルのアップロードまでは動作していますが、CSVファイルをsjisからUTF-8に変換するにはどうすればよいでしょうか?
どなたかご教示いただければ幸いです。以下は作成中のコードです。
index.html
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
<title>sample</title>
</head>
<body>
<form action="read.php" method="POST" enctype="multipart/form-data">
ファイル:<br>
<input type="file" name="upfile" size="30"><br>
<br>
<input type="submit" value="アップロード">
</form>
</body>
</html>
read.php
<?php
//csvファイルのアップロード
if (is_uploaded_file($_FILES["upfile"]["tmp_name"])) {
//「files」という名前のフォルダを同じ階層に設置。ここにCSVファイルをアップロード
if (move_uploaded_file($_FILES["upfile"]["tmp_name"], "files/" . $_FILES["upfile"]["name"])) {
chmod("files/" . $_FILES["upfile"]["name"], 0644);
//fileパス
$file = "files/" .$_FILES["upfile"]["name"];
//DB接続
$sv = "localhost";
$dbname = "shop";
$user = "user";
$pass = "password";
$conn = mysql_connect($sv, $user, $pass) or die("コネクトエラー");
mysql_select_db($dbname) or die("DBセレクトエラー");
mysql_query("SET NAMES utf8");
//CSVデータの取り込み
$sql = "LOAD DATA LOCAL INFILE '$file' INTO TABLE food";
$sql .= " FIELDS";
$sql .= " TERMINATED BY ',' "; //TERMINATED BY 区切り文字
$sql .= " ENCLOSED BY '\"' "; //ENCLOSED BY 囲み文字
$sql .= " LINES TERMINATED BY '\r\n' ";
$sql .= " IGNORE 1 LINES";
$result = mysql_query($sql, $conn);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $sql;
die($message);
}
echo "インポートが完了しました!";
//失敗したとき
} else {
echo "ファイルをアップロードできません。";
}
} else {
echo "ファイルが選択されていません。";
}
?>
CSVファイル
"No","food","price"
"1","うどん","100"
"2","カレー","300"
"3","パスタ","500"
MySQLテーブル
CREATE TABLE IF NOT EXISTS `food` (
`No` int(11) NOT NULL,
`food` varchar(50) NOT NULL,
`price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
使用中のバージョン
PHP/5.4.19
Apache/2.4.4
XAMPP v3.2.1
素人の質問で凝縮ですが、よろしくお願いいたします。
No.1
- 回答日時:
アップロード処理はこのページのコピペでしょうか。
http://www.php-labo.net/tutorial/php/upload.html
上記はいわゆる「ダメなコピペ」です。例外処理はちゃんとしましょう。
http://qiita.com/mpyw/items/939964377766a54d4682
http://qiita.com/mpyw/items/73ee77a9535cc65eff1e
非推奨のMysql関数を使うのもやめましょう。
http://qiita.com/mpyw/items/b00b72c5c95aac573b71
以上を踏まえて下記のようなコードでどうでしょうか・・・
http://pastebin.com/EAQ8yg3x
しかしこれ、何故か文字化けを起こすんですよね。文字化けを回避するためのこともしているのに何故かうまくいきません・・・
http://d.hatena.ne.jp/hchuno/20120324/1332554400
返信が遅くなり申し訳ございません。教えていただいたソースを解読するのに時間がかかっておりました。
http://pastebin.com/EAQ8yg3x
sql文がスッキリしていて目からうろこです。このような書き方があったのですね!
それに、エラー対策に感動しました!確ユーザー側の視点に立った目線でエラー対策を考える事も必要だと感じました。
$conn = mysql_connect($sv, $user, $pass) or die("コネクトエラー");
mysql_connect関数は非推奨だったのですね。今のうちに知る事が出来てよかったです!
ちなみに教えていただいたソースをコピペし実施したところ、PDO接続では何故かsql文が実行できません。当方のmysqlのバージョンはmysql:5.5.32です。
No.2
- 回答日時:
load 文にも、読み込むファイルの文字コードを指定できます。
field の指定より前に記述します。これが一番確実で、csvから読みとったデータをこの指定文字コードから対象テーブルやカラムの設定文字コードに変換します。本家の英文マニュアルみると、5.1.17から使える構文のようです。というか、このversion以降は、これしか参照しないみたいなニュアンス?
http://dev.mysql.com/doc/refman/5.1/en/load-data …
「it is usually preferable to specify the character set of the file by using the CHARACTER SET clause, which is available as of MySQL 5.1.17. 」
日本語版マニュアルは、これ以前の情報しかないようです。
質問文に肝腎のMySQLのversion記載がないですね。XAMPPのversionも違う物みてるみたいだし。3.2ってXAMPP controllerアプリのversionかな
MySQLのversion は select version() で確認した物を書いてください。ときどきdistribution番号を書いてる人がいるので。
あと、\n は、mysql に渡すときに 文字バックスラッシュとn でないとならないので、
phpの文字列内では \\n とする必要があります、または、文字列全体をシングルクォート'で括れば、\' 以外の \ は文字バックスラッシュ。
蛇足だけど " や ' のエスケープが面倒だから、ヒアドキュメントを使うのをおすすめする。
以下 例文, あ、pdo 使用前提で。。
<?php
$file = /* 適宜 */ ;
$table= 'food';
$sql_src = <<<SQL
LOAD DATA LOCAL INFILE %s INTO TABLE `%s`
CHARACTER SET sjis
fields terminated by ',' enclosed by '"'
lines terminated by '\\r\\n'
IGNORE 1 LINES
SQL;
$sql = sprintf($sql_src , $pdo->quote($file), $table);
$stmt = $pdo->query($sql);
教えていただきました通り、SQL文内の「fields」の前に「CHARACTER SET sjis」を追加したところ文字化けせずにインポートできました!ありがとうございました。
>質問文に肝腎のMySQLのversion記載がないですね。XAMPPのversionも違う物みてるみたいだし。3.2ってXAMPP controllerアプリのversionかな
バージョンの記載が漏れてしまい申し訳ございませんでした。当方のmyaqlのバージョン5.5.32です。
XAMPPのversionはおっしゃる通りコントロールパネル画面に記載されている数字をバージョンかと勘違いしておりました。当方のXAMPPのバージョンはXAMPP Version 1.8.2でした。
XAMPPのバージョンは、コントロール画面内の「Helpボタン」→「View ReadMeボタン」→メモ帳ひらいて確認しました。
>蛇足だけど " や ' のエスケープが面倒だから、ヒアドキュメントを使うのをおすすめする。
ヒアドキュメントというものがあったのですね。これならsql文を書くのにいちいち「sql=.""」みたいな形で書かなくともスッキリしますね!調べてみたら他にもprint文を書くときなどにも使用できるようですね。参考になりました。ありがとうございます。
ちなみにPDO接続でコードを書き換えてみたところ何故かSQL文を実行してくれません。書き直したコードは以下の通りです。
<?php
//csvファイルのアップロード
if (is_uploaded_file($_FILES["upfile"]["tmp_name"])) {
//「files」という名前のフォルダを同じ階層に設置。ここにCSVファイルをアップロード
if (move_uploaded_file($_FILES["upfile"]["tmp_name"], "files/" . $_FILES["upfile"]["name"])) {
chmod("files/" . $_FILES["upfile"]["name"], 0644);
//fileパス
$file = "files/" .$_FILES["upfile"]["name"];
/* データベース接続 */
// PDOオブジェクト生成
$dsn = 'mysql:dbname=shop;host=localhost';
$user = 'user';
$password = 'password';
$pdo = new PDO($dsn, $user, $password);
//CSVデータの取り込み
$table= 'food';
$sql_src = <<<SQL
LOAD DATA LOCAL INFILE %s INTO TABLE `%s`
CHARACTER SET sjis
fields terminated by ',' enclosed by '"'
lines terminated by '\\r\\n'
IGNORE 1 LINES
SQL;
$sql = sprintf($sql_src , $pdo->quote($file), $table);
//sql文の実行
$stmt = $pdo->query($sql);
$stmt->execute();
echo "インポートが完了しました!";
//失敗したとき
} else {
echo "ファイルをアップロードできません。";
}
} else {
echo "ファイルが選択されていません。";
}
?>
初心者のため初歩的な質問ばかりで申し訳ございません。。
No.3ベストアンサー
- 回答日時:
お礼ありがとうございます、お返事遅くなりました。
まず、pdo で接続するときは、dsnで、phpファイルの文字コードで charset を指定する。phpもmysqlも最新版なので、これが標準。
http://jp.php.net/manual/ja/ref.pdo-mysql.php
To_aru_Userさんのでもそうなっています。
あと、第4引数のoption配列 で PDO::ATTR_ERRMODE と PDO::MYSQL_ATTR_LOCAL_INFILE も指定しましょう。phpの最新版では、PDO::MYSQL_ATTR_LOCAL_INFILE をここでtrueにしないと、load出来ないようです。ここらへん、私もまだphpが古いversionでやってたりするので、マニュアルみて漸く気づいたりします。
mysqlとphpのversionの組み合わせで、制約がいろいろ変わるので、混乱しやすいかと思いますが、なるべく、それぞれのマニュアルを参照するようにしてください。
以下、お礼の提示文に少々追加。字下げは全角空白使ってるのでコピー時は削除のこと。
<?php
// PDOオブジェクト生成
$dsn = 'mysql:dbname=shop;host=localhost;charset=utf8';
$user = 'user';
$password = 'password';
$option = array(
// エラーモードを例外スローに設定
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// local ファイルからload可能にする
PDO::MYSQL_ATTR_LOCAL_INFILE => true ,
);
$pdo = new PDO($dsn, $user, $password, $option);
?>
それから、$pdo->query($sql) に入れてる、$sql文を先に表示してみて、間違いがないか見ることと、query()メソッド はprepared ではないので、executeは不要。
細かいことをいうと、mysql本体は、load文にプレースホルダーが使えないのですが(mysqliでの接続も同様)、
pdo_mysqlでは、通常設定時、エミュレーションモードといって、php側で先に置き換えてしまうので、load文でもプレースホルダーが有効になります。To_aru_Userさんのはプレースホルダー使用例。
オプションでPDO::MYSQL_ATTR_DIRECT_QUERY をtrueにすると、mysql本体と同じ動作になります。
最後にエラーチェック、例外モードにしていないときは、query発行後に
var_dump( $pdo->errorInfo() );
してみる。
または、PDO::ATTR_ERRMODEを例外モードにしていれば、query 発行部分を try{}catch(){} でくるんで、sql発行時のエラーチェックをしてください。
<?php
try{ /* ここに query 発行部分
*/
}catch(Exception $e) {
echo 'エラー:', $e->getMessage();
}
>PDO::MYSQL_ATTR_LOCAL_INFILE をここでtrueにしないと、load出来ないようです。
どうりでファイルを読み込んでくれないわけですね!他のサイトのPDOを使った作成例を見ても同じ書き方をしていたので、どこが間違っているのか分かりませんでした。
では書き直したPHP文をもう一度。
<?php
//csvファイルのアップロード
if (is_uploaded_file($_FILES["upfile"]["tmp_name"])) {
//「files」という名前のフォルダを同じ階層に設置。ここにCSVファイルをアップロード
if (move_uploaded_file($_FILES["upfile"]["tmp_name"], "files/" . $_FILES["upfile"]["name"])) {
chmod("files/" . $_FILES["upfile"]["name"], 0644);
//fileパス
$file = "files/" .$_FILES["upfile"]["name"];
//接続文字列
$dsn = 'mysql:dbname=shop;host=localhost;charset=utf8';
$user = 'user';
$password = 'password';
$option = array(
// エラーモードを例外スローに設定
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// local ファイルからload可能にする
PDO::MYSQL_ATTR_LOCAL_INFILE => true ,
);
try{
$pdo = new PDO($dsn, $user, $password, $option);
$sql_src = <<<SQL
LOAD DATA LOCAL INFILE %s INTO TABLE `%s`
CHARACTER SET sjis
fields terminated by ',' enclosed by '"'
lines terminated by '\\r\\n'
IGNORE 1 LINES
SQL;
$table= 'food';
$sql = sprintf($sql_src , $pdo->quote($file), $table);
//sql文の実行
$stmt = $pdo->query($sql);
echo "インポートが完了しました!";
}catch(Exception $e) {
echo 'エラー:', $e->getMessage();
}
//失敗したとき
} else {
echo "ファイルをアップロードできません。";
}
} else {
echo "ファイルが選択されていません。";
}
?>
これで接続できました!ありがとうございました★
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- CGI htmlからパラメータで、cgiに渡したい。 1 2023/02/06 16:15
- CGI perlで書いたcgiでsqliteの使い方を教えてください 2 2023/05/08 21:29
- PHP htmlspecialcharsが機能していないです。 バグですか? 1 2022/04/05 01:22
- JavaScript clear機能を失わずにファイルアップロード機能を作成したい 3 2023/06/10 16:12
- PHP php テーブルが作成できない 1 2022/11/17 23:41
- MySQL php テーブルを作れない 2 2022/11/17 18:22
- PHP if(preg_match("/[^0-9]/",$gu_d)){意味を教えてください。 1 2022/05/06 05:37
- PHP PHP MySql 画像を取得 1 2022/06/04 14:05
- PHP ここでの ②if($su_d<>"")の比較演算子 を使う理由は 1 2022/03/26 02:33
- Excel(エクセル) PHPプログラムをエクセルに張り付けると検索ボックスがでてくる! 3 2022/05/08 07:10
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
こちらはただの直列処理ですか?
-
phpでcookieがうまく保存されない
-
PHPで画像の渡しが上手く行きま...
-
PHPでこのコード自体に意味は無...
-
Q&Aサイトを作成していてURLの...
-
クエリObjectをforeachで回す時...
-
phpでPEAR::DBを使っているので...
-
reuterの記事をbeautiful.soup....
-
phpでクラスのメソッドで同名の...
-
PHP8を使うと、大量のWarningが...
-
SFTPなどは使わないホームペー...
-
awsにApacheとPHPを入れて、何...
-
phpのクラスメソッドの定義が長...
-
PHPで訪問回数を表示するカウン...
-
PHPを使って、別サイトの一部を...
-
jpgraphで表示されない
-
PHP MySQLに画像を直接保存
-
PHPとHTML+Xamppの掲示板で画像...
-
index.phpって何ですか? 具体...
-
index.phpに入るには、どうすれ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
PHPでCSVファイルをUTF8に変換...
-
簡単にEXEファイルを作れるプロ...
-
JCL(富士通/MSP)について教...
-
ftpコマンドの戻り値をチェック...
-
マイナポータルの住宅借入金当...
-
VB6にて、テキストファイルの内...
-
複数時間情報の重複を求める
-
ファイル名を変更前の名前に戻す方
-
秀丸エディタでのファイル分割
-
apacheで他のPCから見えない場...
-
こういう問題分をよんだとき
-
warファイル 「種類:WAR ファ...
-
VB Scriptで隠しファイル、フォ...
-
Apacheを複数のhttpd.confを用...
-
VBAのzip解凍についてのご質問です
-
pdfファイル名だけを変更不可に...
-
画像ファイル名をリストに基づ...
-
ファイルと同名のフォルダを作成
-
ACCESSのmdeファイルについて
-
VB6.0 テキストファイルの操作...
おすすめ情報