クエリ検索でのWHERE句 HAVING句併用

質問

(1)元ソースコードに「WHERE category = '%s'」を追加しましたが、クエリ検索でのWHERE句　HAVING句の併用がうまくいきません。
firefoxの要素調査では、ソース改造前には出なかった「HTML ドキュメントの文字エンコーディングが宣言されていません。」のエラーメッセージが出ます。（phpのソースなのにHTMLのエラー？）
原因が分かりましたらお教え願いたく宜しくお願い致します。

元ソースコードに「WHERE category = '%s'」を追加した状態。

// Search the rows in the markers table
$query = sprintf("SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers WHERE category = '%s' GROUP BY distance HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category)),
mysql_real_escape_string($radius));
$result = mysql_query($query);

※
( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) ・・・は入力された住所の緯度経度（$center_lat　$center_lng）から店（複数任意登録）までの距離を計算する公式

＜質問の背景＞

https://developers.google.com/maps/articles/phpsqlsearch_v3?hl=ja#createmap

でselect要素にcategory（店の種類：小売店・食事など）を追加しようと思っています。

上記質問は上記サイトでの「phpsqlsearch_genxml.php」の$query の部分です。

なお、「phpsqlsearch_genxml.php」のソースは下記のとおりです。

<?php
require("phpsqlsearch_dbinfo.php");

// Get parameters from URL
$center_lat = $_GET["lat"];
$center_lng = $_GET["lng"];
$radius = $_GET["radius"];

// Start XML file, create parent node
$dom = new DOMDocument("1.0");
$node = $dom->createElement("markers");
$parnode = $dom->appendChild($node);

// Opens a connection to a mySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die("Not connected : " . mysql_error());
}

// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ("Can\'t use db : " . mysql_error());
}

// Search the rows in the markers table
$query = sprintf("SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));
$result = mysql_query($query);

if (!$result) {
die("Invalid query: " . mysql_error());
}

header("Content-type: text/xml");

// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
$node = $dom->createElement("marker");
$newnode = $parnode->appendChild($node);
$newnode->setAttribute("name", $row['name']);
$newnode->setAttribute("address", $row['address']);
$newnode->setAttribute("lat", $row['lat']);
$newnode->setAttribute("lng", $row['lng']);
$newnode->setAttribute("distance", $row['distance']);
}

echo $dom->saveXML();
?>

(2)上記のエラー原因探すために、都度、プログラム途中での変数の値を表示できれば（javascriptの変数var 　PHPの変数$の値など）と思いますが、何か方法はあるものでしょうか。
（phpのflushなどがあるみたいですがHTML全体のどこにどのように記述すればいいのでしょうか。
もしできるなら、私の提示した具体的な上記ソースを例にとっていただければ助かります。）

回答No.4

yambejp · ベストアンサー

＞「distance < '%s'」と「WHERE category = '%s'」両方を記載する

実際に試しながらやってるわけではないので、記述ミスがあったらあしからず。
考え方としては、categoryはサブクエリの中で絞り込んで、distanceは外側で絞り込めばいいのでは？

$query = sprintf("SELECT * FROM (SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers WHERE category = '%s') AS sub WHERE distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category),
mysql_real_escape_string($radius)
);

補足

ご提示ありましたソースを実行しました。合わせてほかのパターンも試してみました。

(1)ご提示ありましたソース⇒エラー

$query = sprintf("SELECT * FROM (SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers WHERE category = '%s') AS sub WHERE distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category),
mysql_real_escape_string($radius)
);

(2)上記からdistanceをはずした⇒OK

$query = sprintf("SELECT * FROM(SELECT address, name, lat, lng, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers WHERE category = '%s' ORDER BY distance LIMIT 0 , 200) AS sub ",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
// mysql_real_escape_string($radius),
mysql_real_escape_string($category));

(3)ご提示ありましたソースの「WHERE distance」を「HAVING distance」に変更⇒エラー

$query = sprintf("SELECT * FROM (SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers WHERE category = '%s') AS sub HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category),
mysql_real_escape_string($radius)
);

なかなかしぶといカンジです。

「distance < '%s'」と「WHERE category = '%s'」両方を記載するさらなるヒントをいただければ助かります。

（かれこれ、この問題、1週間くらいになります。ここを突破しないと先に進めないので何年でも粘る所存で、今後、私もあれこれ試していきます。
　本日はこれからOSC北海道（https://www.ospn.jp/osc2014-do/）を覗いて、頭を冷やしてきます。）

回答No.6

yambejp

＞2バイトの漢字の扱いがおかしいのが原因かと思います

渡した文字の文字コードと、DBに格納されている文字の文字コードが
異なるのでしょう。

日本語の取り扱いについては、解説書に必ず書かれていますので
一度きちんと読まれた方がいいでしょう

お礼

アドバイスにありました「渡した文字の文字コードと、DBに格納されている文字の文字コードが異なるのでしょう。」に沿って調べましたら、無事完成致しました。
yambejp先生、お忙しい中、未熟者の私に長らくおつきあい頂きまして、本当にありがとうございました。

yambejp先生なしでは、何年かかったことか・・・。
今後、一層精進して、yambejp先生のように教えれる立場になりたいと思っています。

このたびは本当にありがとうございました。

回答No.5

yambejp

ちなみにエラーは「HTML ドキュメントのなんちゃら・・・」のままですか？

SQLの文法問題であれば
＞die("Invalid query: " . mysql_error());
のところでエラー内容が表示されると思います

またPHPのパースエラーなどであれば
error_reporting(E_ALL);
など、ソースのあたまの方にいれておけばそれなりのエラーが表示されると思います。

上記２点がとおっているなら
DOMDocumentの方の問題かもしれませんね

補足

前回ご提示いただきましたソースに問題ありませんでした。
私の使ったデータではクエリーの結果が0件だったため、おかしな表示になっただけでした。

下記の様な定数を決め打ちで、クエリーを動かしてみたところ正常に動きました。

$center_lat = "100";
$center_lng = "20";
$radius = "10000";
$category ="100";

$query = sprintf("SELECT * FROM (SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers WHERE category = '%s') AS sub WHERE distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category),
mysql_real_escape_string($radius)
);

ところが「$category」を2バイトの漢字にしたところ（DBの「category」をTEXT型にし、いくつかの
categoryを「食事」に変更）また、クエリーの結果が0件になってしまいました。
2バイトの漢字の扱いがおかしいのが原因かと思いますが、よくわかりません。
何かヒントをいただければ助かります。

$center_lat = "100";
$center_lng = "20";
$radius = "10000";
$category ="食事";

回答No.3

yambejp

クォーテーションの位置も変だし、サブクエリも変

もしかしてサブクエリ自体理解していませんか？
一度マニュアルをよく読まれるとよろしいかと
とりあえずこんな感じでしょうか？

$query = sprintf("SELECT * FROM (SELECT address, name, lat, lng, category,(・・・) AS distance FROM markers ) AS sub
WHERE distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));

補足

なるべくご迷惑をお掛けしないように、ネットで調べても判らないことを質問しようと心がけていますが、至らない点が多々あり、ご迷惑おかけしています。
申し訳ございませんが、今一度質問させてください。

(1)yambejpさんの提示していただいたソース⇒OK

$query = sprintf("SELECT * FROM(SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers) AS sub WHERE distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));

(2)yambejpさんの提示していただいたソースの「distance < '%s'」を「WHERE category = '%s'」に変更⇒OK

$query = sprintf("SELECT * FROM(SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers) AS sub WHERE category = '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category));

(3)yambejpさんの提示していただいたソースに「distance < '%s'」と「WHERE category = '%s'」両方を記載⇒エラー

$query = sprintf("SELECT * FROM(SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers) AS sub WHERE category = '%s' AND category = '%s'　ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius),
mysql_real_escape_string($category));

yambejpさんの提示していただいたソースに「distance < '%s'」と「WHERE category = '%s'」両方を記載するアドバイスをいただければ助かります。

回答No.2

yambejp

仮にこんなデータで検証してみると

CREATE TABLE tbl(x int,y int);
INSERT INTO tbl values(2,3),(1,-6),(0,0),(-3,4);

(1)SELECT句で付けた別名をつかってWHERE句で絞り込みはできません

SELECT (x + y) as hoge FROM tbl WHERE hoge>0;
的なことは無理だということ

(2)HAVINGはSELECTの別名を利用できますが、本来GROUP BYとの組み合わせで
しかつかえない句なので

SELECT (x + y) as hoge FROM tbl HAVING hoge>0;
という書き方はお勧めできません
※ただしMySQLでは参照できてしまいます

(3)ただしい書き方
SELECT (x + y) as hoge FROM tbl WHERE (x + y)>0;

しかし、同じ式を2度書くのも非効率なので結局サブクエリをつかって

SELECT hoge FROM (SELECT (x + y) as hoge FROM tbl) as sub WHERE hoge>0;
とするのが妥当です。

補足

categoryでの検索条件を追加する前に、元のコードのSELECT結果をFROM句に設定して稼働するか試してみましたが、正常に稼働しませんでした。
何かアドバイスいただければ助かります。

(1)元のコード⇒正常にMAP表示される

$query = sprintf("SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));

(2)元のコードをインラインビュー風にFROMの後に配置⇒正常にMAP表示されない

$query = sprintf(SELECT * FROM ("SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius)));

(3)上記のコードの最初の「SELECT」を「”SELECT」に変更し、閉じる「”」を最後の「($radius))");」の箇所に付加⇒正常にMAP表示されない
※「”」の扱いが誤っているのか？

$query = sprintf("SELECT * FROM ("SELECT address, name, lat, lng, category, ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius))");

回答No.1

yambejp

これはtypo？

$query = sprintf("SELECT ・・・",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($category)),
mysql_real_escape_string($radius));

4番目の引数指定時にカッコ閉じが2回入っているので
このままいくとおそらくシンタックスエラー・・・

補足

yambejpさん、ご回答ありがとうございます。
4番目の引数指定時にカッコ閉じを訂正しました。

(1)元のコード

・・・・FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 200",

だと、OK

(2)変更

・・・・FROM markers WHERE distance < '%s' ORDER BY distance LIMIT 0 , 200",

だと、エラー。

(3)category使用

・・・・FROM markers HAVING category ＝ '%s' ORDER BY distance LIMIT 0 , 200",

・・・・FROM markers WHERE category ＝ '%s' ORDER BY distance LIMIT 0 , 200",

だと、OK

上記を考えると、distanceは集計関数の条件に見えます。

それで2つを組み合わせて

・・・・FROM markers WHERE category ＝ '%s' HAVING distance < '%s'　ORDER BY distance LIMIT 0 , 200",

だと、エラー。

・・・・FROM markers WHERE category ＝ '%s' GROUP　BY　distance　HAVING distance < '%s'　ORDER BY distance LIMIT 0 , 200",

も、エラー。

category　と　distanceの組み合わせの仕方が分かりません。
何かアドバイスいただけますでしょうか。