Professional Web Applications Themes

order search results by frequency? - MySQL

Hi is it possible to order a mySQL statement which uses "WHERE haystack LIKE '%needle%'" based on the number of times the 'needle' is found in the 'haystack'? This would be very handy for search engine results etc to put the most relevent topics at the top of the results list. Cheers, Ciarán...

  1. #1

    Default order search results by frequency?

    Hi is it possible to order a mySQL statement which uses "WHERE haystack
    LIKE '%needle%'" based on the number of times the 'needle' is found in
    the 'haystack'?

    This would be very handy for search engine results etc to put the most
    relevent topics at the top of the results list.

    Cheers,
    Ciarán

    cron Guest

  2. #2

    Default Re: order search results by frequency?

    >Hi is it possible to order a mySQL statement which uses "WHERE haystack 

    You could use something like
    SUM(haystack like '%needle%')
    to get the number of matches, and ORDER BY it, in combination with
    an appropriate GROUP BY.

    Gordon Guest

  3. #3

    Default Re: order search results by frequency?

    > You could use something like 

    Sounds good Gordon but I'm not sure of the syntax. I'm not exactly an
    expert at SQL. Could you be more specific? could you correct this for
    me maybe?

    SELECT * from data WHERE haystack LIKE '%needle%'" ORDER BY
    SUM(haystack like '%needle%')

    Thanks,
    Ciarán

    cron Guest

  4. #4

    Default Re: order search results by frequency?

    Hello? Can someone please help me out?
    Cheers,
    Ciarán

    cron Guest

  5. #5

    Default Re: order search results by frequency?

    cron wrote: 
    >
    > Sounds good Gordon but I'm not sure of the syntax. I'm not exactly an
    > expert at SQL. Could you be more specific? could you correct this for
    > me maybe?
    >
    > SELECT * from data WHERE haystack LIKE '%needle%'" ORDER BY
    > SUM(haystack like '%needle%')[/ref]

    SELECT SUM(haystack LIKE '%needle%') AS frequency, ...other columns...
    FROM data
    WHERE haystack LIKE '%needle%'
    ORDER BY frequency DESC

    But if you're doing a search-engine like application, you should also
    read about the fulltext search indexing that MySQL offers.
    http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

    Regards,
    Bill K.
    Bill Guest

  6. #6

    Default Re: order search results by frequency?

     


    Cheers for the info Bill. I tried it but I got an error saying the
    statement needs a GROUP BY? I'll have a read of that article too - it
    looks interesting. Thanks again,
    Ciarán

    cron Guest

  7. #7

    Default Re: order search results by frequency?

    I don't think the SUM idea will work.

    You definitely should check fulltext searching to order your results by
    relevancy.

    Tigger


    cron wrote: 
    >
    >
    > Cheers for the info Bill. I tried it but I got an error saying the
    > statement needs a GROUP BY? I'll have a read of that article too - it
    > looks interesting. Thanks again,
    > Ciarán[/ref]

    Tigger Guest

Similar Threads

  1. Search results
    By Bskyweb in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 16th, 07:52 PM
  2. Sending search results to a results page..with asp
    By dan.how in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 26th, 04:18 PM
  3. Help with search results in asp vbs
    By Chris in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 17th, 09:57 PM
  4. Search within Search Results
    By Maria Kovacs in forum ASP Database
    Replies: 1
    Last Post: October 7th, 04:45 PM
  5. Printing Search Results
    By Sarah in forum Windows XP/2000/ME
    Replies: 3
    Last Post: July 15th, 07:57 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139