技巧问题;显示“nr的结果”的MySql搜索

I have a classifieds website, and users can search ads.

我有一个分类广告网站，用户可以搜索广告。

The results are displayed in three tabs on top of the page. These three are "All", "Private", and "Company". Each tab has a nr attached to it, which represents the nr of ads found in that tab.

结果显示在页面顶部的三个选项卡中。这三个是“全部”、“私人”和“公司”。每个选项卡都有一个nr，它代表该标签中发现的nr。

So for example:

举个例子:

  All     Private     Company
   5         3           2

All is just a total of private+company!

所有的一切都只是一个私人+公司!

I am using MySql as a database.

我使用MySql作为数据库。

I am trying to figure out a way to find out these "numbers of ads found" for each tab.

我正试图找到一种方法来找出每个标签上“发现的广告数量”。

I have one way of doing this, which is like this, but gives me a headache because it is so messy:

我有一种方法，就像这样，但让我头疼的是它太乱了:

$query = "SELECT SQL_CACHE * FROM classified WHERE classified.classified_id=$id";

        if ($adtypes=="Private"){
        $query_priv_comp = "SELECT SQL_CACHE * FROM classified WHERE priv_comp='Company'";
        }
        else {
        $query_priv_comp = "SELECT SQL_CACHE * FROM classified WHERE priv_comp='Private'";
        }

        switch ($adtypes){ 
            case "Private":
                $query.= " AND classified.priv_comp='Private'";
            break;

            case "Company":
                $query.= " AND classified.priv_comp='Company'";
            break;
        }

        $qry_result = mysql_query($query); // main query
        $result_priv_comp = mysql_query($query_priv_comp); // second query

        $num_priv_comp = mysql_num_rows($result_priv_comp);
        if ($adtypes=="All"){
        $num_total = mysql_num_rows($qry_result);
        }
        else if ($adtypes!="All"){
            $num_total=mysql_num_rows($qry_result) + mysql_num_rows($result_priv_comp);
        }

        if ($adtypes=="Private"){
        $num_private = $num_total - $num_priv_comp;
        $num_company = $num_priv_comp;
        }
        else {
        $num_company = $num_total - $num_priv_comp;
        $num_private = $num_priv_comp;
        }

Do you know of any other way which this can be done?

你知道还有其他的方法吗?

Thanks

谢谢

BTW: I need the rows too, in order to display information to the user of the ads found!

顺便说一句:我也需要行，以便显示信息给用户的广告找到!

3 个解决方案

#1

It depends on what you need exactly. If you just need the counts it's relatively easy:

这取决于你到底需要什么。如果你只需要计数，这相对容易:

SELECT  count(*)                           count_all
,       sum(if(priv_com = 'Private', 1, 0)) count_private
,       sum(if(priv_com = 'Company', 1, 0)) count_company
FROM    classified
WHERE   classified.classified_id=$id

If on the other hand, you need both counts as well as row data, you should either do separate queries for the data and the counts, or use a trick. Let's say your table has an id column wich is primary key, you could do:

如果另一方面，您既需要数据也需要行数据，那么您应该对数据和计数进行单独的查询，或者使用一个技巧。假设你的表有一个id列，它是主键，你可以这样做:

SELECT  count(*)                           count_all
,       sum(if(priv_com = 'Private', 1, 0)) count_private
,       sum(if(priv_com = 'Company', 1, 0)) count_company
,       classified.*
FROM    classified
WHERE   classified.classified_id=$id
GROUP BY id  -- group by on primary key
WITH ROLLUP

The WITH ROLLUP magic will give you an extra row with the counts for the entire query. The only snag is that you will receive this row as last row of the entire result, so if you want to report the counts before the data, you're going to have to cache the row data in an php array or so and process that later to build up the page.

带滚动魔术将为您提供一个额外的行，其中包含整个查询的计数。唯一的问题是,你会收到这一行作为整个结果的最后一行,如果你想报告前的统计数据,您要缓存一个php数组的行数据和过程后建立的页面。

#2

After your switch variable $query_priv_comp would be equal to:

切换变量$query_priv_comp后，将等于:

SELECT SQL_CACHE * FROM classified WHERE priv_comp='Company' 
  AND classified.priv_comp='Private'

或

SELECT SQL_CACHE * FROM classified WHERE priv_comp='Private' 
 AND classified.priv_comp='Company'

Question: What the difference???

问题:有什么区别? ? ?

#3

You can select all counts with one query:

您可以选择所有计数与一个查询:

SELECT priv_comp, COUNT(*) AS record_count FROM classified GROUP BY priv_comp

Then you can query all the records needed for the current tab.

然后可以查询当前选项卡所需的所有记录。

These 2 should be separated clearly.

这两个应该分开。

#1