Find most frequently word in field

[eevan79]

How to find most frequently word in table field?

I have table players with filed items and want to find most used word in that filed.

 

Items are like 'HIDU', 'I099', 'I05F'. Thats item code (id) that is used for images. I also have table items where is filed itemid same as items in players table. In that table I have additional information about that item. So how to find one frequently used item in from players table ?

[kickstart]

Hi

 

What does the field contain? Does it just contain a single one of those strings and you want a list of the count of rows for each string? Or does the field contain any number of those strings, and if so how are they separated?

 

All the best

 

Keith

[BlueSkyIS]

the way i have done this in the past is to count the words as they are entered and use a separate table to maintain that count. counting them on the fly could be a long, slow process as your database grows.

[eevan79]

This field contain 4 letters code like 'H45T', 'AB7T' etc. Also I have images H45T to match selected item. So I want to find most used item in filed items. There are lots of records with different or same items code, so I want to find what same items are most used.

 

This can be very hard job for MySql cause it need to compare every filed . Maybe this is impossible to do in this way.

 

[kickstart]

Hi

 

Is it a 4 character field? Or is it a field that contains multiple 4 character fields? It multiple are the separated by a space, a comma or what?

 

It is is a single 4 character field it is easy. Multiples would be far more difficult (and also suggest an issue with the database design).

 

Can you give us the table declaration and a few sample records?

 

All the best

 

Keith

[eevan79]

Fileds:

`id`, `botid`, `gameid`, `colour`, `kills`,

`deaths`, `creepkills`, `creepdenies`, `assists`, `gold`, `neutralkills`,

`item1`, `item2`, `item3`, `item4`, `item5`, `item6`,

`hero`, `newcolour`, `towerkills`, `raxkills`, `courierkills`

Values:

(8, 0, 1, 7, 10, 6, 279, 0, 12, 24988, 31, 'I0BG', 'I0BJ', 'I095', 'I0A9', 'I06B', 'I099', 'O00J', 7, 4, 4, 0),

(9, 0, 1, 1, 10, 11, 333, 3, 10, 6748, 49, 'I06B', 'I0AB', 'I0BA', 'I0G1', 'I0AI', 'I09C', 'E005', 1, 1, 0, 0),

(10, 0, 1, 8, 6, 12, 154, 6, 14, 1, 3, 'I04I', 'I0GJ', 'I000', 'I0BK', '\0\0\0\0', 'I05I', 'Hjai', 2, 0, 0, 0),

(11, 0, 2, 3, 0, 0, 0, 0, 0, 316, 0, 'I02O', '\0\0\0\0', '\0\0\0\0', '\0\0\0\0', '\0\0\0\0', '\0\0\0\0', 'H00S', 3, 0, 0, 0)

 

\0\0\0\0 - This is empty/blank item and no need to count.

All items have 4 characters and it's not separated by a space, comma...

 

item1 filed is slot 1 for item

item2 is for slot 2...

There are 6 slots and I want to find most used item for each slot.

Let's start for item 1...then it'll be easy to find for other slots.

[BlueSkyIS]

i suggest that you keep track of the different combinations in a separate table, incrementing a count column each time one is encountered. performing the count on the fly will take more time and resources, and become more time- and resource-intensive as the database grows.

[eevan79]

I know, but it's only possible if hosting program (which is used for importing data) modify to update another table.

Maybe solution is to periodically update count for items table via cron (or some script). Then I need to check for about 300 items.

 

Only problem is that items is chained with heroes (another table). My goal is to check most used items for each hero.

 

So I dont need items count table, but hero_items and update data for every filed (hero_items.item1, hero_items.item2 etc.) where I match most used item.

 

I'm not sure which is best way to do something like this.

[kickstart]

Hi

 

Really would be easier if you had them on a seperate table.

 

If not then something like this

 

 

SELECT 'item1' AS ItemType, item1 AS Item, count(*) AS ItemCountFROM someTableGROUP BY ItemType, ItemUNIONSELECT 'item2' AS ItemType, item2 AS Item, count(*) AS ItemCountFROM someTableGROUP BY ItemType, ItemUNIONSELECT 'item3' AS ItemType, item3 AS Item, count(*) AS ItemCountFROM someTableGROUP BY ItemType, ItemUNIONSELECT 'item4' AS ItemType, item4 AS Item, count(*) AS ItemCountFROM someTableGROUP BY ItemType, ItemUNIONSELECT 'item5' AS ItemType, item5 AS Item, count(*) AS ItemCountFROM someTableGROUP BY ItemType, ItemUNIONSELECT 'item1' AS ItemType, item6 AS Item, count(*) AS ItemCountFROM someTableGROUP BY ItemType, Item

 

 

All the best

 

Keith

[eevan79]

Ok, I have tried your code but it gives me following result:

ItemType -> item1

Item -> blank (nothing)

ItemCount -> 3105

This is my code:

 

SELECT 'item1' AS ItemType, item1 AS Item, count(*) AS ItemCount     FROM dotaplayers     GROUP BY ItemType, Item     UNION     SELECT 'item2' AS ItemType, item2 AS Item, count(*) AS ItemCount     FROM dotaplayers     GROUP BY ItemType, Item     UNION     SELECT 'item3' AS ItemType, item3 AS Item, count(*) AS ItemCount     FROM dotaplayers     GROUP BY ItemType, Item     UNION     SELECT 'item4' AS ItemType, item4 AS Item, count(*) AS ItemCount     FROM dotaplayers     GROUP BY ItemType, Item     UNION     SELECT 'item5' AS ItemType, item5 AS Item, count(*) AS ItemCount     FROM dotaplayers     GROUP BY ItemType, Item     UNION     SELECT 'item6' AS ItemType, item6 AS Item, count(*) AS ItemCount     FROM dotaplayers     GROUP BY ItemType, Item

 

 

$getItems = $db->query($sql);     $rowItems = $db->fetch_array($getItems,'assoc');      echo $rowItems['ItemType'];      echo $rowItems['Item'];      echo $rowItems['ItemCount'];

 

[eevan79]

I found solution. It's not so hard.

Here is code:

SELECT 
    count(*) as total,
    item1 FROM dotaplayers 
    WHERE hero = '$heroid'
    GROUP BY item1 having count(*) > 1 
    ORDER BY total DESC LIMIT 5

Thats for item1

[kickstart]

Hi

 

Yep, that is if you want the first 5. My solution was to give you the totals of all of them.

 

All the best

 

Keith

[eevan79]

Hi,

 

I have tried your code and it gives me wrong results or error. Can't remember what it is. I'll try to combine your and my code to get all items with one query.