Jump to content

Counting information in MYSQL Table


n3mesis125

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/103756-counting-information-in-mysql-table/
Share on other sites

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

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

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

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.

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

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.