4.2.8.検索çµæžœã®è¡¨ç¤º  最後ã«æ¤œç´¢çµæžœã‚’出力ã™ã‚‹ç”»é¢ã§ã™ã€‚ã‚ã‚‹æ¡ä»¶ã«ã‚ˆã‚ŠDB検索を行ã£ãŸçµæžœã¯ã€ä¸€èˆ¬ã« ãã®ä»¶æ•°ã‚’予測ã§ãã¾ã›ã‚“。クライアントå´ã«å¯¾ã—ã¦å¤§é‡ã®ãƒ‡ãƒ¼ã‚¿ã‚’一度ã«è¿”ã—ã¦ã—ã¾ ã†ã®ã¯ã€ãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚¯è² è·ã‚„ç”»é¢ã®å¿œç”速度ã®é¢ã‹ã‚‰ã‚‚é¿ã‘ã‚‹ã¹ãã§ã™ã€‚出力データ件 æ•°ãŒå¤šã„å¯èƒ½æ€§ãŒã‚ã‚‹å ´åˆã€ãƒšãƒ¼ã‚¸åˆ¶å¾¡ã‚’è¡Œã†ã“ã¨ã‚’考慮ã™ã‚‹å¿…è¦ãŒã‚ã‚Šã¾ã™ã€‚  zip_display()ã«æ¸¡ã™å¼•æ•°ã¯ã€æ¤œç´¢çµæžœå…¨ä½“を一定ã®è¡Œæ•°ã§åŒºåˆ‡ã‚Šã€ãã®ä¸ã®ä½•ãƒšãƒ¼ ジ目を表示ã™ã‚‹ã®ã‹æŒ‡ç¤ºã™ã‚‹æ•°å€¤ã§ã™ã€‚å…ˆé ã®ãƒšãƒ¼ã‚¸ãªã‚‰0ã«ãªã‚Šã¾ã™ã€‚ [zip_display.inc] 1 <?php 2 // 3 // 検索çµæžœã®è¡¨ç¤º 4 // å¼•æ•°ï¼šãƒšãƒ¼ã‚¸ç•ªå· 5 // 6 function zip_display($page) 7 { 8 if (DEBUG) { 9 print "zip_display($page)<br>\n"; 10 } 11 $type = $_POST['type']; 12 $zip7 = $_POST['zip7']; 13 $pref_j = $_POST['pref_j']; 14 $city_j = $_POST['city_j']; 15 $count = $_POST['count']; 16 $db = sqlite_open(DB) or die(DB . " ãŒã‚ªãƒ¼ãƒ—ンã§ãã¾ã›ã‚“。\n"); 17 $select_phrase = "SELECT zip7,pref_j,city_j,town_j FROM zip "; 18 if ($type == 1) { // 郵便番å·ã§æ¤œç´¢ 19 $where_clause = "WHERE zip7 LIKE '$zip7%'"; 20 } else { // ä½æ‰€ã§æ¤œç´¢ 21 $where_clause = "WHERE 0=0"; 22 if (strlen($pref_j) > 0) { 23 $where_clause .= " AND pref_j = '$pref_j'"; 24 } 25 if (strlen($city_j) > 0) { 26 $where_clause .= " AND city_j = '$city_j'"; 27 } 28 } 29 // 30 // æ–°è¦æ¡ä»¶æŒ‡å®šæ™‚(セッションデータã¨ã—ã¦ä¿å˜ï¼‰ 31 // 32 if ($page == 0) { 33 $sql = "SELECT COUNT(*) FROM zip " . $where_clause; 34 $ar = sqlite_array_query($db, $sql, SQLITE_NUM); 35 $count = $ar[0][0]; // æ–°ã—ã„æ¡ä»¶ã«è©²å½“ã™ã‚‹ä»¶æ•° 36 $_SESSION['type'] = $type; 37 $_SESSION['zip7'] = $zip7; 38 $_SESSION['pref_j'] = $pref_j; 39 $_SESSION['city_j'] = $city_j; 40 $_SESSION['count'] = $count; 41 if (DEBUG) { 42 print "\$_SESSION[] = "; print_r($_SESSION); print "<br>\n"; 43 } 44 } 45 $sql = $select_phrase . $where_clause 46 . " LIMIT " . RECORD_PER_PAGE 47 . " OFFSET " . $page * RECORD_PER_PAGE; 48 // 49 // ページ制御部分ã®è¡¨ç¤º 50 // 51 $pages = $count / RECORD_PER_PAGE; // ページ数 52 if ($pages > 1) { 53 print "検索çµæžœãƒšãƒ¼ã‚¸ï¼š"; 54 for ($i=0; $i<$pages; $i++) { 55 if ($i == $page) { 56 printf(" %d\n", $i+1); 57 } else { 58 printf(" <a href=\"{$_SERVER['PHP_SELF']}?page=%d\">%d</a>\n", 59 $i, $i+1); 60 } 61 } 62 if ($page+1 < $pages) { 63 printf( 64 " <a href=\"{$_SERVER['PHP_SELF']}?page=%d\">次ページ</a><br>\n", 65 $page+1); 66 } 67 } 68 69 if (DEBUG) { print "$sql<br>\n"; } 70 71 $res = sqlite_query($db, $sql); // SQL ã®ç™ºè¡Œ 72 $rows = sqlite_num_rows($res); // çµæžœã®è¡Œæ•° 73 if ($rows > 0) { 74 print "<table border>\n" 75 . "<tr><th>郵便番å·<th>都é“府県<th>市区町æ‘<th>町域</tr>\n"; 76 for ($i=0; $i<$rows; $i++) { 77 $row = sqlite_fetch_array($res, SQLITE_ASSOC); 78 printf("<tr><td>%s<td>%s<td>%s<td>%s</tr>\n", 79 $row['zip7'], $row['pref_j'], $row['city_j'], $row['town_j']); 80 } 81 print("</table>\n"); 82 } else { 83 print "該当ã™ã‚‹ãƒ‡ãƒ¼ã‚¿ã¯ã‚ã‚Šã¾ã›ã‚“。\n"; 84 } 85 sqlite_close($db); 86 print "</body></html>"; 87 } // zip_display(); 88 ?>  16行目ã§ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’é–‹ãã€17行目ã§ã¯ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’抽出ã™ã‚‹ãŸã‚ã®SQLæ–‡ã®å…ˆé 部 分を作ã£ã¦$select_phraseã«ã‚»ãƒƒãƒˆã—ã¦ãŠãã¾ã™ã€‚18〜28行目ã§ã¯ã€SELECTã§æŒ‡å®šã™ã‚‹ WHEREå¥ã ã‘ã‚’ã€åˆ¥é€”$where_clauseã«ã‚»ãƒƒãƒˆã—ã¾ã™ã€‚  32〜44行目ã§ã¯ã€$where_clauseã§æŠ½å‡ºã•ã‚Œã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰å…¨ä½“ã®ä»¶æ•°ã‚’求ã‚ã¦ã€ç¾åœ¨ã® 抽出æ¡ä»¶ã¨ã¨ã‚‚ã«ã‚»ãƒƒã‚·ãƒ§ãƒ³å¤‰æ•°ã«ä¿å˜ã—ã¾ã™ã€‚ã“ã®å‡¦ç†ã¯å½“該スクリプトãŒå‘¼ã³å‡ºã• れるãŸã³ã«è¡Œã£ã¦ã‚‚æ£ã—ã動作ã—ã¾ã™ãŒã€é€Ÿåº¦çš„ã«ä¸åˆ©ãªã®ã§ã€å…ˆé ページ表示時ã®ã¿ ã§è¡Œã‚れるよã†ã«æ¡ä»¶ã‚’é™å®šã—ã¦ã„ã¾ã™ã€‚  45〜47行目ã§å®Ÿéš›ã«æŠ½å‡ºã‚’ã‹ã‘ã‚‹ãŸã‚ã®SQL文を生æˆã—ã¾ã™ã€‚ã“ã®é–¢æ•°ã®å¼•æ•°ã§ã‚ã‚‹ $pageãŒçµžã‚Šè¾¼ã¿æ¡ä»¶ã¨ã—ã¦ä½¿ã‚ã‚Œã¦ã„ã¾ã™ã€‚WHREå¥ã«ã‚ˆã‚‹æŠ½å‡ºã•ã‚ŒãŸãƒ¬ã‚³ãƒ¼ãƒ‰ã®é›†åˆ (レコードセット)ã®ä¸ã‹ã‚‰OFFSETã§æŒ‡å®šã—ãŸãƒ¬ã‚³ãƒ¼ãƒ‰ç•ªå·ï¼ˆå…ˆé ã¯0)ã‹ã‚‰æ•°ãˆã¦ã€ LIMITã§æŒ‡å®šã—ãŸãƒ¬ã‚³ãƒ¼ãƒ‰æ•°åˆ†ã ã‘ã®ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å–り出ã—ã¦ã„ã¾ã™ã€‚  次ã«ã€51行目ã§ã¯æ¤œç´¢çµæžœãŒå…¨éƒ¨ã§ä½•ãƒšãƒ¼ã‚¸ã‚ã‚‹ã®ã‹ã‚’算出ã—ã¦ã„ã¾ã™ã€‚ã“ã‚ŒãŒ1ペ ージã«åŽã¾ã‚‹ã®ã§ã‚ã‚Œã°å•é¡Œã‚ã‚Šã¾ã›ã‚“ãŒã€2ページ以上ã«ã¾ãŸãŒã‚‹å ´åˆã€å›³4-7ã®ã‚ˆã† ã«ã€æ¤œç´¢çµæžœãŒå…¨éƒ¨ã§ä½•ãƒšãƒ¼ã‚¸ã‚ã‚‹ã®ã‹ã€ã¾ãŸç¾åœ¨ä½•ãƒšãƒ¼ã‚¸ç›®ã‚’表示ä¸ãªã®ã‹ã¨ã„ã†ã€ ページ制御用ã®ãƒŠãƒ“ゲータを表示ã—ã¾ã™ï¼ˆ52〜67行目)。  やã£ã¨æº–備完了ã§ã™ã€‚71行目ã§SQLを発行ã—ã€æ¬¡ã®è¡Œã§å–り出ã—ãŸè¡Œæ•°ã‚’å–å¾—ã—ã¾ã™ã€‚ 73〜84è¡Œã§ã€è¡Œæ•°åˆ†ã ã‘ループã—ãªãŒã‚‰HTMLã®è¡¨ã‚’構æˆã—ã¦ã„ã¾ã™ã€‚