Page 1 of 1

find popular data in MySQL database

Posted: Fri Mar 18, 2011 3:48 am
by tutigan
I am trying to write a report for some helpdesk software I am using at work...
What I want to do is find the five most used (sub)categories (each service request (or 'sr') has a category and subcategory)
to do this, I would need to query the 'category' table for the category AND subcategory. I would then check the 'sr' table for the category and subcategory used in each row...
I assume that there would be an easier way than checking what cat/subcat is used for each sr, adding these together, then working out the top five from there, but have no idea how it would work!

Does this even make sense to you? If so, please help! If not, tell me and I'll try to clarify! :D

Re: find popular data in MySQL database

Posted: Fri Mar 18, 2011 3:57 am
by gooney0
From what I understand about your question much of this could be done in your query.

For instance you could query the table of service requests and count the distinct categories and subcategories.

You could also LEFT JOIN the category table in order to use the category names.

You could also use ORDER BY and LIMIT to return only the top 5 rows.

I would suggest playing around with your queries in phpMyAdmin (or whatever client you like) and see how close you can get to what you want. Then you can use PHP to perfect and output the data.

If I'm way off perhaps you could post a couple of rows from each of the tables in question?

Re: find popular data in MySQL database

Posted: Fri Mar 18, 2011 4:20 am
by tutigan
hmm, I tried that, but either I did it wrong, or that's not quite what I need...
This is the query I tried:

Code: Select all

SELECT category.cat, category.scat, sr.cat1, sr.cat2 FROM category LEFT JOIN sr ON category.cat=sr.cat1 AND category.scat=sr.cat2
but it doesn't show which is most used, just lists each cat/subcat and srs that use them
an example of how the tables work:
category table:
[text]a a
a b
a c
a d
b a
b b
b c
b d
c a[/text]and so on and so on... (the first character represents the category, the second represents the subcategory)
the sr table is somewhat like this:
[text]a a
a d
b c
b d
a a
c a[/text]and so on and so on... (again, the first character represents the category, the second represents the subcategory)

what I am trying to do is find which 5 categories and subcategories (as defined by the category table) are used most in the sr table