Searching Database for Most found result

[Pomtom44]

Hey all.

Kinda new to PHP and MySql so please be gentle haha

Im wanting to make a page which goes though my database and finds the most used word in a column then display it on screen. but with different search requests.

 

Heres an example

 

Database:

 

Name:                  Entry:                       Result

Tom                     A                              1
Jim                       B                              2
Tom                     A                              1
Sam                     A                              1
Jim                       B                              2
Sam                     A                              3
Sam                     B                              2 
Tom                     A                              2

 

What number comes up most for Tom A
What number comes up most for Sam B
What number comes up most for Sam A

And so on.

 

Hope it makes sense and hope someone can help me out  

[AdRock]

I don't understand your question

[cyberRobot]

Perhaps the COUNT() function in MySQL will help:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

[digibucc]

with the current structure and what you need to do, it's a pita, you actually have to compare 3 columns right now, name ,entry & results.

 

I would change the db/table structure, make it:

NAME | ENTRIES | RESULT | ID

and have ID be an auto-incrementing primary key. then in the results field, you want to put "serialized array" with each result. then when a user enters a value it would unserialize that array, add the value to it, and then serialize and insert the whole thing.

 

that way your data is ready to use, you just pull the row and it will have a name & entry, run an if on each entry and then unserialize it's results field and you can use count() on the resulting array. that makes the code simpler and it's more efficient, no need to check the name on every entry if they are all tied to the name specifically.

Edited October 31, 2013 by digibucc

[Barand]

Then it becomes a pita to sort or search by result. I don't think extra effort occasionally required is sufficient excuse to denormalize the data

[Pomtom44]

Most of that went right over the top of my head sorry.
I do have a auto assigned ID value in there as well (I thought that was just standard practise so I didnt mention it)

As for count() I had a look but couldnt figure out how to get it to display the most hit result rather than how many times a certain value was entered

[Barand]

try

SELECT name, entry, result, COUNT(*) as total
FROM pomtom
WHERE name='Tom' AND entry='A'
GROUP BY name, entry, result
ORDER BY name, entry, total DESC;

Line 3 is optional. If omitted you get counts for all, with highest occurring results first for each

[digibucc]

I don't know that table structure just looks messy to me, however my mysql skills are not top notch

[Pomtom44]

the table structure is messy as its an example.
and thanks Barand for the tip. ill try it out see if I can get what I want from it