PHP 和 MySQL 找到 COUNT 和 ORDER BY 结果

Question

我有这样的代码:

function search_keyword(){       

        $keyword = $this->db->escape_like_str(trim($_POST['keyword']));

        $sql = " ( SELECT  name, id_global_info AS id, body AS body, tag AS tag  ,info_type_id AS info_type, \"global_info\" AS mytable FROM global_info WHERE ";
         $sql .= " MATCH (name, body, tag) AGAINST ( '$keyword') ";
          $sql .= " ) UNION ALL "; 

          $sql .= " ( SELECT name, id_person AS id, surname AS body, info AS tag , location AS info_type, \"person\" AS mytable FROM person WHERE ";
             $sql .= " MATCH (name, surname, info) AGAINST ( '$keyword' ) ";       

          $sql .= " ) UNION ALL ";

          $sql .= "( SELECT name, id_event AS id, body AS body, caffe_id AS tag , date AS info_type, \"event\" AS mytable FROM event WHERE ";
             $sql .= " MATCH (name, body) AGAINST ( '$keyword' ) ";             

        $sql .= " ) UNION ALL ";

          $sql .= "( SELECT name, id_caffe AS id, description AS body, adress AS tag, location_id AS info_type, \"caffe\" AS mytable FROM caffe WHERE ";
             $sql .= " MATCH (name, description) AGAINST ( '$keyword' ) ";           

        $sql .= " ) ";        
        $q = $this->db->query($sql);
         return $q = $q->num_rows() == 0 ? FALSE :  $q->result();
    }

如何计算每行的结果数，然后按计数结果对它们进行排序？

示例:我想搜索单词“car” - 第一行有名字“car”，并且单词“car”在正文中出现了 7 次，而在第二行“汽车”在正文中出现了 2 次。我想计算单词 car 被找到的次数，然后按该计数结果对结果进行排序。

Answer 1

我认为最好使用 PHP 来完成:

$counters = array();
foreach($q->result as $r)
  $counters[$r['mytable']]++;

由于我不知道您的数据库接口(interface)，因此这不是确切的代码，但我相信您可以了解总体思路(我假设您的结果集对象是关联数组的数组)。

编辑:

备用架构

CREATE TABLE info_generic (id int not null auto_increment PRIMARY KEY, name varchar(100),
  record_type tinyint not null default 1, body TEXT, tag_id int , info_type_id int , 
  KEY (record_type) record_type,
  KEY(tag_id) tag_id,
  KEY(info_type_id) info_type_id
);

CREATE TABLE tags(id int not null auto_increment PRIMARY KEY,  description VARCHAR(100) );

CREATE TABLE info_types(id int not null auto_increment PRIMARY KEY, 
  description VARCHAR(100), record_type tinyint default 1 
);

属性解释

record_type:1 -> global_info，2 -> person，3 -> event，4 -> caffe

正文:1 -> 正文，2 -> 姓氏，3 -> 正文，4 -> 描述

标签:1 -> 标签，2 -> 信息，3 -> 正文，4 -> 描述

然后，您可以使用 INNER JOIN 构建单个 SQL，将记录提供给 Sphinx。