Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008

    Determine the Most Frequently Appeared data in Database

    Is there anyway to determine the most frequently entered data in Mysql?

    For example, there is a form that users can submit their favorite hobby. What can I do to automatically fetch out the most frequently entered hobby? I also have to filter out unrelated words such as "is, there, going, to, and etc"

    user_id | Hobby
    Uesr 1: Hobby Submition: Going to skydive
    User 2: Going to the gym
    User 3: Skydiving
    User 4: playing basketball
    User 5: skydive tomorrow
    What can I do to automatically fetch out "skydive" as the most frequently appeared data?

  2. #2
    Join Date
    May 2009
    I can't think of a specific query to accomplish this, but if you break it up into a couple of steps you might be able to get some usable results. I might do something like this:

    SELECT hobby FROM table

    Try to capture unique words into an array. You can think of some common language rules to help with verb tenses, for example:

    if one word matches /(.*)ing/ and another matches /$1?[e]/ then you have the same word. Ie, (skydiv)ing would match (skydiv)e and (eat)ing would match (eat).

    Once you've accumulated a good list of keywords, you could then run them against the table in a FULL TEXT search and track the result counts. You might want to play around with different search types to see how you like the search results. An advantage here is that common words searched on their own will return no results, so they are automatically filtered out of the results.


  3. #3
    Join Date
    Jun 2008
    thanks Mattle!

  4. #4
    Join Date
    Sep 2009
    Well in this case, you will have problem, because it is not good idea to create stats from text data. Misspellings, plurals, -ing etc will harden your job and you will get incorrect results.

    I advise you to create select-multiple drop list and/or checkboxes.

  5. #5
    Join Date
    Sep 2009
    I would suggest modifying your form where they submit the data to remove the information you don't want before you put it in the database. If you do that, then it would be pretty easy to write an SQL query to get what you want...

  6. #6
    Join Date
    Nov 2001
    Constraining the data might the preferred option for some application requirements, but perhaps the OP wants to leave the choices up to users for a good reason. After all, is a constrained list likely to include "Competitive Rockclimbing" or "Arial Crocheting" ?

    Face it, a lot of "data" is free form text. Don't try to fight that all the time... that's what search is for.

    Filtering out "stop words" is a good start.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  7. #7
    Join Date
    Jul 2008
    Kansas City
    Well it is possible, you can use select from users where dateupdated is closest to today, and if you need the actual code I can send it over

Similar Threads

  1. Domain Name Frequently Asked Questions Database
    By kohashi in forum Domain Name Tutorials
    Replies: 51
    Last Post: 11-04-2013, 12:09 PM
  2. Javascript error - Suddenly appeared
    By Philipf in forum Programming Discussion
    Replies: 6
    Last Post: 10-22-2004, 06:35 PM
  3. [PHP] show me an example - read database and insert data to another database
    By Georgecooldude in forum Programming Discussion
    Replies: 9
    Last Post: 09-23-2004, 11:04 AM
  4. Imagine sitting in your room one day and this appeared.....
    By The Dude in forum Web Hosting Lounge
    Replies: 34
    Last Post: 04-12-2003, 08:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts