seventheyejosh Posted December 5, 2008 Share Posted December 5, 2008 Ok, i've never posted an sql question here, so I hope i do this right. I believe we are running mysql 5.0. basically, i am making a search module, that will display results based on the occurence of a word. The problem i am having is searching multiple fields and adding the results. Basically I want to type in "run" in my search, and have it search my `news_messages` table inside of the `headline` `summary` and `story` and then add all of the results together. I need this to go through the whole table and give me the stories that have the word "run" in them, in the order of the most occurences through the sum of all three fields. I've searched, and i haven't found anycode to do this purely with an sql statement, so i was considering just pulling each row out in a while and adding them together, as this would possibly work. However, the larger the site gets, the longer it would take etc. Any thoughts? thanks in advance. Quote Link to comment Share on other sites More sharing options...
Maq Posted December 5, 2008 Share Posted December 5, 2008 in the order of the most occurences through the sum of all three fields. Not sure exactly what order that would be but after you run your query you just use mysql_num_rows to return the number of results. I understand you need a query for a search in 3 fields: headline summary story but I'm lost in what kind of order you want them returned. Quote Link to comment Share on other sites More sharing options...
seventheyejosh Posted December 5, 2008 Author Share Posted December 5, 2008 Ok basically. If this is a test story: head - He runs! sum - Still running! story- Some guy is running. Still running. basically i want it to go through the DB when u search for run and see that this story has 4 occurences of %run%. 1 in head 1 in sum and 2 in story. if we have this: head - He runs! sum - Still running! story- Some guy is running. Still running. And running. there are 5 occurences of %run%, so when the search results were displayed, these storys would both show, except the latter of the two would be first because there are 5 occurcnces as opposed to 4. so basically it goes through every row of the 'news_messages' table, adding `headline` `summary` and `story` instances of the searched word, then displays all results that have the word at least once, in order of most occurences. Quote Link to comment Share on other sites More sharing options...
seventheyejosh Posted December 5, 2008 Author Share Posted December 5, 2008 I mean adding the instances. I suppose can you join the fields and then do a count of the occurence of the word in that one? or would that be a waste of time? Quote Link to comment Share on other sites More sharing options...
seventheyejosh Posted December 7, 2008 Author Share Posted December 7, 2008 sorry to *bump*, but i desperately need to figure this out by monday. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 8, 2008 Share Posted December 8, 2008 You can't count words within a field without a custom function... Quote Link to comment Share on other sites More sharing options...
seventheyejosh Posted December 8, 2008 Author Share Posted December 8, 2008 is there a place where i could find such a snippet? or at least some guidlines for writing my own? thanks much. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 8, 2008 Share Posted December 8, 2008 Seems like you really want the power of full-text indexing... have you looked at Sphinx or Lucene? 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.