Forum Moderators: coopster
id cat id subcat id item
-- ------- --------- ----
1 1 2 abcdd
2 1 1 abcdd
3 1 1 abcdd
4 1 1 abcdd
5 1 1 abcdd
6 2 3 abcdd
7 1 1 abcdd
8 1 2 abcdd
9 1 1 abcdd
10 1 1 abcdd
11 2 4 abcdd
12 1 2 abcdd
13 2 4 abcdd
14 1 3 abcdd
15 1 1 abcdd
16 1 3 abcdd
17 1 1 abcdd
18 2 4 abcdd
19 1 3 abcdd
20 2 4 abcdd
i need to find out:
1) for which subcat id there is most number of entries where the cat id is given (also the number)
2) which is the last entry for a given cat id
looking at it i know for cat id = 1 the last entry is row 19 and for cat id = 2 its row 20. and where cat id = 2 i know sub cat id 4 has most entries. but how do i do it?
all i know is that there needs to be loops but when i set out to write them, i just got lost somewhere and deleted all id written :( and im left with this
function WhichSubcatHasMostEntries($CatID)
{
}
if its of any use, ive got another table, where im storing the subcat id and cat id, in which subcat id is primary and unique.
please help me with this
thanks in adavance
this is Too much Simple..
Please reply with your Database Table Design with all field names and table names
and i ll write you your queries for that function within minutes.
Cheers
Kami
If your table is of the form:
category (id, cat_id, subcat_id, item)Then 1) would be something like:
select subcat_id, count(subcat_id) as 'total' from category where cat_id = $cat_id group by subcat_id order by total limit 1And for 2)
select max(id) from category where cat_id = $cat_idarran.
if its of any use, ive got another table, where im storing the subcat id and cat id, in which subcat id is primary and unique.
Your initial post is a little vague, but I would use something similar to this...
You could add a col to the table with the unique id's 'subcat_cnt'. Initially you would have to insert the values (counts) manually, but then you could automate it.
When you update a category and/or sub category add an update to the subcat_cnt for a running total. Depending on how many categories and entries you have this could save quite a bit of time.
EG
UPDATE yourtable SET subcat_cnt=subcat_cnt+1 WHERE cat="$yourcategory"
(or something to this effect depending on the exact structure of your tables)
Now there is a running total for the categories, by subcategory, so rather than adding and sorting the full table, you can make category based selections from the table you just added the col to:
$sql="SELECT subcat FROM yourtable WHERE cat=$cat ORDER BY subcat_cnt DESC";
This will give you the subcat with the largest number of entries for the category selected.
**query stuff**
then find the last entry from the main table:
$last_entry = mysql_fetch_array($sql);
$sql2="SELECT * FROM main_table WHERE subcat=$last_entry['subcat'] ORDER BY id DESC LIMIT 1";
**query stuff**
Finally, you can output all the information:
while($result=mysql_fetch_array($sql2)) {
**do what you want with the stuff you just got**
}
There are a number of ways to solve this problem, I just prefer to keep things as short as possible when returning information, and by doing the counts on the way in they will be stored and can then be returned in a shorter period of time.
In this case it appears finding a category number and ordering by a count would be faster than finding, grouping and counting multiple cols to find the one with the most entries.
Hope this helps.
Justin
thanks again for all the help
