n3mesis125 Posted May 1, 2008 Share Posted May 1, 2008 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. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 1, 2008 Share Posted May 1, 2008 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 Quote Link to comment Share on other sites More sharing options...
craygo Posted May 1, 2008 Share Posted May 1, 2008 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 Quote Link to comment Share on other sites More sharing options...
n3mesis125 Posted May 1, 2008 Author Share Posted May 1, 2008 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. Quote Link to comment Share on other sites More sharing options...
craygo Posted May 2, 2008 Share Posted May 2, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.