SQL文でcnt(0)を出す方法（質問変更しました）

お世話になっております。
前回の続きの質問で恐縮ですが宜しくお願い致します。
コードが無駄にかなり長くなっているかもしれません。

わからない部分は以下のコードでチェックボックスとMySQLの中のwardとcntの部分を
表示する場合仮にデータベースのarea_idが存在しない場合
チェックボックス、地区名であるwardの後、cntは(0)と表示したいのですが、中々実現出来ずにいます。
誠にお手数ですがご教授いただきたいと思います。
何卒よろしくお願い致します。

なお、データのある場合は正常に表示されております。
下の画像がデータのある場合のイメージです。
データの無い場合の(0)と表示する方法を知りたいです。

以下のコードをデータベースにデータがある場合とない場合で条件分岐したいと思っております。


foreach((array)$results2 as $result2 ){
	if( $i >= 17 && $i <= 19){
	echo '<li class="num'.$i.'count_li">
	<input type="checkbox" name="area_id[]" value="'.$i.'"' ?>
	<?php if (isset($_POST['area_id']) and in_array($i,$_POST['area_id'])) print ' checked'; ?>/>
	<?php echo '('.$result2['ward'].')'.'('.$result2['cnt'].')'.'</li>' ?>
	<?php }
	$i++;
}

?>

コード

以下全体のプログラムです。

<html>
<head>
<meta charset="utf-8" />
<title>不動産サイト</title>
<link rel="stylesheet" type="text/css" href="../common/style.css"/>

<!--フォーム完全クリア-->
<script>
function resetValue(f){
for (var i=0;i<f.length;i++){
if(f[i].type=="text") f[i].value="";
if(f[i].type=="select-one") f[i].selectedIndex=0;
if(f[i].type=="checkbox") f[i].checked=false;
}
}
</script>
<!--フォーム完全クリアEND-->
</head>
<body>
<div id="main">
<form name="search_form" action="list2.php" method="post" >
<input type="hidden" name="cmd" value="search" />
<table class="check_t">
<tr>
<th>マンション名</th>
<td><input type="text" name="rental_name" value="<?php print( htmlspecialchars( $_POST["rental_name"] ,ENT_QUOTES ) ) ?>" size="20"></td>
</tr>
<tr>
<th>価格帯</th>
<td>
<input type="text" name="price_min" value="<?php print( htmlspecialchars( $_POST["price_min"] ,ENT_QUOTES ) ) ?>" size="8"> - 
<input type="text" name="price_max" value="<?php print( htmlspecialchars( $_POST["price_max"] ,ENT_QUOTES ) ) ?>" size="8"><br />
</td>
</tr>
<tr>
<th>住所</th>
<td><input type="text" name="address" value="<?php print( htmlspecialchars( $_POST["address"] ,ENT_QUOTES ) ) ?>" size="20"></td>
</tr>
<tr>
<th>名古屋市</th>
<td>

<?php 
	//チェックボックスのフォーム表示用SQL(名古屋市、物件1～16)/////////////////////////////////  

	$dsn = 'mysql:dbname=xxxxx;host=localhost';
	$user = 'xxxxx';
	$password = 'xxxxxx';
	$dbh2 = new PDO($dsn, $user, $password);
	$dbh2->query('SET NAMES utf-8');
	$sql2 = "SELECT rental.area_id,area.ward, Count(id) AS cnt
			 FROM rental
			 INNER JOIN area ON rental.area_id = area.area_id
			 GROUP BY rental.area_id";
	$condition2 = array();
	$statement2 = $dbh2->prepare( $sql2 );
    $statement2->execute( $condition2 );
	$results2 = $statement2->fetchAll();
	$i = 1;
?>
<?php
foreach((array)$results2 as $result2 ){
	if( $i >= 0 && $i <= 16){
	echo '<li class="num'.$i.'count_li">
	<input type="checkbox" name="area_id[]" value="'.$i.'"' ?>
	<?php if (isset($_POST['area_id']) and in_array($i,$_POST['area_id'])) print ' checked'; ?>/>
	<?php echo '('.$result2['ward'].')'.'('.$result2['cnt'].')'.'</li>' ?>
	<?php }
	$i++;
}

//echo '<li class="'. $i . 'count_li">' . $result['rental_name'] . '</li>';

?>
</td>
</tr>
<tr>
<th>愛知県 - その他の地域</th>
<td>
<?php 
	//チェックボックスのフォーム表示用SQL(愛知県内名古屋市以外、物件17～19)/////////////////////////////////  

	$dsn = 'mysql:dbname=xxxxx;host=localhost';
	$user = 'xxxxx';
	$password = 'xxxxx';
	$dbh2 = new PDO($dsn, $user, $password);
	$dbh2->query('SET NAMES utf-8');
	$sql2 = "SELECT rental.area_id,area.ward, Count(id) AS cnt
			 FROM rental
			 INNER JOIN area ON rental.area_id = area.area_id
			 GROUP BY rental.area_id";
	$condition2 = array();
	$statement2 = $dbh2->prepare( $sql2 );
    $statement2->execute( $condition2 );
	$results2 = $statement2->fetchAll();
	$i = 1;
?>

<?php

foreach((array)$results2 as $result2 ){
	if( $i >= 17 && $i <= 19){
	echo '<li class="num'.$i.'count_li">
	<input type="checkbox" name="area_id[]" value="'.$i.'"' ?>
	<?php if (isset($_POST['area_id']) and in_array($i,$_POST['area_id'])) print ' checked'; ?>/>
	<?php echo '('.$result2['ward'].')'.'('.$result2['cnt'].')'.'</li>' ?>
	<?php }
	$i++;
}

?>


</td>
</tr>
<tr>
<th>こだわり条件</th>
<td>
<input type="checkbox" name="pid[]" value="1" <?php if (isset($_REQUEST['pid']) and in_array('1',$_REQUEST['pid'])) print 'checked'; ?>/>
新築
<input type="checkbox" name="pid[]" value="2" <?php if (isset($_REQUEST['pid']) and in_array('2',$_REQUEST['pid'])) print 'checked'; ?>/>
南向き
<span class="space4"><input type="checkbox" name="pid[]" value="3" <?php if (isset($_REQUEST['pid']) and in_array('3',$_REQUEST['pid'])) print 'checked'; ?>/>
セキュリティ充実<br></span>
<input type="checkbox" name="pid[]" value="6" <?php if (isset($_REQUEST['pid']) and in_array('4',$_REQUEST['pid'])) print 'checked'; ?>/>
システムキッチン
<input type="checkbox" name="pid[]" value="7" <?php if (isset($_REQUEST['pid']) and in_array('5',$_REQUEST['pid'])) print 'checked'; ?>/>
ペット可
<input type="checkbox" name="pid[]" value="4" <?php if (isset($_REQUEST['pid']) and in_array('6',$_REQUEST['pid'])) print 'checked'; ?>/>
市街地が近い<br />
<input type="checkbox" name="pid[]" value="5" <?php if (isset($_REQUEST['pid']) and in_array('7',$_REQUEST['pid'])) print 'checked'; ?>/>
内装リフォーム
</td>
</tr>
</table>
<input type="submit" value="検索" class="Btn-gray button">
<input type="button" value="リセット" class="Btn-reset button" onClick="resetValue(this.form)">
</form>
<br>
<?php
print_r($sql);		

//rental_name,price_min,price_max,addressの値があれば<h1>検索結果</h1>表示////////////

if(!empty($_POST["rental_name"]) or !empty($_POST["price_min"]) or !empty( $_POST["price_max"] ) or !empty( $_POST["address"] ) or !empty( $_POST["area_id"] )){ 
	echo "<h1>検索結果</h1>";
}
	
//rental_name,price_min,price_max,addressの値があれば<h1>検索結果</h1>表示////////////
if(!empty($_POST["rental_name"]) or !empty($_POST["price_min"]) or !empty( $_POST["price_max"] ) or !empty( $_POST["address"] ) or !empty( $_POST["area_id"] )){ 
	echo "<table border=\"1\" class=\"table_result\">";
/*	<tr>
	<th></th>
	<th>物件名</th>
	<th>住所</th>
	<th>宿泊料金</th>
	</tr>";*/
}
?>

<?php  //▼▼▼foreach文はよくエラー出すので注意。配列なので(array)を入れる
	foreach((array)$results as $result ){
?>
<tr>
<td><img src="<?php echo($PHOTODIR)."/" ?><?php if (!empty( htmlspecialchars($result["photofilename"], ENT_QUOTES )) ) { print $result["photofilename"] ;} 
else { print "noimage.jpg" ;}?>" /></td>
<td><p class="h_name"><?php print( htmlspecialchars( $result["rental_name"], ENT_QUOTES ) ); ?></p><br>
	<p><?php print( htmlspecialchars( $result["pref"], ENT_QUOTES ) ); ?>
    <?php print( htmlspecialchars( $result["address"], ENT_QUOTES ) ); ?><br>
   \<?php print( htmlspecialchars( number_format( $result["price"] ), ENT_QUOTES ) ); ?></p>
</td>
<td></td>
<td></td>
</tr>
<?php
	}
?>
<?php
//rental_name,price_min,price_max,addressの値があれば</table>表示////////////
if(!empty($_POST["rental_name"]) or !empty($_POST["price_min"]) or !empty( $_POST["price_max"] ) or !empty( $_POST["address"] ) or !empty( $_POST["area_id"] )){
	echo "</table>";
}
?>

</div>
</body>
</html>	 
コード

行動規範の内容に同意します

回答2件

rentalに条件が入るのであれば、サブクエリにします。

rentalにpriceというカラムがあったとしたら、こんな感じです。

sql
1SELECT
2	area.area_id, 
3	area.ward, 
4	IFNULL(R2.cnt, 0)
5FROM area
6LEFT OUTER JOIN 
7	(
8		SELECT *, COUNT(*) AS cnt 
9		FROM rental 
10		WHERE BETWEEN price 30000 AND 60000 
11		GROUP BY area_id
12	) AS R2 ON R2.area_id = area.area_id

１回のクエリでとろうとせず、一旦areaテーブルだけSELECTして
phpのforeachでarea_id毎にrentalテーブルをSELECTするほうがわかりやすいかもしれません。

投稿2016/04/05 01:44

rkojima

総合スコア421

pechiper

2016/04/05 06:50

ご回答いただき誠にありがとうございます！ SQL文入力したところ期待通りの動作は確認できなかったのですが、まだまだ私の理解力のほうが無いので教えていただいたSQL文も理解できるよう努力しなければならないのが先だと思いました。本日はその時間がないのでもうしばらく考えてみようと思います。ご教授いただき誠にありがとうございました。

行動規範の内容に同意します

ベストアンサー

これはつまり、次のような事でよいでしょうか？
現状、物件がある場合は地区のチェックボックスが出るけども、物件が無い地区はチェックボックスが表示されない。しかし物件登録は無くても地区のチェックボックスは表示し、その際件数を0と表示したい。

areaというのが地区管理、reatalというのが物件管理のテーブルでしょうか？
もしそうなら、SQLを次のように変更すると解決するかも知れません。

SQL
1SELECT rental.area_id,area.ward, Count(id) AS cnt
2FROM area
3LEFT JOIN rental ON rental.area_id = area.area_id
4GROUP BY rental.area_id

※データベースで直接SQLを実行できるなら、念のためコードに組み込む前に実行して結果を既存のSQLと見比べてみてください。
※「GROUP BY rental.area_id」こうしたうえで集計関数と「rental.area_id」以外をselect文に書いて動作するのは恐らくmysqlだけです。postgresとかだとエラーになります。
※また、その値は選択できる候補の中のどれかが選択されるため、どれが選択されるかは保障されません。(このコードではarea_idでグループした中のarea.wardはどれも同じでしょうから、このことは問題になりません。)

投稿2016/04/04 23:07

hirohiro

総合スコア2068

pechiper

2016/04/05 06:44

ご回答いただき誠にありがとうございました。 >現状、物件がある場合は地区のチェックボックスが出るけども、物件が無い地区はチェッ>クボックスが表示されない。しかし物件登録は無くても地区のチェックボックスは表示し>、その際件数を0と表示したい。仰るとおりです。説明が下手ですみません。 SELECT rental.area_id,area.ward, Count(id) AS cnt FROM area LEFT JOIN rental ON rental.area_id = area.area_id GROUP BY rental.area_id 教えていただいたSQL文で直接phpmyadminで入力したところ area_id ward cnt NULL 　　北名古屋市 0 以下カウントのある表示が続きますが省略このように表示されます。ありがとうございます。あとはlocal環境で作ったサイトに表示したところ（チェックボックスは省略しますが）以下のようになります。名古屋市 (中村区)(2)(西区)(5)(昭和区)(1) 名古屋以下部分省略愛知県 - その他の地域 (南区)(1)(一宮市)(1) 北名古屋市(0)が出てこず、南区(1)が出てきます。ただ、phpmyadminで北名古屋市（0）が出てきたのでゴールは近い気がします。本当に親切にご回答いただきありがとうございました。

hirohiro

2016/04/05 09:37 編集

あ、そうか。動作確認してなかったから気がつきませんでした因みに0件のレコードのarea_idがnullなのが問題なんですよね？ SELECT rental.area_id,area.ward, Count(id) AS cnt この部分 SELECT area.area_id,area.ward, Count(id) AS cnt この様に変更してみてください。

pechiper

2016/04/06 07:10

再度ご回答誠にありがとうございます！教えていただいたSQL文はカウント0の地域が何故か１つしかでなかったので GROUP BY rental.area_idをGROUP BY area.area_idに変更、そしてORDER BY area_id ASC　を付け足したところphpadmin上と local環境のサイト上でもカウント0の地域が正常に表示させることが出来ました。以下SQL文です。理屈は未だに理解していないですけど運で上手くいきました。本当にありがとうございました！ SELECT area.area_id,area.ward, Count(id) AS cnt FROM area LEFT JOIN rental ON rental.area_id = area.area_id GROUP BY area.area_id ORDER BY area_id ASC

hirohiro

2016/04/06 07:42 編集

そうですね。Groupのほうも治さないと駄目でしたね。中途半端ですみません。うまく動作したようでよかったです。 > 理屈は未だに理解していない「left join」はjoinする側にデータがなくてもレコードを消しません。北名古屋市がareaに存在するけどrentalに存在しない場合 area.area_id=北名古屋市のarea_id　rental.area_id=null　になります。 ※inner join だと北名古屋市のレコードは消えます。それで、rental.area_idをgroupしても、値がnullなのでrentalに存在しない他の地域と一緒くたになってしまったというわけですね。

pechiper

2016/04/06 08:03

誠に丁寧な解説ありがとうございます！私の作ったコードは単に変更してどのように動作するか何パターンか確認しただけで自分なりの理由付けはできていませんでした。なるほどrentalのテーブルにarea_idが存在しない0の地域が無かったということですね。おかげ様で理解が深まりました。本当にありがとうございました。

行動規範の内容に同意します

あなたの回答