Counting information in MYSQL Table

[n3mesis125]

Hey everyone, I have a little dilemma I'm in an not sure if its even possible to do what I want with the data that is in my mysql table.

 

Basically I have a table called tracker_data which holds the following column names:

 

data_id, cat_main, cat_sub1, cat_sub2, cat_sub3, cat_sub4, cat_sub5, date, cat_type

 

So an example of what would be in these columns for data would be:

 

1, cable, acnt_chng, auth_user, ac_au_add, 0, 0, 2008-04-17, email

6, wireless, reg_signin, forgot_pw, new_pw_prov, 0, 0, 2008-04-16, phone

 

What I am trying to do is search the entire table and output the total number for cat_main items are the same, cat_sub1 and so on. Then put this information in an array so I can search through it after with array_filter. However I'm not too sure how to do multiple counts on different columns in mysql, is this possible and would I be able to put each cat_* column in an array.

 

How I want to put it into an array is:

$test = array(
  'tag' => (this would be the cat_* item, ie: wireless or reg_signin...etc),
  'count' => (get number of instances of wireless, reg_signin etc)
);

 

Not sure if this makes sense, if you need more info, please let me know.

 

Thanks,

n3m.

[GingerRobot]

Im not entirely sure what you mean. You could use the following to produce a result set with the number of each duplicated cat_main and the name of this category:

 

SELECT cat_main,COUNT(*) as count FROM tracker_data GROUP BY cat_main HAVING count > 1

 

I dont think there would be a way of doing this for each sub_category in one query; you'd have to UNION the results.

 

If that's not what you were after, then maybe you could describe the problem a little more.

 

On a side note, it sounds like your ought to look into some database normalization. You should have a separate table for the sub categories. This sticky might get you started

[craygo]

Try this

<?php
$sql = "SELECT * FROM tracker_data";
$res = mysql_query($sql) or die(mysql_error());
$i=0;
while ($i < mysql_num_fields($res)) {
   $meta = mysql_fetch_field($res, $i);
   $sql2 = "SELECT `".$meta->name."`, COUNT(".$meta->name.") as Count 
   FROM tracker_data WHERE ".$meta->name." IS NOT NULL AND ".$meta->name." NOT LIKE '' 
   GROUP BY `".$meta->name."` 
   HAVING Count > 1";
   $res2 = mysql_query($sql2) or die(mysql_error());
   while($r = mysql_fetch_assoc($res2)){
   // echo results or store data in array below
   echo "Column ".$meta->name." has ".$r['Count']." rows with ".$r[$meta->name]." in it.<br />";
   }
$i++;
}
?>

 

If you want to skip the first row, since it is an ID field, start $i = 1;

 

 

Ray

[n3mesis125]

Wow thanks a bunch Ray, thats exactly what I was trying to do, but I'm gonna throw another curve ball in if its possible

Is there away to also count how many of each of those sub categories had column cat_main equal to wireless or cable, for example:

 

reg_signin can have cat_main='cable' and 'wireless at times, so is there a way so that I can also say that reg_signin has a total of say 40 but out of that 40 there were say (30 cable, showing up in cat_main for it and 10 wireless). Hope this makes sense.

 

Thanks,

n3m.

[craygo]

Since everything is being created on the fly I am not sure if you can do it. I would say you can change the group by to GROUP BY cat_main, cat_sub1 but that would be static and throw off the results.

 

To be honest you may want to think about having a separate table for your sub catagories and link them through the data_id. That way you are not limited to only 5 catagories and can probably get the results you want.

 

Ray