Pomtom44 Posted October 31, 2013 Share Posted October 31, 2013 Hey all. Kinda new to PHP and MySql so please be gentle hahaIm 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 1Jim B 2Tom A 1Sam A 1Jim B 2Sam A 3Sam B 2 Tom A 2 What number comes up most for Tom AWhat number comes up most for Sam BWhat number comes up most for Sam AAnd so on. Hope it makes sense and hope someone can help me out Quote Link to comment Share on other sites More sharing options...
AdRock Posted October 31, 2013 Share Posted October 31, 2013 I don't understand your question Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted October 31, 2013 Share Posted October 31, 2013 Perhaps the COUNT() function in MySQL will help: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count Quote Link to comment Share on other sites More sharing options...
digibucc Posted October 31, 2013 Share Posted October 31, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 31, 2013 Share Posted October 31, 2013 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 Quote Link to comment Share on other sites More sharing options...
Pomtom44 Posted October 31, 2013 Author Share Posted October 31, 2013 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 31, 2013 Share Posted October 31, 2013 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 Quote Link to comment Share on other sites More sharing options...
digibucc Posted November 1, 2013 Share Posted November 1, 2013 I don't know that table structure just looks messy to me, however my mysql skills are not top notch Quote Link to comment Share on other sites More sharing options...
Pomtom44 Posted November 1, 2013 Author Share Posted November 1, 2013 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 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.