kwic5

5．検索語の前後に現れる単語を数へる（アルファベット順表示）

　まづ、MySQLに収めたデータから、wb1、wb2、wf1、wf2の四項目を抜き出して、同じ単語の数を数へて並べるだけです。

	$cnt_wb1="select wb1, count(wb1) from temptable_n group by wb1";
	$rs_wb1=mysql_query($cnt_wb1);
	$cnt_wb2="select wb2, count(wb2) from temptable_n group by wb2";
	$rs_wb2=mysql_query($cnt_wb2);
	$cnt_wf1="select wf1, count(wf1) from temptable_n group by wf1";
	$rs_wf1=mysql_query($cnt_wf1);
	$cnt_wf2="select wf2, count(wf2) from temptable_n group by wf2";
	$rs_wf2=mysql_query($cnt_wf2);
	
echo "<table border=1 align='left'>";
echo "<caption>Sort by word alphabetically</caption>";
echo "<tr bgcolor='#d5eaff'>";
echo "<td>2nd left word</td>";
echo "<td>1st left word</td>";
echo "<td>1st right word</td>";
echo "<td>2nd right word</td>";
echo "</tr>";
echo "<tr valign='top' bgcolor='#ffeaeb'>";
echo "<td>";
while($row1=mysql_fetch_array($rs_wb2)){
echo $row1["wb2"], ":" .$row1["count(wb2)"]."<br />";
}
echo "</td>";
echo "<td>";
while($row2=mysql_fetch_array($rs_wb1)){
echo $row2["wb1"], ":" .$row2["count(wb1)"]."<br />";
mysql_query($insert_wb1);
}
echo "</td>";
echo "<td>";
while($row3=mysql_fetch_array($rs_wf1)){
echo $row3["wf1"], ":" .$row3["count(wf1)"]."<br />";
mysql_query($insert_wf1);
}
echo "</td>";
echo "<td>";
while($row4=mysql_fetch_array($rs_wf2)){
echo $row4["wf2"], ":" .$row4["count(wf2)"]."<br />";
mysql_query($insert_wf2);
}
echo "</td>";
echo "</tr>";
echo "</table>";

6．検索語の前後に現れる単語を数へる（頻度順表示）

　これで、前後のそれぞれ二列の単語を集計して表示させることができます。でも、ちょっと物足りない。やはり頻度順に表示させてみたいでせう。共起の頻度が気になるのが普通でせうから。そこで、MySQLに先ほどの結果表示と同時に集計結果を収めることにします。それから、今度は頻度順に呼び出すことにしませう。

	$del="delete from cnt_b2";
	mysql_query($del);
	$del="delete from cnt_b1";
	mysql_query($del);
	$del="delete from cnt_f1";
	mysql_query($del);
	$del="delete from cnt_f2";
	mysql_query($del);

	$cnt_wb1="select wb1, count(wb1) from temptable_n group by wb1";
	$rs_wb1=mysql_query($cnt_wb1);
	$cnt_wb2="select wb2, count(wb2) from temptable_n group by wb2";
	$rs_wb2=mysql_query($cnt_wb2);
	$cnt_wf1="select wf1, count(wf1) from temptable_n group by wf1";
	$rs_wf1=mysql_query($cnt_wf1);
	$cnt_wf2="select wf2, count(wf2) from temptable_n group by wf2";
	$rs_wf2=mysql_query($cnt_wf2);

echo "<table border=1 align='left'>";
echo "<caption>Sort by word alphabetically</caption>";
echo "<tr bgcolor='#d5eaff'>";
echo "<td>2nd left word</td>";
echo "<td>1st left word</td>";
echo "<td>1st right word</td>";
echo "<td>2nd right word</td>";
echo "</tr>";
echo "<tr valign='top' bgcolor='#ffeaeb'>";
echo "<td>";
while($row1=mysql_fetch_array($rs_wb2)){
echo $row1["wb2"], ":" .$row1["count(wb2)"]."<br />";
$insert_wb2="insert into cnt_b2(word_b2,count_b2)values('".$row1["wb2"]."','".$row1["count(wb2)"]."')";
mysql_query($insert_wb2);
}
echo "</td>";
echo "<td>";
while($row2=mysql_fetch_array($rs_wb1)){
echo $row2["wb1"], ":" .$row2["count(wb1)"]."<br />";
$insert_wb1="insert into cnt_b1(word_b1,count_b1)values('".$row2["wb1"]."','".$row2["count(wb1)"]."')";
mysql_query($insert_wb1);
}
echo "</td>";
echo "<td>";
while($row3=mysql_fetch_array($rs_wf1)){
echo $row3["wf1"], ":" .$row3["count(wf1)"]."<br />";
$insert_wf1="insert into cnt_f1(word_f1,count_f1)values('".$row3["wf1"]."','".$row3["count(wf1)"]."')";
mysql_query($insert_wf1);
}
echo "</td>";
echo "<td>";
while($row4=mysql_fetch_array($rs_wf2)){
echo $row4["wf2"], ":" .$row4["count(wf2)"]."<br />";
$insert_wf2="insert into cnt_f2(word_f2,count_f2)values('".$row4["wf2"]."','".$row4["count(wf2)"]."')";
mysql_query($insert_wf2);
}
echo "</td>";
echo "</tr>";
echo "</table>";

echo "<table border=1>";
echo "<caption>Sort by frequency</caption>";
echo "<tr bgcolor='#d5eaff'>";
echo "<td>2nd left word</td>";
echo "<td>1st left word</td>";
echo "<td>1st right word</td>";
echo "<td>2nd right word</td>";
echo "</tr>";
echo "<tr valign='top' bgcolor='#ffffd5'>";
echo "<td>";
	$srt_wb2="select * from cnt_b2 order by count_b2 desc,word_b2";
	$rsrt_wb2=mysql_query($srt_wb2);
	while($row22=mysql_fetch_array($rsrt_wb2)){
echo $row22["count_b2"], ":" .$row22["word_b2"]."<br />";
}
echo "</td>";
echo "<td>";
	$srt_wb1="select * from cnt_b1 order by count_b1 desc,word_b1";
	$rsrt_wb1=mysql_query($srt_wb1);
	while($row21=mysql_fetch_array($rsrt_wb1)){
echo $row21["count_b1"], ":" .$row21["word_b1"]."<br />";
}
echo "</td>";
echo "<td>";
	$srt_wf1="select * from cnt_f1 order by count_f1 desc,word_f1";
	$rsrt_wf1=mysql_query($srt_wf1);
	while($row11=mysql_fetch_array($rsrt_wf1)){
echo $row11["count_f1"], ":" .$row11["word_f1"]."<br />";
}
echo "</td>";
echo "<td>";
	$srt_wf2="select * from cnt_f2 order by count_f2 desc,word_f2";
	$rsrt_wf2=mysql_query($srt_wf2);
	while($row12=mysql_fetch_array($rsrt_wf2)){
echo $row12["count_f2"], ":" .$row12["word_f2"]."<br />";
}
echo "</td>";
echo "</tr>";
echo "</table>";

mysql_free_result($rs_wb1);
mysql_free_result($rs_wb2);
mysql_free_result($rs_wf1);
mysql_free_result($rs_wf2);

mysql_close($con);

　実際に作ってみたページはこちらです。検索したあとで、一番下にある控へ目なcountといふ語をクリックすると、集計値が表示されます。
（2005年2月27日）

Kwic ConcordanceをPHPで作る（さらに続き）

5．検索語の前後に現れる単語を数へる（アルファベット順表示）

6．検索語の前後に現れる単語を数へる（頻度順表示）