Trick Question; Showing 'nr of results' of MySql search

Question

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.

So for example:

  All     Private     Company
   5         3           2

All is just a total of private+company!

I am using MySql as a database.

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!

Answer 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:

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.

Answer 2

Ha-ha... I think if Camran knows the SQL_CACHE, COUNT he's really know.

Answer 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.

Answer 4

After your switch variable $query_priv_comp would be equal to:

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

or

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

Question: What the difference???