PHP/MySQL: Is there a better way to count distinct values in a column

[Peter Karunyu]

I have a table with 10 columns, and about 20,000 rows.

I want to get the count of the distinct values in 5 of the columns, so normally, I would probably write 5 queries thus:

SELECT column_a, count(column_a) FROM my_table GROUP BY column_a;

SELECT column_b, count(column_b) FROM my_table GROUP BY column_b;

SELECT column_c, count(column_c) FROM my_table GROUP BY column_c;

SELECT column_d, count(column_d) FROM my_table GROUP BY column_d;

SELECT column_e, count(column_e) FROM my_table GROUP BY column_e;

My question is, is there a way I can do this in a more generic/future proof way? 

Maybe a stored procedure which I just pass the table name to? Or some PHP function which I just give the name of the table to? 

Ultimate goal is to write just one query to get the above data.

[Peter Karunyu]

And 7 hours later, I am here:

function count_distinct_values($table_name = '', $column_name = '')
{
    $result = $this->db->query("SELECT $column_name, count(*) AS value_count FROM $table_name GROUP BY $column_name");
   
    if ($result == FALSE) {
        return FALSE;
    }
   
    if ($result->num_rows() == 0) {
        return FALSE;
    }
   
    $master_array = array();
    foreach ($result->result_array() as $row) {
        $master_array[$row[$column_name]] = $row['value_count'];
    }
   
    return $master_array;
}

--
You received this message because you are subscribed to the Google Groups "CodeIgniter Kenya" group.
To unsubscribe from this group and stop receiving emails from it, send an email to codeigniter-ke...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Regards,
Peter Karunyu
-------------------

[Isaak Mogetutu]

I'm eeked out by this code segment:

 if ($result == FALSE) {
        return FALSE;
    }
    
 if ($result->num_rows() == 0) {
        return FALSE;
    }

Quick question if $result == false can $result->num_rows() != 0 thats why the double checks for false

Instead can you check if its true load master array else return false

// Not tested just my thoughts

function count_distinct_values($table_name = '', $column_name = '')

{

    $result = $this->db->query("SELECT $column_name, count(*) AS value_count FROM $table_name GROUP BY $column_name");

    

    if ($result) {

        $master_array = array();

        foreach ($result->result_array() as $row) {

            $master_array[$row[$column_name]] = $row['value_count'];

        }

        

        return $master_array;

    }

    

    return FALSE;

}

[Peter Karunyu]

I got into the habit of separating num_rows() from the other one coz I sometimes need to differentiate from a query failing (which is super bad) to a query not returning any data (which is ok).

But in the example above, there's no need for that so yes, your suggestion is better