ベストアンサー

入門書のPHPサンプル内で、MySQL関数を使いたい

2005/07/13 10:11

環境：PHP5.0.3+MySQL4.0.14です。PHPどころか、プログラムも素人です。検索結果をHTML表に一覧化する仕組みを作っているのですが WHERE条件に合致した数値データを平均化したり、 WHERE条件合致した日付データの書式を【MySQL関数によって】加工したいです。それぞれ、mysql.exe（DOS窓上）での書き方はわかったものの（SELECT AVG(HOGE1) WHERE (HOGE <= 1) のように）、これをPHPに組み込んだ際ににどう使えばわからないです。今回使っているPHP入門書のサンプルは以下の通りです。ここで mysql_fetch_arrayに格納されたテーブル（カラム）を取り出しているらしいことはわかりますが、以下のサンプルで加工したい対象である $col["DATE1"]、$col["HOGE1"]を $col["date_format(DATE1, '%y/%m/%d')"]　や　$col["AVG(HOGE1）"]などとしてもだめみたいです。以下の例で、これらを取り出すにはどのように記述したらいいでしょうか。よろしくお願い致します。 <?php require_once("dbini.php"); $con = mysql_connect($DBSERVER, $DBUSER, $DBPASSWORD); $selectdb = mysql_select_db($DBNAME, $con); $sql = "select * from mytable"; $rst = mysql_query($sql, $con); $recmax = mysql_num_rows($rst); $body = "結果: $recmax 件"; $body .= "<table>"; for ($recnum = 0; $recnum < $recmax; $recnum++) { $col = mysql_fetch_array($rst); $body .= "<td>" . $col["DATE1"] . "</td>"; $body .= "<td>" . $col["HOGE1"] . "</td>"; $body .= "</tr>"; } $body .= "</table>\n"; mysql_free_result($rst); $con = mysql_close($con); ?> <html> <head></head> <body> <?= $body ?> </body> </html>

litton101
お礼率89% (1106/1240)

PHP
回答数5
ありがとう数4

みんなの回答 （5）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

athanasius
ベストアンサー率37% (361/964)

2005/07/14 11:26 回答No.4

#1です。大きな勘違いをしてました。よく観たらAVG()は、集約関数だったので、それぞれのカラムと一緒に取り出せるとは限らないのでした。 avg() は、設定した範囲の平均値ですので、確かに、それぞれのカラムと一緒には、出力できませんね。多分、、"select date_format(DATE1,'%y/%m/%d') AS DATE2,AVG(HOGE1）AS HOGE2 from mytable" だと、取り出せるどころか、エラーになりますね。これが、DATE2 毎の平均値であれば、 "SELECT date_format(DATE1,'%y/%m/%d') AS DATE2,AVG(HOGE1）AS HOGE2 FROM mytable GROUP BY DATE2" で可能になるはずです。いずれにしろ、集約関数は、GROUP BY なんかでまとめる単位でしかカラムを出力できません。集約関数と、個別のカラムは一緒には、必ずしも出力できません。集約関数で演算される対象であれば、特にです。お悩みの件の解消策としては、集約関数と、個別のカラムは一緒に取得できないですから。１）2回SQL を実行して、個別のデータと集約を取得２）個別データを取得して、集約関数的な処理を言語で行うでしょうか。私は、この範囲では、2回SQL を実行しても問題なさそうな気はしますが。 UNION でつなげちゃうこともできる範疇ではありますが。関係ないけど、例を挙げると SELECT date_format(DATE1,'%y/%m/%d') AS DATE2,HOGE1 FROM mytable UNION SELECT 'AVG',AVG(HOGE1) FROM mytable で取り出せれば、両方を一度に取得したことになるかなぁ。この場合、配列の中で DATE2 の値が"AVG" であれば、平均値だから別にするとか。また、当然のことながら関数の入れ子は可能ですよ。 SQL でも、色々とできるので調べてみてください。

質問者

お礼 2005/07/14 22:41

実は、レスいただける前にも素人なりにモガきまして PHP側をいじくったら偶然にも希望していた動作を一通り実現することができました。結論としては、ご提示いただいた＞2回SQL を実行して、個別のデータと集約を取得という方法になっているのだと思います。 PHPファイルの中はぐちゃぐちゃなので不具合ない限り見直すのは怖いですが(^^;、ダメだったらGROUP BYとか別のご提示いただいた手段を勉強させていただきます。本件では、本当にお世話になりました。見捨てずアドバイスいただけたおかげで、思い通りに動いたときのプログラミングの楽しさに魅了されてしまいました。また稚拙な質問するかもしれませんが、今度ともよろしくお願い致します。

その他の回答 (4)

sakyo-t
ベストアンサー率70% (19/27)

2005/07/14 11:44 回答No.5

sakyoです。私もAVG関数は頭から飛んでいってました(^^;。 GROUP BYなりを調べてみてください。多分、エラーにはならずに各行に全体の平均値がくっつくと思います。試してないので、なんとも。 (2) それと、もうひとつ核心があったのですが、それがこちらでした。 > 又、"select date_format(DATE1, '%y/%m/%d') AS DATE2,AVG(HOGE1） > AS HOGE2,* from mytable" > とすることも可能です。 > この場合、戻ってくるデータのカラムは、DATE2、HOGE2、DATE1、 > HOGE1 になります。 > > 後々、HOGE2を使うと、HOGE1が使えなくなっちゃうんじゃ・・・ > という・・・。～中略～ > 最下行（height_avg）はもちろん取り出せるが、 > 途中行（height）が取り出せなくなってしまうのではないか？と。 SELECT hoge1 AS a , hoge1 AS b, hoge1 as c FROM mytable ; なんてこともできるはずですので、 SELECT date1, date_format(date1, '%y/%m/%d') AS date2, 　　　 hoge1, AVG(hoge1）AS hoge2 from mytable もできます。同じカラムを何度使っても別名さえついてれば大丈夫なはずです。同じ名前がついているとPHP側でエラーがでそうな気もしますが、実験してないので、なんとも。私見ですが、 PHPが処理をする速度より、DBの方が速いと思います。プログラムをこねるより、SQLをこねた方が簡単だと思います。

質問者

お礼 2005/07/14 22:50

sakyo-tさん、たびたびのアドバイスありがとうございました。 #5さん（＝No1さん）への御礼にも書きましたように、結果的には「とりあえず」思うように動くようになったみたいです。ダメならイチから整理しなおす予定ですが、 > SELECT hoge1 AS a , hoge1 AS b, hoge1 as c FROM mytable ; > なんてこともできるはずですので最初にこれを知っていたらかなり整然とできそうな気がして脱力ものでしたが(^^;　大変勉強になりました。やっぱりじっくりSQLを復習してから取り組むべきだったかもしれません。しかし、今回ご教示いただいた内容は必ず今後に生きるものです。恥ずかしい質問ばかりですみませんでしたが、どうぞ今度ともよろしくお願い致します。

athanasius
ベストアンサー率37% (361/964)

2005/07/13 22:11 回答No.3

＃１です。少し、勘違いをされているようですので、SQL を調べてみると良いかもしれません。 "select * from mytable" の * は、mytable のカラム全てを指すワイルド・カードです。仮に mytable が CREATE TABLE mytable (DATE1 DATETIME, HOGE1 INTEGER) で作成されたときに、 select * from mytable とした場合、戻ってくるデータのカラムは、DATE1 と HOGE1 です。これは、"select DATE1,HOGE1 from mytable" と同等です。つまり、"select date_format(DATE1,'%y/%m/%d') AS DATE2,AVG(HOGE1）AS HOGE2 from mytable" として置き換える事は可能です。又、"select date_format(DATE1, '%y/%m/%d') AS DATE2,AVG(HOGE1）AS HOGE2,* from mytable" とすることも可能です。この場合、戻ってくるデータのカラムは、DATE2、HOGE2、DATE1、HOGE1 になります。通常、データベースの関数は、"SELECT MAX(XX) FROM mytable" で呼び出されるように、DB の中で宣言したり実行します。（VIEW を作って、呼び出す方法もありますが、MySQL では使えないので、関係ありません。） litton101 さんが、書かれているような方法ですと、DBから受け取ったデータに対して、関数を実行はできません。 Sqlの中で関数の演算を行うようにしないとMYSQLの関数は実行されません。

質問者

補足 2005/07/14 00:26

athanasiusさん、たびたびのご教示、感謝にたえません。おかげさまで、かなりの疑問は解決しました。 (1) まず、いただいたレスのうち、 select * from mytable を select DATE1,HOGE1 from mytable という書き方でも実現できるのだということが、まさに今回の質問の核心であった次第です。m(_ _)m (2) それと、もうひとつ核心があったのですが、それがこちらでした。 > 又、"select date_format(DATE1, '%y/%m/%d') AS DATE2,AVG(HOGE1）AS HOGE2,* from mytable" > とすることも可能です。 > この場合、戻ってくるデータのカラムは、DATE2、HOGE2、DATE1、HOGE1 になります。後々、HOGE2を使うと、HOGE1が使えなくなっちゃうんじゃ・・・という・・・。まずは御礼申し上げますが、延長上にある以下の残る課題、もしよろしければ相談に乗ってください ---------------- なぜ、(2)のようにしたい（平均値を算出しつつ、その元になった各値も返してほしい）かと申しますと、次のような事情があるからです（長文すみません）。データが記入されたたくさんの帳票をExcelにうちこんだのですが、これを・さまざまな条件で絞り込み、要点部分の項目、数値をリスト化・絞り込まれたリスト内で平均をみられるようにしたいですある条件で絞り込んだ結果、たとえばheightというカラムだけを切り取ってみると、次のようにリストアップされるというイメージがあります。この場合、select hoge1, hoge2, …, avg(height) as height_avg from mytable としてしまうと、最下行（height_avg）はもちろん取り出せるが、途中行（height）が取り出せなくなってしまうのではないか？と。 ---------- 高さ(cm) ---------- 10　←検索条件に合致したheightの値1：$body .= "<td>" . $col["height"] . "</td>"; ---------- 28　←検索条件に合致したheightの値2：$body .= "<td>" . $col["height"] . "</td>"; ---------- 22　←検索条件に合致したheightの値3：$body .= "<td>" . $col["height"] . "</td>"; ――――― 20　←検索結果の平均値（forでループしているブロック{}の外側に、もう一行<tr>を組んで、 ―――――　　avg(height) as height_avg とした値["height_avg"]を表示？）ただ、値1～3には、三桁区切り＋小数点揃えのためにformat(height, 2) as fmt_height, のようなMySQL関数も適用したいので、これも悩みの種ではあります。（MySQLでExcelのように関数を入れ子にできればよさそうにも思いますが）あるいは、SELECT文はAVGを算出するにとどめ、フォーマットはPHP関数（？）で切り抜けた方が妥当でしょうか・・・

sakyo-t
ベストアンサー率70% (19/27)

2005/07/13 12:37 回答No.2

No.1の方と同意見ですが、補足までに。解決方法は2つだと思います。 1.SQLを改造して各カラムに別名をつける 2.DBからの取得方法をMYSQL_BOTHまたはMYSQL_NUMにして　配列で取り出す 1.の場合は、$sql = "select * from mytable";を変更します。 $sql =　"select col1,col2,col3, " 　　　　."date_format(DATE1, '%y/%m/%d') AS DATE2, " 　　　　."AVG(HOGE1）AS HOGE2" " 　　　　."from mytable " ; 基本的なSQL文です。プログラム上でSQLを発行する場合は、カラム指定を*（すべて）ではなく、一つ一つカンマ区切りで指定したほうが好ましいと思います。 ASによって別名が定義されているので $colは連想配列となって、$col['col1'],$col['col2'],$col['col3'],$col['DATE1'],$col['HOGE2'] などのキーを指定して値を取り出せます。 2.の場合は $col = mysql_fetch_array($rst);を $col = mysql_fetch_array($rst,MYSQL_NUM); と変更して配列を取り出します。この場合は $colには $col[0],$col[1],$col[2],$col[3],$col[4]と SQL上の列名に関係なく、配列として添字によって値を取り出せます。どこまでがSQLに関する問題で、どこからがPHPの問題化をうまく切り分けていけば、効率よく勉強できると思います。

参考URL：: http://jp2.php.net/manual/ja/ref.mysql.php

質問者

お礼 2005/07/13 23:34

sakyo-tさん、ご教示ありがとうございました。カラム指定を*ではなく、一つ一つカンマ区切りでというのを知りませんでした（というか、前にSQL基礎を勉強したとき出てきていたのでしょうね。）カラムの指定は、*(全て)か、特定カラムしかできないと勘違いしてこのような恥ずかしい質問にいたった次第です。配列で取り出すという方法はどっちも有用なようなので、いただいたレスをひかえておいて、今後ともぜひ活用させていただけたらと思います。

athanasius
ベストアンサー率37% (361/964)

2005/07/13 11:25 回答No.1

結果を取り出すときのカラム名が分からないということのように見えるので、 $sql= "select date_format(DATE1, '%y/%m/%d') AS DATE2," ." AVG(HOGE1）AS HOGE2 ." from mytable' てな感じでカラム名を決めてしまって、 $col["DATE2"] $col["HOGE2"] で取り出せるような気がするけど。

質問者

補足 2005/07/13 11:52

すみません、このような書き方ができるとわかっただけでも勉強になりました。また、質問の意図がわかりにくく、すみません。質問のプログラム例では、上から五行目あたりにて $sql = "select * from mytable";　　などと既に*をつけてしまっている関係で、 select date_format(DATE1, '%y/%m/%d')　みたいなことを書くことができないと思うのですが、どのように解決したらよいでしょうか？という主旨の質問でした。ご提示のような、* をつけないselect文を改めて書き出す、という意味では・・ないですよね？とんちんかんでわかりにくい説明、本当にすみません。

入門書のPHPサンプル内で、MySQL関数を使いたい