MySQL 検索結果のファイル保存と行数値入手

質問

MySQL での検索結果をファイル保存し、かつ検索データ数（行数値）を入手する事を実現するのが目標です。
検索結果をファイル保存、及び、検索データ数（行数値）の入手は単独ではできるのですが、２つを同時に実現する事ができていません。
本件について、アドバイスをお願いしたく、よろしくお願いいたします。

＜実際にやった事＞

PHPで MySQL のデータベースに接続 → 成功
$link = mysqli_connect($domain,$user,$password,$dbname);

テーブルの作成とテーブルへのデータ追加も　
mysqli_query( )を使って成功。　←　括弧内には必要なパラメータを入れて実行しています。
また、テーブル名は my_database としています。

ここからが困っている点です。

mysql からのデータの内容とデータの行数を取り出しすために、以下の内容を実行する時、
$res = mysqli_query($link, $sql);
$row_count = mysqli_num_rows($res);

(1) $sql が 「SELECT * FROM my_database」という文字列ならば、 $row_count には正常に行数値が戻ってくる。
また、テーブルの内容も正常に表示される。

(2) $sql が 「SELECT * FROM my_database INTO OUTFILE "output.csv"」というにファイル出力にすると、

mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given in （ファイル名）

というようなエラーメッセージが出て、データの行数値が取り出せない。そこで、$res について調査すると、

　　　　 var_dump($res);　　 →　bool(true)
　　　　print_r($res);　　　→　1

　　　という値になっている。　
このため、前述のエラーメッセージが出力されるものと思われます。
ただし、この場合でも　output.csv　は正常に出力されています。

ネット情報を調べて見ると、エラーメッセージが出るのは上記 (2) の SELECT 文の構文に問題がある、との情報でしたが、実際にファイル出力は正常なので構文に問題あるとは、私には良く理解できていません。

ちなみに、サーバーは Centos7 系の自宅 Linux マシンで、PHP は Ver 7.4.6 、MySQL は Ver 8.0.30 です。

回答No.1

superside0 · ベストアンサー

「SELECT * FROM my_database」
だとこのテーブルのデータがまるごと入ったmysqli_result オブジェクトを返すので
mysqli_num_rows()に渡して行数のカウントができるが

「SELECT * FROM my_database INTO OUTFILE "output.csv"」
だとテーブルの内容を外部ファイルに取り出すということを実行し
その結果が成功ならtrue・失敗ならfalseの区別がbool型が返るだけなので
「bool型の変数はmysqli_num_rows() の引数に使えないよ」
というエラーが出るということしょう。

”MySQL での検索結果をファイル保存し、かつ検索データ数（行数値）を入手”
を同時に行えるSQLがあればよいのでしょうけど、
同時に行わせることに強い理由がないのであれば
それぞれを別々に行っても問題ないのではないでしょうか。

なお、データ数のカウントのほうは　カウントするだけのために
全データを取り出してから、それを改めてカウントするのは無駄なので
「SELECT count(*) as XXX from テーブル名 (～where等）」
とやってDB上でカウントさせた値を直接取り出したほうが
効率的だと思わます。

お礼

これで、今回の問題は全て解決できました。
ありがとうございました。

補足

早速のコメント、深く感謝いたします。

文字列が「SELECT * FROM my_database」の場合と「SELECT * FROM my_database INTO OUTFILE "output.csv"」の場合で
戻り値の型式が全く違う、という事は私の想定外だったので、袋小路に入っていました。
ご説明、ありがとうございます。

ご教示頂いたように、「SELECT count(*) FROM my_database」という文字列を使って、データが４個だけの簡単なデータベースを作って動作確認しました。

mysql> SELECT COUNT(*) FROM my_database;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

というように、４個のデータが入っている事を確認できたのですが、前回と同様にこの文字列を使って
　$res = mysqli_query($link, $sql);
$row_count = mysqli_num_rows($res);

　実際にやってみると、 $row_count は（4ではなく）1 となってしまいます。
　この 4 の値を得る方法（関数）はあるのでしょうか？

（追伸）
今回の質問の背景は、自宅サーバーにある MySQL を利用するユーザーは、PC のブラウザで操作する事を想定しています。　従って、行数値や出力ファイル内容（の必要部分）を php 経由で html 側に渡す必要があります。

回答No.6

superside0

補足に
「行数値や出力ファイル内容（の必要部分）を php 経由で html 側に渡す必要があります」

とかいてありますが、最終目的は　外部ファイル化することでなく
HTMLをブラウザに渡すことなんですか？

もしそういうことなら、そもそも外部ファイル化する必要もなくて
$resの内容を　HTMLにして出力すればよいだけの
ことではないですか？

補足

本日は外出していて、レスポンスが遅れて申し訳ありませんでした。

＞最終目的は　外部ファイル化することでなく
＞HTMLをブラウザに渡すことなんですか？
＞もしそういうことなら、そもそも外部ファイル化する必要もなくて
＞$resの内容を　HTMLにして出力すればよいだけの
＞ことではないですか？

言葉足らずで申し訳ありませんでした。

最終的には、検索結果から必要情報を表示させ、見やすくしたものをブラウザで表示させ、それを Excel 型式のファイルで保存する事です。
ただし、ブラウザの表示内容を Excel に保存する部分は既に作成済みなので、「今回作業の最終目的は 検索結果をHTML（ブラウザ）に渡すこと」という表現をしてしまいました。

ご提示頂いた２つの方法の動作を確認いたしました。
今回、文字列の内容によって戻り値の型式が違う、という問題がメインだったので特に報告はしていませんでしたが、実際は

　INTO OUTFILE （ファイル名） FIELDS TERMINATED BY ","

というように、カンマで区切る型式にしていました。

第１の方法（exec 文でリダイレクト）では、カンマ型式にならなかったので（多分、設定はできない？）、第２の方法の、ご提示頂いた php スプリクトを使わせて頂きました。

どちらも属性 644 のファイルが正常に作成できました。
これで、今回の問題は全て解決できました。
ありがとうございました。

回答No.5

superside0

> mysql ver 8.0.17 からUMASK の値が変更されて制限強化されている

なるほど、もはやinto outfileを使うのはシステム管理の用途のみで
アプリケーション用に使うべきではないという位置づけなのでしょうね。

ファイル化するのに　
SQLの　select ～into outfile　の機能を使う以外に
以下の２通りがあるので
別の方法を試してみてはどうでしょう。

・PHPのシステムコール（OSのコマンドライン機能の呼び出し）で
　 mysqlコマンド の出力をリダイレクトしてファイル化
例）
　exec ("mysql -u ユーザー -pパスワード DB名 　-e 'SELECT * FROM my_database;' > /パス/ファイル");

・PHPで　$res = mysqli_query($link,"SELECT * FROM my_database");
　でとりだした結果をそのままPHPの処理でファイルに書き出す
例）
　 if($fp = fopen(ファイル名,'w')) {
while ($row = mysqli_fetch_assoc($res)) {
　　　　//日本語のコード変換が必要ならmb_convert_variables('SJIS-win', 'UTF-8', $row);みたいにして
fputcsv($fp, $row);
}
fclose($fp);
} else {
書き込みできないというエラーメッセージ処理
}

回答No.4

superside0

> まず、mysqli では cnt というインデックスは使えないようでエラーになります。

例で書いたSQL文の
$sql = "SELECT count(*) as cnt FROM my_database";
に注目してほしいのですが、　「as cnt 」　ってつけてますよね。
なので、$row["count(*)"]　でなく　$row["cnt"]　　で　4が
とりだせます。

補足

すみませんでした。　as cnt があるのを見逃していました。ご教示頂いたように実行すると 4 が取り出せました。
おかげさまで今回の問題はこれで解決したのですが、別件で１つ大きな問題に気がつきました。

superside0 様は、mysql には大変お詳しいようなので、便乗して質問させて頂く事お許し下さい。

問題というのは、「SELECT * FROM my_database INTO OUTFILE ・・・・」で作成されたファイルの属性が（想定した 644 ではなく） 640 になっている事で、これだと、他の PHP モジュールに移行した時に、このファイルが読み出せず、「Permission denied 。。。」というエラーになって
しまいます。 ネット情報だと、mysql ver 8.0.17 からUMASK の値が変更されて制限強化されているようです。

この出力ファイルの所有者が mysql：mysql になっているためか、PHP で chmod をやっても失敗します。
本件に関し、何か対策はあるのでしょうか？

回答No.3

superside0

あ、すみません
先のは
$row = mysql_fetch_assoc($res);
じゃなくて
$row = mysqli_fetch_assoc($res);
でした。

回答No.2

superside0

> $row_count = mysqli_num_rows($res);
>実際にやってみると、 $row_count は（4ではなく）1 となってしまいます。

mysqli_num_rows()は、行数を返す関数ですから
SQLの結果として4 というデータが１行あるのであれば、当然１ですね。

DB側でカウントしたほうが無駄がないので
PHP側でmysqli_num_rows()でカウントするのではなく　
$sql = "SELECT count(*) as cnt FROM my_database";
$res = mysqli_query($link, $sql);
$row = mysql_fetch_assoc($res);
print $row["cnt"];
でどうですか？

補足

コメントありがとうございました。実行内容をご報告します。

$res = mysqli_query($link, $sql);
$row = mysqli_fetch_assoc($res);
print $row["cnt"];

まず、mysqli では cnt というインデックスは使えないようでエラーになります。代わりに count 関数が使えるようです。（ただし私はこの関数を良く理解できていません）

$row という連想配列を print_r($row); で確認すると、Array ( [COUNT(*)] => 4 )　が返ってくるのですが、count($row)　の値を調べると 4 ではなく 1 となってしまいます。

初歩的な質問で恐縮ですが、４ を取り出す方法をご教示頂けたら幸いです。