[modify]死因の文字列をPHP側でなくSQL文で作成する

diff --git a/db_common.inc b/db_common.inc
index 0586ee4..cb19b6f 100644 (file)
--- a/db_common.inc
+++ b/db_common.inc
@@ -102,15 +102,37 @@ class ScoreDB
      */
     private function get_search_query($offset, $limit, $where, $order_by)
     {
      */
     private function get_search_query($offset, $limit, $where, $order_by)
     {

-        switch ($this->sort_mode) {
-        case "score":
-            $query = "SELECT *, group_concat(realm_name) AS realms_name from (select * from scores ${where} {$order_by} LIMIT ${offset}, {$limit}) NATURAL JOIN races NATURAL JOIN classes NATURAL JOIN personalities NATURAL LEFT JOIN score_realms NATURAL LEFT JOIN realms GROUP BY score_id {$order_by}";
-            break;
-        case "newcome":
-            $query = "SELECT *, group_concat(realm_name) AS realms_name from (select * from scores ${where} {$order_by} LIMIT ${offset}, {$limit}) NATURAL JOIN races NATURAL JOIN classes NATURAL JOIN personalities NATURAL LEFT JOIN score_realms NATURAL LEFT JOIN realms GROUP BY score_id {$order_by}";
-            break;
-        }
-
+        $query = <<<EOM
+SELECT
+  *,
+  group_concat(realm_name) AS realms_name,
+  CASE
+    WHEN killer = 'ripe' THEN '勝利の後引退'
+    WHEN killer = 'Seppuku' THEN '勝利の後切腹'
+    ELSE killer || 'に殺された'
+  END AS death_reason
+FROM
+ (SELECT
+    *
+  FROM
+    scores
+  {$where}
+  {$order_by}
+  LIMIT {$offset}, {$limit}) AS s
+NATURAL INNER JOIN
+  races
+NATURAL INNER JOIN
+  classes
+NATURAL INNER JOIN
+  personalities
+NATURAL LEFT JOIN
+  score_realms
+NATURAL LEFT JOIN
+  realms
+GROUP BY
+  score_id
+{$order_by}
+EOM;

         return $query;
     }
 
         return $query;
     }
 

diff --git a/score_ranking.php b/score_ranking.php
index 99deec8..3ccdfbb 100644 (file)
--- a/score_ranking.php
+++ b/score_ranking.php
@@ -9,18 +9,6 @@ include "dump_file.inc";
 
 ini_set('zlib.output_compression', 'On');
 
 
 ini_set('zlib.output_compression', 'On');
 

-function get_death_reason($killer)
-{
-    switch ($killer) {
-    case "ripe":
-        return "勝利の後引退";
-    case "Seppuku":
-        return "勝利の後切腹";
-    default:
-        return $killer."に殺された";
-    }
-}
-

 
 /**
  * ページ情報を計算する
 
 /**
  * ページ情報を計算する


@@ -117,17 +105,15 @@ EOM;
 
     foreach($scores as $idx => $score) {
         $rank = $rank_start + $idx + 1;
 
     foreach($scores as $idx => $score) {
         $rank = $rank_start + $idx + 1;

-        $score_id = $score['score_id'];
-        $date = substr($score['date'], 0, 10);
+        $date = substr($score['date'], 0, 10); // 日時から日付部分を取り出す

         $sex_str = $score['sex'] ? "男" : "女";
         $sex_str = $score['sex'] ? "男" : "女";

-        $death_reason = get_death_reason($score['killer']);

         $depth = !$score['winner'] ? $score['depth']."階, " : "";
         $realms = isset($score['realms_name']) ? "(".$score['realms_name'].")" : "";
         $depth = !$score['winner'] ? $score['depth']."階, " : "";
         $realms = isset($score['realms_name']) ? "(".$score['realms_name'].")" : "";

-        $dumpfile = new DumpFile($score_id);
+        $dumpfile = new DumpFile($score['score_id']);

 
         echo "<tr>\n";
         if ($dumpfile->exists('dumps', 'txt')) {
 
         echo "<tr>\n";
         if ($dumpfile->exists('dumps', 'txt')) {

-            $name = "<a href=\"show_dump.php?score_id={$score_id}\">{$score['personality_name']}{$score['name']}</a>\n";
+            $name = "<a href=\"show_dump.php?score_id={$score['score_id']}\">{$score['personality_name']}{$score['name']}</a>\n";

         } else {
             $name = "{$score['personality_name']}{$score['name']}";
         }
         } else {
             $name = "{$score['personality_name']}{$score['name']}";
         }


@@ -142,7 +128,7 @@ EOM;
 
 EOM;
         if ($dumpfile->exists('screens', 'html')) {
 
 EOM;
         if ($dumpfile->exists('screens', 'html')) {

-            echo "<td><a href=\"show_screen.php?score_id={$score_id}\">{$death_reason}</a>";
+            echo "<td><a href=\"show_screen.php?score_id={$score['score_id']}\">{$score['death_reason']}</a>";

         } else {
             echo "<td>{$death_reason}";
         }
         } else {
             echo "<td>{$death_reason}";
         }