SQL カウントとデータの取得

Question

データを表示したいときは

SELECT `id`, `name` FROM table WHERE id>10

件数を表示したいときは

SELECT COUNT(*) FROM table WHERE id>10

と思います。

ですがｺﾁﾗを１つの文でまとめることはできないのでしょうか？
その際
php で出力する際いつもｺﾁﾗを使っていますがｺﾁﾗは正しい方法なのでしょうか？

$sql='SELECT `id`, `name` FROM table WHERE id>10';
$stmt=$dbh->prepare($sql);
$stmt->execute();
foreach($stmt as $result){
	$id[]=$result['id'];
	$name[]=$result['name'];
}
foreach($name as $val){
	echo "名前は".$val;
}

もし最初から１つのデータしかなければ『foreach』でまわす必要はなく
foreach($stmt as $result)
を使わないほうがいいのではないのか？と疑問をもっていつも使っています。
ご回答宜しくお願いいたします。

Answer 1

取得するSQLについては、他の方が回答されているので割愛しますが、件数とデータを取得する必要がある場合についての多くは、画面表示の際のページング制御によるものが多いと思います。

画面表示の際に必要な情報を全件取得し全件表示するなら、件数の取得は全件取得後に考えればよいですが、多くはメモリーやトラフィックを大量に消費することと、レスポンスの悪さが問題になるため、取得する件数を制限しようということになります。

そうなると、先ずは件数を取得し、それによりページングの制御を行うというSQLを２回発行することになりますので、致し方ないのかと思います。

--とは言え、私も同じ条件のSQLを２回発行する無駄を何とか省けないものかと考えました。
--発想を変えたアプローチで対応して、自分としてはそれなりに納得しています。

Answer 2

where句が同じSQLを一発で発行できないかという題意ならできないですね
SQLを１文にまとめるならこうです

create table tbl (id int,name varchar(20));
insert into tbl values(1,'n01'),(2,'n02'),(10,'n10'),(11,'n11'),(12,'n12'),(99,'n99');

select id,name,(select count(*) from tbl where id>10) as count
from tbl where id>10

Answer 3

「where id>10」というのが、よく分からないですが、
SQLで「limit」を使わないのであれば、
KiyoshiMotokiさんの１つ目の回答の内容で十分ではないでしょうか。

とはいえ、「limit」も使う（？）ようなので、
以下のような方法が考えられますね。

-- 対象のレコードに全件数を付与
SELECT	T1.id
	,	T1.name
	,	T3.cnt
FROM	table T1
	,	(
			SELECT	COUNT('X') as cnt
			FROM	table T2
			WHERE	T2.id > 10
		) T3
WHERE	T1.id > 10

-- もしくは、以下のように最後につなげる。

SELECT	T1.id
	,	T1.name
	,	0 as cnt
FROM	table T1
WHERE	T1.id > 10
UNION ALL
SELECT	0	as id
	,	''	as name
	,	COUNT('X') as cnt
FROM	table T2
WHERE	T2.id > 10

Comments

[KiyoshiMotoki]

横から失礼します。

回答欄にご提示の SQL文は
「LIMIT を使ってデータを取得しつつ、LIMIT を使わなかったときの件数も取得する」
という意図のものでしょうか？

であれば、正確には以下のようになりますでしょうか？

SELECT　　T1.id
　　,　　T1.name
　　,　　T3.cnt
FROM　　`table` T1
　　,　　(
　　　　　　SELECT　　COUNT('X') as cnt
　　　　　　FROM　　`table` T2
　　　　　　WHERE　　T2.id > 10
　　　　) T3
WHERE　　T1.id > 10
LIMIT 1

SELECT * FROM (
SELECT　　T1.id
　　,　　T1.name
　　,　　0 as cnt
FROM　　`table` T1
WHERE　　T1.id > 10
LIMIT 1
) AS T3
UNION ALL
SELECT　　0　　as id
　　,　　''　　as name
　　,　　COUNT('X') as cnt
FROM　　`table` T2
WHERE　　T2.id > 10

[tomari_perform]

はい、記載したSQLにはLimitを含めても問題ない事を意識した内容です。

質問文には「Limit」の記載がなかったのと、
Limitの具体的な値が不明だったのと、
そもそものwhere句が謎だったので、
あえて記載はしませんでしたが。

Limitを追加する場合、KiyoshiMotokiさんが追加した位置になります。

[tomari_perform]

ちなみに、２つ目のSQLは
MySQLのバージョンによっては、動かない可能性もあるので、
(
SELECT～
LIMIT ～
)
UNION ALL
(
SELECT ～
)
とした方が良いかもしれません。

[KiyoshiMotoki]

レスありがとうございます。
了解いたしました。

Answer 4

ご質問の主意は

「データの取得と件数の確認とで foreach を2回、回さなければならいのが気持ち悪い」

ということでしょうか？

であれば、プログラムの書き方を工夫することで回避できます。

例えば以下のようにカウンタ変数を用意してやれば、foreach の使用は1回で済みます。

$sql='SELECT `id`, `name` FROM `table` WHERE id>10';
$stmt=$dbh->prepare($sql);
$stmt->execute();

$count = 0;
foreach($stmt as $val){
    echo "名前は" . $val['name'] . PHP_EOL;
		$count++;
}
echo '件数は' . $count . PHP_EOL;

もっとも、これだと
「名前を表示した後にしか件数を表示できない」
というデメリットがあります。

他の方法としては、以下のようにPDOStatement::fetchAll() メソッドを使用するという手もあります。
http://php.net/manual/ja/pdostatement.fetchall.php

$sql='SELECT `id`, `name` FROM `table` WHERE id>10';
$stmt=$dbh->prepare($sql);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '件数は' . count($result) . PHP_EOL;

foreach ($result as $value) {
    echo '名前は' . $value['name'] . PHP_EOL;
}

この方法であれば、件数と名前を表示する順番は自由に変更できます。

---

もし最初から１つのデータしかなければ『foreach』でまわす必要はなく

foreach($stmt as $result)
を使わないほうがいいのではないのか？

については、ケースバイケースだと考えます。

例えば

• テーブルの性質や SQL文の書き方（LIMIT 1を指定している、など）から、

ヒットするレコードは確実に1件だけである

• 何件ヒットしようと、その中から任意の1件だけを使用すればよい

などの場合にはわざわざ foreach を使う必要はない(※)と思いますが、
さもなくば、何件のレコードが取得されるかわからない以上、
何らかの方法で反復操作を行ってやる必要があるはずです。

※ 「1件もヒットしない」というエラーケースを考慮する必要はあるかもしれませんが。

Comments

[konpei_konpei]

お伝え方がわかりにくくて申し訳ありません…
主意としてはSQL文を二回書かずに一つのSQL文でどうにかならないか
というお話です。
ご回答いただいたものですと『LIMT』等で件数を指定してしまうとそのカウントしか指定ができないのでその場合でも何件あるかのカウントできる形をとりたかったのです。

わかりにくく大変申し訳ありません。

[KiyoshiMotoki]

レスありがとうございます。

しかし、

・質問文
・質問欄にご提示のコード
・コメント

の内容がバラバラであるため、何をお答えすればよいか確信がもてません。

今一度、質問したい内容を整理したうえで、
改めて質問欄を更新してください。