- ベストアンサー
入門書のPHPサンプル内で、MySQL関数を使いたい
環境：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>
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
#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 でも、色々とできるので調べてみてください。
その他の回答 (4)
- sakyo-t
- ベストアンサー率70% (19/27)
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をこねた方が簡単だと思います。
お礼
sakyo-tさん、たびたびのアドバイスありがとうございました。 #5さん（＝No1さん）への御礼にも書きましたように、 結果的には「とりあえず」思うように動くようになったみたいです。 ダメならイチから整理しなおす予定ですが、 > SELECT hoge1 AS a , hoge1 AS b, hoge1 as c FROM mytable ; > なんてこともできるはずですので 最初にこれを知っていたらかなり整然とできそうな気がして 脱力ものでしたが(^^; 大変勉強になりました。 やっぱりじっくりSQLを復習してから取り組むべきだったかもしれません。 しかし、今回ご教示いただいた内容は必ず今後に生きるものです。 恥ずかしい質問ばかりですみませんでしたが、 どうぞ今度ともよろしくお願い致します。
- athanasius
- ベストアンサー率37% (361/964)
＃１ です。 少し、勘違いをされているようですので、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の関数は実行されません。
補足
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)
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の問題化を うまく切り分けていけば、効率よく勉強できると思います。
お礼
sakyo-tさん、ご教示ありがとうございました。 カラム指定を*ではなく、一つ一つカンマ区切りでというのを 知りませんでした（というか、前にSQL基礎を勉強したとき 出てきていたのでしょうね。） カラムの指定は、*(全て)か、特定カラムしかできないと勘違いして このような恥ずかしい質問にいたった次第です。 配列で取り出すという方法はどっちも有用なようなので、 いただいたレスをひかえておいて、今後ともぜひ活用させていただけたらと 思います。
- athanasius
- ベストアンサー率37% (361/964)
結果を取り出すときの カラム名が分からないということのように見えるので、 $sql= "select date_format(DATE1, '%y/%m/%d') AS DATE2," ." AVG(HOGE1）AS HOGE2 ." from mytable' てな感じでカラム名を決めてしまって、 $col["DATE2"] $col["HOGE2"] で取り出せるような気がするけど。
補足
すみません、このような書き方ができるとわかっただけでも 勉強になりました。 また、質問の意図がわかりにくく、すみません。 質問のプログラム例では、上から五行目あたりにて $sql = "select * from mytable"; などと 既に*をつけてしまっている関係で、 select date_format(DATE1, '%y/%m/%d') みたいなことを 書くことができないと思うのですが、どのように 解決したらよいでしょうか？という主旨の質問でした。 ご提示のような、* をつけないselect文を改めて書き出す、 という意味では・・ないですよね？ とんちんかんでわかりにくい説明、本当にすみません。
お礼
実は、レスいただける前にも素人なりにモガきまして PHP側をいじくったら偶然にも希望していた動作を 一通り実現することができました。 結論としては、ご提示いただいた ＞2回SQL を実行して、個別のデータと集約を取得 という方法になっているのだと思います。 PHPファイルの中はぐちゃぐちゃなので不具合ない限り 見直すのは怖いですが(^^;、ダメだったらGROUP BYとか 別のご提示いただいた手段を勉強させていただきます。 本件では、本当にお世話になりました。 見捨てずアドバイスいただけたおかげで、 思い通りに動いたときのプログラミングの楽しさに 魅了されてしまいました。 また稚拙な質問するかもしれませんが、今度とも よろしくお願い致します。